Преобразование JSON в SQLite в Python - Как правильно сопоставить json-ключи с столбцами базы данных?

Я хочу преобразовать файл JSON, созданный в базу данных SQLite.

Я намерен позже решить, какой контейнер данных и точка входа лучше всего, json (ввод данных через текстовый редактор) или SQLite (ввод данных через графические интерфейсы, подобные 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), я понял, что, поскольку в файле dict, загруженном из файла JSON, нет специального порядка, он неправильно отображает порядок столбцов базы данных.

Итак, я спрашиваю: "какую стратегию/метод следует использовать для программного чтения ключей из каждого" блока "в файле JSON (в данном случае" локальный "," координирующий "," отправленный "," veiculos "," modalidade "," mode "," pistas "e" medicoes"), создать базу данных с столбцами в том же порядке, а затем вставить строки с соответствующими значениями??

У меня есть хороший опыт работы с Python, но я только начинаю с SQL, поэтому я хотел бы получить некоторые рекомендации о хороших практиках и не обязательно готовый рецепт.

Спасибо за чтение!

Ответы

Ответ 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 (?,?,?,?,?,?,?)