将JSON转换为SQLite表

Question

我希望将我创建的JSON文件转换为SQLite数据库。

我的意图是稍后决定哪个数据容器和入口点最好，json（通过文本编辑器进行数据输入）还是SQLite（通过类似于SQLiteStudio的电子表格GUI进行数据输入）。

我的JSON文件如下所示（包含来自我城市某些十字路口的交通数据）：

...
"2011-12-17 16:00": {
    "local": "Av. Protásio Alves; esquina Ramiro Barcelos",
    "coord": "-30.036916,-51.208093",
    "sentido": "bairro-centro",
    "veiculos": "automotores",
    "modalidade": "semaforo 50-15",
    "regime": "típico",
    "pistas": "2+c",
    "medicoes": [
        [32, 50],
        [40, 50],
        [29, 50],
        [32, 50],
        [35, 50]
        ]
    },
"2011-12-19 08:38": {
    "local": "R. Fernandes Vieira; esquina Protásio Alves",
    "coord": "-30.035535,-51.211079",
    "sentido": "único",
    "veiculos": "automotores",
    "modalidade": "semáforo 30-70",
    "regime": "típico",
    "pistas": "3",
    "medicoes": [
        [23, 30],
        [32, 30],
        [33, 30],
        [32, 30]
        ]
    }
...

我已经用以下这些Python代码创建了一个与之具有一对多关系的美好数据库：

import sqlite3

db = sqlite3.connect("fluxos.sqlite")
c = db.cursor()

c.execute('''create table medicoes
         (timestamp text primary key,
          local text,
          coord text,
          sentido text,
          veiculos text,
          modalidade text,
          pistas text)''')

c.execute('''create table valores
         (id integer primary key,
          quantidade integer,
          tempo integer,
          foreign key (id) references medicoes(timestamp))''')

但问题在于，当我准备使用类似于c.execute("insert into medicoes values(?,?,?,?,?,?,?)" % keys)的语句插入实际数据行时，我意识到，由JSON文件加载的字典没有特定顺序，因此无法正确映射到数据库的列顺序。

因此，我的问题是：“我应该使用哪种策略/方法来以编程方式从JSON文件中的每个“块”中读取键（在本例中为“local”，“coord”，“sentido”，“veiculos”，“modalidade”，“regime”，“pistas”和“medicoes”），按照相同的顺序创建具有相应值的数据库列，并插入行？”

我在Python方面有一些经验，但刚开始接触SQL，因此我想了解一些关于良好实践的建议，而不仅仅是一个现成的配方。

- heltonbiker

Answer 1

你有这段Python代码：

c.execute("insert into medicoes values(?,?,?,?,?,?,?)" % keys)

我认为应该是这样的

c.execute("insert into medicoes values (?,?,?,?,?,?,?)", keys)

由于%操作符需要左侧的字符串包含格式化代码，因此您需要做的就是将keys作为一个元组（或列表）包含在正确顺序下medicoes表的新行值中。请考虑以下Python代码：

import json

traffic = json.load(open('xxx.json'))

columns = ['local', 'coord', 'sentido', 'veiculos', 'modalidade', 'pistas']
for timestamp, data in traffic.iteritems():
    keys = (timestamp,) + tuple(data[c] for c in columns)
    print str(keys)

当我使用你的样本数据运行此代码时，我得到：

(u'2011-12-19 08:38', u'R. Fernandes Vieira; esquina Prot\xe1sio Alves', u'-30.035535,-51.211079', u'\xfanico', u'automotores', u'sem\xe1foro 30-70', u'3')
(u'2011-12-17 16:00', u'Av. Prot\xe1sio Alves; esquina Ramiro Barcelos', u'-30.036916,-51.208093', u'bairro-centro', u'automotores', u'semaforo 50-15', u'2+c')

这似乎是你所需的元组。

你可以使用类似以下代码添加必要的sqlite代码：

import json
import sqlite3

traffic = json.load(open('xxx.json'))
db = sqlite3.connect("fluxos.sqlite")

query = "insert into medicoes values (?,?,?,?,?,?,?)"
columns = ['local', 'coord', 'sentido', 'veiculos', 'modalidade', 'pistas']
for timestamp, data in traffic.iteritems():
    keys = (timestamp,) + tuple(data[c] for c in columns)
    c = db.cursor()
    c.execute(query, keys)
    c.close()

编辑：如果你不想硬编码列的列表，你可以像这样做：

import json

traffic = json.load(open('xxx.json'))

someitem = traffic.itervalues().next()
columns = list(someitem.keys())
print columns

当我运行这个程序时，它会打印：

[u'medicoes', u'veiculos', u'coord', u'modalidade', u'sentido', u'local', u'pistas', u'regime']

您可以像这样使用它：

import json
import sqlite3

db = sqlite3.connect('fluxos.sqlite')
traffic = json.load(open('xxx.json'))

someitem = traffic.itervalues().next()
columns = list(someitem.keys())
columns.remove('medicoes')
columns.remove('regime')

query = "insert into medicoes (timestamp,{0}) values (?{1})"
query = query.format(",".join(columns), ",?" * len(columns))
print query

for timestamp, data in traffic.iteritems():
    keys = (timestamp,) + tuple(data[c] for c in columns)
    c = db.cursor()
    c.execute(query)
    c.close()

这段代码在使用你提供的示例数据进行测试时所生成的查询语句类似于：

insert into medicoes (timestamp,veiculos,coord,modalidade,sentido,local,pistas) values (?,?,?,?,?,?,?)

- srgerg

---

我认为你的答案包含了我想要做的大部分内容。我唯一剩下的问题是columns是硬编码的，我希望这些字符串可以从文件中读取。最好是按照相同的顺序。你觉得呢？ - heltonbiker

Answer 2

使用流行的数据处理库pandas，将json转换为sqlite表非常简单，因为pandas会处理大部分的操作。基本上，可以使用pandas.read_json将json文件转换为pandas DataFrame，然后我们可以简单地筛选所需的列并使用to_sql将其导入SQLite表中。

import sqlite3
import pandas as pd

con = sqlite3.connect('data/fluxos.sqlite')
(
    pd.read_json('data/my_json.json', orient='index')
    .filter(['local', 'coord', 'sentido', 'veiculos', 'modalidade', 'pistas'])
    .to_sql('medicoes', con, index_label='timestamp', dtype={'timestamp': 'TEXT PRIMARY KEY'}, if_exists='append')
)
con.close()

---

然而，如果你不想使用内置的库，而是想避免显式地打开/关闭连接并逐行插入数据，你可以事先处理数据并使用executemany让sqlite3处理循环。

import json
import sqlite3


with open('data/my_json.json') as f:
    my_json = json.load(f)

columns = ['local', 'coord', 'sentido', 'veiculos', 'modalidade', 'pistas']
processed_data = [(timestamp, *(data[c] for c in columns)) for timestamp, data in my_json.items()]

con = sqlite3.connect('data/fluxos.sqlite')
cur = con.cursor()

cur.execute('''
CREATE TABLE medicoes (
    timestamp TEXT PRIMARY KEY,
    local TEXT,
    coord TEXT,
    sentido TEXT,
    veiculos TEXT,
    modalidade TEXT,
    pistas TEXT)
''')

cur.executemany("INSERT INTO medicoes VALUES (?, ?, ?, ?, ?, ?, ?)", processed_data)
con.commit()
cur.close()
con.close()

- cottontail