Преобразование JSON в форму с помощью SQLite
Очевидно, что большая часть мира решила, что они хотят использовать JSON для своих общедоступных конечных точек API. Однако большую часть времени вам придется иметь дело с механизмами хранения, которые не очень хорошо работают с JSON. Это может привести к путанице, потому что вам нужно вставить квадратный колышек в круглое отверстие.
Однако в SQLite добавлены функции JSON, позволяющие вам изменять и изменять данные JSON любым творческим способом. Вы можете использовать эти триггеры и триггеры SQLite для автоматического преобразования JSON в таблицы любого типа, которые вы хотите. Добавьте дополнительные настройки, и вы сможете сделать все еще более автоматизированным. Эта поддержка была добавлена в SQLite 3.9.0 (выпущена в 2015 году), поэтому, если предположить, что Debian не отключил ее без уважительной причины, вы сможете использовать ее сегодня.
В этом примере мы собираемся запрашивать общедоступные конечные точки JSONFeed и превращать их в таблицы SQL. Начнем со схемы таблицы, которая выглядит так:
CREATE TABLE IF NOT EXISTS jsonfeed_raw ( feed_url TEXT PRIMARY KEY , scrape_date TEXT NOT NULL DEFAULT (DATE('now')) , raw TEXT NOT NULL ); scrape date — это, по сути, дата, когда строка JSONFeed была вставлена в базу данных. Это может быть полезно при написании других частей стека для автоматического запроса фидов на наличие изменений. Это оставлено читателю в качестве упражнения.
Затем вы можете вставлять данные в базу данных SQLite, используя модуль Python sqlite3 :
#!/usr/bin/env nix-shell #! nix-shell -p python39 --run python import sqlite3 import urllib.request con = sqlite3.connect("data.db") def get_feed(feed_url): req = urllib.request.Request(feed_url, headers=) with urllib.request.urlopen(req) as response: cur = con.cursor() body = response.read() cur.execute(""" INSERT INTO jsonfeed_raw (feed_url, raw) VALUES (?, json(?)) """, (feed_url, body)) con.commit() print("got feed %s" % (feed_url)) get_feed("https://christine.website/blog.json") Итак, теперь давайте поиграем с данными! Давайте загрузим схему базы данных с помощью команды sqlite3 :
Символ «меньше» является перенаправлением, он загружает данные из schema.sql в качестве стандартного ввода в команду sqlite .
Затем запустите этот скрипт Python для заполнения базы данных:
$ python ./jsonfeedfetch.py got feed https://christine.website/blog.json Затем откройте командную строку SQLite:
$ sqlite3 data.db SQLite version 3.36.0 2021-06-18 18:36:39 Enter ".help" for usage hints. sqlite> А теперь мы можем поиграть с некоторыми функциями JSON. Сначала продемонстрируем json_extract . Это позволяет вам извлечь значение из объекта JSON. Например, давайте возьмем заголовок фида из JSONFeed моего сайта:
sqlite> select json_extract(raw, '$.title') from jsonfeed_raw; Xe's Blog Мы можем использовать эту функцию, чтобы помочь нам создать таблицу, в которой хранятся метаданные, которые нам нужны, из JSONFeed, например:
CREATE TABLE IF NOT EXISTS jsonfeed_metadata ( feed_url TEXT PRIMARY KEY , title TEXT NOT NULL , description TEXT , home_page_url TEXT , updated_at TEXT NOT NULL DEFAULT (DATE('now')) ); Если вы спросите моих коллег, они подтвердят, что в реальной жизни я действительно неиронично пишу SQL таким образом.
Затем мы можем заполнить эту таблицу запросом, подобным этому:
INSERT INTO jsonfeed_metadata ( feed_url , title , description , home_page_url , updated_at ) SELECT jsonfeed_raw.feed_url AS feed_url , json_extract(jsonfeed_raw.raw, '$.title') AS title , json_extract(jsonfeed_raw.raw, '$.description') AS description , json_extract(jsonfeed_raw.raw, '$.home_page_url') AS home_page_url , DATE('now') AS updated_at FROM jsonfeed_raw; Ключевое слово AS позволяет связать значения в операторе SELECT с именами для использования в другом месте запроса.Я не знаю, нужно ли это *strictly*, однако это заставляет имена выстраиваться в линию, и SQLite не жалуется на это, так что, вероятно, все в порядке.
Теперь это работает, однако знаете, что проще, чем писать операторы в консоли SQLite вот так? Триггеры SQLite позволяют нам автоматически запускать операторы базы данных при возникновении определенных условий. Основное условие, о котором мы хотим позаботиться прямо сейчас, — это когда мы вставляем новые данные. Мы можем превратить этот оператор в триггер после вставки следующим образом:
CREATE TRIGGER IF NOT EXISTS jsonfeed_raw_ins AFTER INSERT ON jsonfeed_raw BEGIN INSERT INTO jsonfeed_metadata ( feed_url , title , description , home_page_url ) VALUES ( NEW.feed_url , json_extract(NEW.raw, '$.title') , json_extract(NEW.raw, '$.description') , json_extract(NEW.raw, '$.home_page_url') ); END; Затем мы можем запустить несколько команд, чтобы уничтожить все состояние базы данных:
sqlite3> DELETE FROM jsonfeed_metadata; sqlite3> DELETE FROM jsonfeed_raw;И снова запустите этот скрипт Python, после чего данные должны появиться автоматически:
sqlite3> SELECT * FROM jsonfeed_metadata; https://christine.website/blog.json|Xe's Blog|My blog posts and rants about various technology things.|https://christine.website|2022-01-04 Однако, если вы снова запустите этот скрипт Python, не удаляя строки, вы получите нарушение первичного ключа. Мы можем исправить это, превратив вставку в upsert примерно так:
cur.execute(""" INSERT INTO jsonfeed_raw (feed_url, raw) VALUES (?, json(?)) ON CONFLICT DO UPDATE SET raw = json(?) """, (feed_url, body, body)) А также сделайте дополнительный триггер обновления для таблицы jsonfeed_raw :
CREATE TRIGGER IF NOT EXISTS jsonfeed_raw_upd AFTER UPDATE ON jsonfeed_raw BEGIN INSERT INTO jsonfeed_metadata ( feed_url , title , description , home_page_url ) VALUES ( NEW.feed_url , json_extract(NEW.raw, '$.title') , json_extract(NEW.raw, '$.description') , json_extract(NEW.raw, '$.home_page_url') ) ON CONFLICT DO UPDATE SET title = json_extract(NEW.raw, '$.title') , description = json_extract(NEW.raw, '$.description') , home_page_url = json_extract(NEW.raw, '$.home_page_url') Вероятно, вам следует обновить исходный триггер, чтобы он тоже был upsert. Вы можете следовать этому триггеру в качестве руководства. Но обязательно DROP TRIGGER jsonfeed_raw_upd; cначала!
Мы также можем очистить элементы фида с помощью json_each . json_each позволяет перебирать массив JSON и возвращает строки SQLite для каждого значения в этом массиве. Например:
sqlite> select * from json_each('["foo", "bar"]'); 0|foo|text|foo|1||$[0]|$ 1|bar|text|bar|2||$[1]|$ Схему для временной таблицы, которую json_each (и связанную с ней json_tree ) использует можно найти здесь. Вы также можете получить что-то из списка в объекте со вторым аргументом json_each , так что вы можете делать такие вещи:
sqlite> select * from json_each('', '$.spam'); 0|foo|text|foo|3||$.spam[0]|$.spam 1|bar|text|bar|4||$.spam[1]|$.spam Используя это, мы можем создать таблицу для каждого из элементов фида, которая выглядит примерно так:
CREATE TABLE IF NOT EXISTS jsonfeed_posts ( url TEXT PRIMARY KEY , feed_url TEXT NOT NULL , title TEXT NOT NULL , date_published TEXT NOT NULL ); А затем удалить все данные из базы данных с помощью такого запроса:
INSERT INTO jsonfeed_posts ( url , feed_url , title , date_published ) SELECT json_extract(json_each.value, '$.url') AS url , jsonfeed_raw.feed_url AS feed_url , json_extract(json_each.value, '$.title') AS title , json_extract(json_each.value, '$.date_published') AS date_published FROM jsonfeed_raw , json_each(jsonfeed_raw.raw, '$.items'); Это извлечет все значения поля items в каждом JSONFeed, а затем автоматически заполнит их в таблице jsonfeed_posts . Однако превращение этого в триггер при наивном подходе не сработает мгновенно.
Допустим, у нас есть форма триггера, которая выглядит так:
CREATE TRIGGER IF NOT EXISTS jsonfeed_raw_upd_posts AFTER INSERT ON jsonfeed_raw BEGIN INSERT INTO jsonfeed_posts ( url , feed_url , title , date_published ) SELECT json_extract(json_each.value, '$.url') AS url , NEW.feed_url AS feed_url , json_extract(json_each.value, '$.title') AS title , json_extract(json_each.value, '$.date_published') AS date_published FROM json_each(NEW.raw, '$.items') ON CONFLICT DO UPDATE SET title = excluded.title , date_published = excluded.date_published ; END; Если вы вставите это в свою консоль SQLite, вы получите эту ошибку:
Error: near "DO": syntax error На самом деле это связано с неоднозначностью синтаксического анализа в SQLite. Для того, чтобы исправить это вам нужно будет добавить WHERE TRUE между пунктами триггера FROM и ON CONFLICT :
-- . FROM json_each(NEW.raw, '$.items') WHERE TRUE ON CONFLICT DO -- . Таким образом день спасает wheretrue, скрытый высший хищник царства SQLite, роковое значение, которое не является ложным только ночью. Плачьте от ужаса, как бы он не добавил вас к своей таблице жертв!
Коррелирующее изменение триггера вставки также является упражнением для читателя.
Теперь вы можете добавлять JSONFeeds по своему усмотрению, и все данные будут автоматически обновляться. Это, вероятно, можно значительно упростить с помощью сгенерированных столбцов, однако это должно работать превосходно для большинства нужд.
Storing JSON in a SQLite table using Python
In this tutorial, we’ll learn how to populate a SQLite database table with JSON data types using Python sqlite3 module. A Linux machine with Python3 and SQLite version > 3.3 (Contains JSON1 extension. In previous versions of SQLite can be installed following the JSON1 official documentation) will be required to follow this tutorial.
Retrieve data from a public API using Python
Retrieves a list of countries from the World Bank API using requests Python library (First 100 countries). countries is a Python dict array.
$ sudo apt update $ sudo apt install python3-pip # if Python pip needs to be installed $ pip3 install requests # if requests needs to be installed $ python3 >>> import requests >>> countries_api_res = requests.get('http://api.worldbank.org/countries?format=json&per_page=100') >>> countries = countries_api_res.json()[1] >>> print(len(countries)) 100 >>> import pprint >>> pprint.pprint(countries[0]) 'adminregion': 'id': '', 'value': ''>, 'capitalCity': 'Oranjestad', 'id': 'ABW', 'incomeLevel': 'id': 'HIC', 'value': 'High income'>, 'iso2Code': 'AW', 'latitude': '12.5167', 'lendingType': 'id': 'LNX', 'value': 'Not classified'>, 'longitude': '-70.0167', 'name': 'Aruba', 'region': 'id': 'LCN', 'value': 'Latin America & Caribbean '>> Create a table using Python SQLite driver
Using Python and it’s driver for SQLite we are creating a connection to the test.db database. This allow us to create a table countries with a SQLite-JSON based field called data :
$ python3 >>> import sqlite3 >>> conn = sqlite3.connect('test.db') >>> c = conn.cursor() >>> c.execute("CREATE TABLE countries (id varchar(3), data json)") sqlite3.Cursor object at 0x7f32fa57cf10> Insert values into a SQLite table with a JSON column using Python
Loops array countries and inserts them one by one:
$ python3 >>> import json >>> for country in countries: . c.execute("insert into countries values (?, ?)", . [country['id'], json.dumps(country)]) . conn.commit() >>> conn.close() Query JSON-SQLite values
Finally we can retrieve stored values in countries table. For example, getting a list of country names by accessing the attribute name on the JSON type data field:
$ apt-get install -y sqlite3 # or equivalent in your OS, if sqlite3 needs to be installed $ sqlite3 --version 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1 $ sqlite3 test.db sqlite3> select json_extract(data, '$.name') from countries; > Aruba Afghanistan Africa Angola Albania Andorra Andean Region . Or fetching one full JSON object randomly and pretty-printing it using Python JSON formatter module:
$ sqlite3 test.db "select data from countries order by random() limit 1" | python -m json.tool "adminregion": "id": "", "value": "" >, "capitalCity": "Berlin", "id": "DEU", "incomeLevel": "id": "HIC", "value": "High income" >, "iso2Code": "DE", "latitude": "52.5235", "lendingType": "id": "LNX", "value": "Not classified" >, "longitude": "13.4115", "name": "Germany", "region": "id": "ECS", "value": "Europe & Central Asia" > > Although we’ve used Python in this post, there are lot of other programming languages that can be used to easily query JSON in SQLite like PHP.
Finally, you should definitely take a look at these books to fuel your SQLite knowledge: