#python #postgresql #sqlalchemy
#python #postgresql #sqlalchemy
Вопрос:
Я пытаюсь вернуть некоторые именованные столбцы из набора данных jsonb, который хранится в PostgreSQL.
Я могу выполнить необработанный запрос, который напрямую отвечает моим потребностям, однако я пытаюсь выполнить запрос с использованием SQLAlchemy, чтобы убедиться, что мой код «pythonic» и легко читается.
Запрос, который возвращает правильный результат (два столбца), является:
SELECT
tmp.item->>'id',
tmp.item->>'name'
FROM (SELECT jsonb_array_elements(t.data -> 'users') AS item FROM tpeople t) as tmp
Пример json (у каждого пользователя более 20 столбцов)
{ "results":247, "users": [
{"id":"202","regdate":"2015-12-01","name":"Bob Testing"},
{"id":"87","regdate":"2014-12-12","name":"Sally Testing"},
{"id":"811", etc etc}
...
]}
Таблица достаточно проста, с PK, datetime извлечения json и столбцом jsonb для извлечения
CREATE TABLE tpeople
(
record_id bigint NOT NULL DEFAULT nextval('"tpeople_record_id_seq"'::regclass) ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
scrape_time timestamp without time zone NOT NULL,
data jsonb NOT NULL,
CONSTRAINT "tpeople_pkey" PRIMARY KEY (record_id)
);
Кроме того, у меня есть класс People, который выглядит следующим образом:
class people(Base):
__tablename__ = 'tpeople'
record_id = Column(BigInteger, primary_key=True, server_default=text("nextval('"tpeople_record_id_seq"'::regclass)"))
scrape_time = Column(DateTime, nullable=False)
data = Column(JSONB(astext_type=Text()), nullable=False)
В настоящее время мой код для возврата двух столбцов выглядит следующим образом:
from db.db_conn import get_session // Generic connector for my db
from model.models import people
from sqlalchemy import func,
sess = get_session()
sub = sess.query(func.jsonb_array_elements(people.data["users"]).label("item")).subquery()
test = sess.query(sub.c.item).select_entity_from(sub).all()
SQLAlchemy генерирует следующий SQL:
SELECT anon_1.item AS anon_1_item
FROM (SELECT jsonb_array_elements(tpeople.data -> %(data_1)s) AS item
FROM tpeople) AS anon_1
{'data_1': 'users'}
Но ничто из того, что я, кажется, делаю, не может позволить мне получать только определенные столбцы внутри самого элемента, например, необработанный SQL, который я могу написать. Некоторые из подходов, которые я пробовал следующим образом (все они выдают ошибку):
test = sess.query("sub.item.id").select_entity_from(sub).all()
test = sess.query(sub.item.["id"]).select_entity_from(sub).all()
aas = func.jsonb_to_recordset(people.data["users"])
res = sess.query("id").select_from(aas).all()
sub = select(func.jsonb_array_elements(people.data["users"]).label("item"))
В настоящее время я могу извлечь нужные мне столбцы в простом цикле for, но это кажется хакерским способом сделать это, и я уверен, что есть что-то совершенно очевидное, чего мне не хватает.
for row in test:
print(row.item['id'])
Ответ №1:
Поиск в течение нескольких часов в конечном итоге нашел тех, кто случайно сделал это, пытаясь получить другой результат.
sub = sess.query(func.jsonb_array_elements(people.data["users"]).label("item")).subquery()
tmp = sub.c.item.op('->>')('id')
tmp2 = sub.c.item.op('->>')('name')
test = sess.query(tmp, tmp2).all()
Комментарии:
1. Вы можете использовать
.astext
вместо явногоop()
метода.