Konwertuj JSON na SQLite w Pythonie - jak poprawnie mapować klucze json do kolumn bazy danych?

Question

Konwertuj JSON na SQLite w Pythonie - jak poprawnie mapować klucze json do kolumn bazy danych?

Chcę przekonwertować utworzony przeze mnie plik JSON do bazy danych SQLite.

Moim zamiarem jest później zdecydować, który kontener danych i punkt wejścia jest najlepszy, json (wprowadzanie danych przez edytor tekstu) lub SQLite(wprowadzanie danych przez arkusz kalkulacyjny-jak Gui jak SQLiteStudio).

Mój plik json jest taki (zawierający dane o ruchu drogowym z jakiegoś skrzyżowania w moim mieście):

...
"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]
        ]
    }
...

I stworzyłem ładną bazę danych z relacją jeden do wielu z tymi liniami kodu Pythona:

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))''')

Ale problem w tym, że kiedy przygotowywałem się do wstawiania wierszy z rzeczywistymi danymi za pomocą czegoś w rodzaju c.execute("insert into medicoes values(?,?,?,?,?,?,?)" % keys), Zdałem sobie sprawę, że ponieważ dict załadowany z pliku JSON nie ma specjalnego porządku, nie mapuje poprawnie do kolejności kolumn bazy danych.

Więc pytam: "jakiej strategii/metody powinienem użyć, aby programowo odczytać klucze z każdego "bloku" w pliku JSON (w tym przypadku "local", "coord"," sentido"," veiculos", "modalidade", "regime", "pistas" e "medicoes"), utworzyć bazę danych z kolumny w tej samej kolejności, a następnie wstawić wiersze z odpowiednimi wartościami"?

Mam dobre doświadczenie z Pythonem, ale dopiero zaczynam z SQL, więc chciałbym zasięgnąć porady na temat dobrych praktyk, a niekoniecznie gotowej receptury.

39

python json database-design sqlite

Author: A-B-B, 2012-01-11

Source

1 answers

score 41 · Accepted Answer

Masz ten kod Pythona:

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

Które moim zdaniem powinno być

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

Ponieważ % operator oczekuje, że ciąg znaków po lewej stronie będzie zawierał kody formatowania.

Teraz wszystko, co musisz zrobić, to aby keys była krotką (lub listą) zawierającą wartości dla nowego wiersza tabeli medicoes w odpowiedniej kolejności. Rozważ następujący kod Pythona:

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)

Kiedy uruchamiam to z Twoimi przykładowymi danymi, otrzymuję:

(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')

Które to chyba krotki, których potrzebujesz.

Możesz dodać niezbędny kod sqlite za pomocą czegoś takiego:

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

Edit: jeśli nie chcesz twardo kodować listy kolumn, możesz zrobić coś takiego:

import json

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

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

Kiedy uruchamiam to drukuje:

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

Przydałoby się coś takiego:

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

Zapytanie, które wyświetla ten kod, gdy próbuję go z Twoimi przykładowymi danymi, wygląda mniej więcej tak:

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