使用Python将多个json项解析为SQLite数据库

Question

我正在寻找一种非显式的方法来将JSON列表（即带有{}项的.[]）解析到sqlite数据库中。在

具体地说，我被困在我想说的地方

INSERT into MYTABLE (col 1, col2, ...) this datarow in this jsondata

我觉得应该有一种方法来抽象事物，使上面的线几乎就是它所需要的。JSON是一个包含多个字典的。每本字典有一打左右键：值对。没有嵌套。在

^{pr2}$

json列表[{k1:v1a, k2:v2a}, {k1:v1b,k2:v2b},...]中的每个itesm都有完全相同的键名。我将这些列作为sqlite数据库中列的名称。不过，数值会有所不同。因此，通过将每个键/列的值放入该项的该行中来填充表。在

k1  | k2  | k3  | ... | km
v1a | v2a | v3a | ... | vma
v1b | v2b | v3b | ... | vmb
...
v1n | v2n | v3n | ... | vmn

在SQL中，insert语句的编写顺序不必与数据库的列完全相同。这是因为您在INSERT声明中指定了要为其插入的列（以及顺序）。这对于JSON来说似乎是完美的，JSON列表中的每个行/项都包含其列名（键）。因此，我需要一个语句，声明“给定这行JSON，通过协调JSON键名和SQL表列名，将其所有数据插入到SQL表中”。这就是我所说的不明确的意思。在

import json

r3 = some data file you read and close
r4 = json.loads(r3)
# let's dump this into SQLite

import sqlite3

the_database = sqlite3.connect("sys_col_database.sqlite")
the_cursor = the_database.cursor()

row_keys = r4[0].keys()
# all of the key are below for reference. 25 total keys.
'''
'is_merge_published',   'rule_object_id',   'system_type_id',
'is_xml_document',      'user_type_id',     'is_ansi_padded',
'column_id',            'is_column_set',    'scale',
'is_dts_replicated',    'object_id',        'xml_collection_id',
'max_length',           'collation_name',   'default_object_id',
'is_rowguidcol',        'precision',        'is_computed',
'is_sparse',            'is_filestream',    'name',
'is_nullable',          'is_identity',      'is_replicated',
'is_non_sql_subscribed'
'''

sys_col_table_statement = """create table sysColumns (
    is_merge_published text,
    rule_object_id integer,
    system_type_id integer,
    is_xml_document text,
    user_type_id integer,
    is_ansi_padded text,
    column_id integer,
    is_column_set text,
    scale integer,
    is_dts_replicated text,
    object_id integer,
    xml_collection_id integer,
    max_length integer,
    collation_name text,
    default_object_id integer,
    is_rowguidcol text,
    precision integer,
    is_computed text,
    is_sparse text,
    is_filestream text,
    name text,
    is_nullable text,
    is_identity text,
    is_replicated text,
    is_non_sql_subscribed text
    )
"""

the_cursor.execute(sys_col_table_statement)

insert_statement = """insert into sysColumns values (
{0},{1},{2},{3},{4},
{5},{6},{7},{8},{9},
{10},{11},{12},{13},{14},
{15},{16},{17},{18},{19},
{20},{21},{22},{23},{24})""".format(*r4[0].keys())

我被困在这里。insert_statement是一个将是executed的字符串的构造。现在我需要执行它，但是从r4中的每个JSON项向它提供正确的数据。我不知道该怎么写。在

Answer 1

这里有两种选择。我还一步就加载了JSON数据。在

#python 3.4.3

import json
import sqlite3

with open("data.json",'r') as f:
    r4 = json.load(f)

the_database = sqlite3.connect("sys_col_database.sqlite")
the_cursor = the_database.cursor()

row_keys = list(r4[0].keys())
# all of the key are below for reference. 25 total keys.
'''
'is_merge_published',   'rule_object_id',   'system_type_id',
'is_xml_document',      'user_type_id',     'is_ansi_padded',
'column_id',            'is_column_set',    'scale',
'is_dts_replicated',    'object_id',        'xml_collection_id',
'max_length',           'collation_name',   'default_object_id',
'is_rowguidcol',        'precision',        'is_computed',
'is_sparse',            'is_filestream',    'name',
'is_nullable',          'is_identity',      'is_replicated',
'is_non_sql_subscribed'
'''

sys_col_table_statement = """create table sysColumns (
is_merge_published text,
rule_object_id integer,
system_type_id integer,
is_xml_document text,
user_type_id integer,
is_ansi_padded text,
column_id integer,
is_column_set text,
scale integer,
is_dts_replicated text,
object_id integer,
xml_collection_id integer,
max_length integer,
collation_name text,
default_object_id integer,
is_rowguidcol text,
precision integer,
is_computed text,
is_sparse text,
is_filestream text,
name text,
is_nullable text,
is_identity text,
is_replicated text,
is_non_sql_subscribed text
)
"""
# method 1
# create a string with the keys prepopulated
prepared_insert_statement = """insert into sysColumns (\
{0}, {1}, {2}, {3}, {4}, \
{5}, {6}, {7}, {8}, {9}, \
{10}, {11}, {12}, {13}, {14}, \
{15}, {16}, {17}, {18}, {19}, \
{20}, {21}, {22}, {23}, {24}) values (\
'{{{0}}}', '{{{1}}}', '{{{2}}}', '{{{3}}}', '{{{4}}}', \
'{{{5}}}', '{{{6}}}', '{{{7}}}', '{{{8}}}', '{{{9}}}', \
'{{{10}}}', '{{{11}}}', '{{{12}}}', '{{{13}}}', '{{{14}}}', \
'{{{15}}}', '{{{16}}}', '{{{17}}}', '{{{18}}}', '{{{19}}}', \
'{{{20}}}', '{{{21}}}', '{{{22}}}', '{{{23}}}', '{{{24}}}'\
)""".format(*r4[0].keys())

# unpack the dictionary to extract the values
insert_statement = prepared_insert_statement.format(**r4[0])

# method 2
# get the keys and values in one step
r4_0_items = list(r4[0].items())
insert_statement2 = """insert into sysColumns (\
{0[0]}, {1[0]}, {2[0]}, {3[0]}, {4[0]}, \
{5[0]}, {6[0]}, {7[0]}, {8[0]}, {9[0]}, \
{10[0]}, {11[0]}, {12[0]}, {13[0]}, {14[0]}, \
{15[0]}, {16[0]}, {17[0]}, {18[0]}, {19[0]}, \
{20[0]}, {21[0]}, {22[0]}, {23[0]}, {24[0]}) values (\
'{0[1]}', '{1[1]}', '{2[1]}', '{3[1]}', '{4[1]}', \
'{5[1]}', '{6[1]}', '{7[1]}', '{8[1]}', '{9[1]}', \
'{10[1]}', '{11[1]}', '{12[1]}', '{13[1]}', '{14[1]}', \
'{15[1]}', '{16[1]}', '{17[1]}', '{18[1]}', '{19[1]}', \
'{20[1]}', '{21[1]}', '{22[1]}', '{23[1]}', '{24[1]}'\
)""".format(*r4_0_items)


the_cursor.execute(sys_col_table_statement)
#the_cursor.execute(insert_statement)
the_cursor.execute(insert_statement2)
the_database.commit()

供参考：
unpack dictionary
string formatting examples