在Python中将JSON转换为SQLite-如何将JSON键正确映射到数据库列？

Question

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

我的意图是稍后决定最好的数据容器和入口点是json（通过文本编辑器输入数据）或SQLite（通过类似电子表格的GUI（例如SQLiteStudio）输入数据）。

我的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文件中的每个“块”中读取密钥（在这种情况下，是“本地”，“协调”，“ sentido”，“
veiculos”，“ modalidade” “，” regime“，” pistas“和”
medicoes“），以相同的顺序创建具有列的数据库，然后插入具有适当值的行”？

我在Python方面有丰富的经验，但是从SQL才刚刚开始，所以我想对良好的做法提供一些建议，但不一定是现成的食谱。

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 (?,?,?,?,?,?,?)