Преобразование 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={"User-Agent": "Xe/feedfetch"})
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:
$ sqlite3 data.db < schema.sql
Символ "меньше" является перенаправлением, он загружает данные из 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": ["foo", "bar"]}', '$.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 по своему усмотрению, и все данные будут автоматически обновляться. Это, вероятно, можно значительно упростить с помощью сгенерированных столбцов, однако это должно работать превосходно для большинства нужд.
