使用Python将JSON整个整合到一个SQLite字段中 [英] Entire JSON into One SQLite Field with Python

问题描述

我有一个简单的问题.我正在尝试从在线资源中提取JSON，并将其存储在SQLite表中.除了将数据存储在丰富的表中(与JSON中的许多字段相对应)外，我还想在每次提取JSON时将其全部转储到表中.

I have what is likely an easy question. I'm trying to pull a JSON from an online source, and store it in a SQLite table. In addition to storing the data in a rich table, corresponding to the many fields in the JSON, I would like to also just dump the entire JSON into a table every time it is pulled.

表格如下:

CREATE TABLE Raw_JSONs (ID INTEGER PRIMARY KEY ASC, T DATE DEFAULT (datetime('now','localtime')), JSON text);

我已使用以下python代码从某个URL中提取了JSON:

I've pulled a JSON from some URL using the following python code:

from pyquery import PyQuery
from lxml import etree
import urllib

x = PyQuery(url='json')
y = x('p').text()

现在，我想执行以下INSERT命令:

Now, I'd like to execute the following INSERT command:

import sqlite3

db = sqlite3.connect('a.db')
c = db.cursor()

c.execute("insert into Raw_JSONs values(NULL,DATETIME('now'),?)", y)

但是我被告知我提供了不正确的数字绑定(即数千个，而不只是1个).我收集它是将y变量读取为JSON的所有不同元素.

But I'm told that I've supplied the incorrect number bindings (i.e. thousands, instead of just 1). I gather it's reading the y variable as all the different elements of the JSON.

有人可以帮我存储JSON的全部内容吗?

Can someone help me store just the JSON, in it's entirety?

此外，由于我显然是这个JSON游戏的新手，因此任何值得推荐的在线资源都将很棒.

Also, as I'm obviously new to this JSON game, any online resources to recommend would be amazing.

谢谢！

推荐答案

.execute()期望一个序列，最好给它一个一元组:

.execute() expects a sequence, better give it a one-element tuple:

c.execute("insert into Raw_JSONs values(NULL,DATETIME('now'),?)", (y,))

Python字符串也是一个序列，是单个字符之一.因此，.execute()调用尝试将每个单独的字符视为您查询的参数，并且除非您的字符串短一个字符，否则它将无法提供正确数量的参数.

A Python string is a sequence too, one of individual characters. So the .execute() call tried to treat each separate character as a parameter for your query, and unless your string is one character short that means it'll not provide the right number of parameters.

别忘了提交您的插入内容:

Don't forget to commit your inserts:

db.commit()

或将数据库连接用作上下文管理器:

or use the database connection as a context manager:

with db:
    # inserts executed here will automatically commit if no exceptions are raised.

这篇关于使用Python将JSON整个整合到一个SQLite字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！