将JSON转换为SQLite表

Question

我想把我创建的一个JSON文件转换成SQLite数据库。

我的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"
    },
"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"
    }
}

我用这些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))''')

我该怎么做才能程序化地读取JSON文件中每个“块”的键（在这个例子中是“local”、“coord”、“sentido”、“veiculos”、“modalidade”、“regime”、“pistas”和“medicoes”），按照这个顺序创建数据库的列，然后再插入正确的值到相应的行里呢？

Answer 1

使用流行的数据处理库 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()

Answer 2

你有这样一段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 (?,?,?,?,?,?,?)