JSON2SQLite.py

# Êîíâåðòàöèÿ JSON â SQLite íà Python
# https://dzen.ru/subscriptions-manager?channel=615c3ace71bd4944acea8a67
# Ïðèìåð JSON-äàííûõ
# Äîïóñòèì, ÷òî ó íàñ åñòü ôàéë users.json ñî ñëåäóþùèì ñîäåðæèìûì:

[
{
"id": 1,
"name": "Anna",
"email": "anna@example.com"
},
{
"id": 2,
"name": "Ivan",
"email": "ivan@example.com"
}
]

# Ñêðèïò äëÿ êîíâåðòàöèè
# Ïåðåä íàïèñàíèåì ñêðèïòà, ïåðâûì äåëîì ìû èìïîðòèðóåì ìîäóëü sqlite3 äëÿ ðàáîòû ñ ÁÄ, à òàêæå ìîäóëü json äëÿ ðàáîòû ñ íàøèì json-ôàéëîì:

import json
import sqlite3

# Çàãðóçèì äàííûå èç json-ôàéëà, èñïîëüçóÿ êîíòåêñòíûé ìåíåäæåð with … as:

import json
import sqlite3

# Çàãðóæàåì JSON-äàííûå
with open('users.json', 'r', encoding='utf-8') as f:
data = json.load(f)

# Ïîäêëþ÷èìñÿ ê áàçå äàííûõ SQLite ïîä íàçâàíèåì users.db (áóäåò ñîçäàíà, åñëè å¸ íå ñóùåñòâóåò):

import json
import sqlite3

with open('users.json', 'r', encoding='utf-8') as f:
data = json.load(f)

# Ïîäêëþ÷àåìñÿ ê SQLite (ñîçäàñò ôàéë, åñëè åãî íåò)
conn = sqlite3.connect('users.db')
cursor = conn.cursor()

# Ñîçäàäèì òàáëèöó, ïðåäâàðèòåëüíî óäàëèâ å¸, ÷òîáû èçáåæàòü îøèáîê â ñëó÷àå, åñëè îíà óæå ñóùåñòâîâàëà:

import json
import sqlite3

with open('users.json', 'r', encoding='utf-8') as f:
data = json.load(f)

conn = sqlite3.connect('users.db')
cursor = conn.cursor()

# Ñîçäà¸ì òàáëèöó (åñëè íóæíî, óäàëÿåì ïåðåä ýòèì)
cursor.execute('DROP TABLE IF EXISTS users')
cursor.execute('''
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL
)
''')

# Èñïîëüçóÿ öèêë âñòàâèì äàííûå èç json-ôàéëà â òàáëèöó áàçû äàííûõ:

import json
import sqlite3

with open('users.json', 'r', encoding='utf-8') as f:
data = json.load(f)

conn = sqlite3.connect('users.db')
cursor = conn.cursor()

cursor.execute('DROP TABLE IF EXISTS users')
cursor.execute('''
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL
)
''')

# Âñòàâëÿåì äàííûå
for user in data:
cursor.execute('''
INSERT INTO users (id, name, email)
VALUES (:id, :name, :email)
''', user)

# Ñîõðàíèì èçìåíåíèÿ è çàêðûâàåì ñîåäèíåíèå:

import json
import sqlite3

with open('users.json', 'r', encoding='utf-8') as f:
data = json.load(f)

conn = sqlite3.connect('users.db')
cursor = conn.cursor()

cursor.execute('DROP TABLE IF EXISTS users')
cursor.execute('''
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL
)
''')

for user in data:
cursor.execute('''
INSERT INTO users (id, name, email)
VALUES (:id, :name, :email)
''', user)

# Ñîõðàíÿåì èçìåíåíèÿ è çàêðûâàåì ñîåäèíåíèå
conn.commit()
conn.close()