来自jsonb的SQLAlchemy Pandas read_sql [英] SQLAlchemy Pandas read_sql from jsonb

问题描述

我想使用pandas read_sql 从我的sqlalchemy查询中，并使用PostgreSQL的 jsonb 列的属性.

I want to generate a dataframe with pandas read_sql from my sqlalchemy query with a PostgreSQL's jsonb attribute to columns.

实际上，这将给我我的答案:

Actually this will give me my answer:

query = session.query(
    cls.id,
    cls._my_jsonb_column
).all()
pd.DataFrame.from_dict([dict(id=id_, **i) for id_,i in query])

但是我宁愿用PostgreSQL而不是在应用程序中解压jsonb.

But I would prefer to unpack the jsonb with PostgreSQL instead of in the application.

我的尝试给了

query = session.query(
    cls.id,
    func.jsonb_to_record(cls._my_jsonb_column)
)
pd.read_sql(query.statement, query.session.bind)

(psycopg2.NotSupportedError)函数返回在无法接受类型记录的上下文中调用的记录

(psycopg2.NotSupportedError) function returning record called in context that cannot accept type record

推荐答案

json_to_record(和jsonb_to_recordset)返回记录，就好像它是SELECT查询的结果一样.在sqlalchemy上下文中，它提供了一个可以像表一样使用的选择.

json_to_record (and jsonb_to_recordset) returns record as if it was the result of a SELECT query. In sqlalchemy context, it provides a selectable which can be used like a table.

因此，您应该将func.jsonb_to_record(cls._my_jsonb_column)的结果视为可以连接到原始表的一种表.

So you should consider the result of func.jsonb_to_record(cls._my_jsonb_column) as a sort of table you can join to you your original table.

那您的查询应如下所示:

That is you query should look like:

jsonb_data = func.jsonb_to_record(cls._my_jsonb_column)
query = session.query(
    select(
        [cls.id, <other columns>]
    ).select_from(
        cls.join(jsonb_data, <on_clause>)
    )
)

您甚至可以使用 JSON处理功能来展平JSON数据.但是如果不了解JSON数据的结构，就不可能更精确.

You could even flatten your JSON data using JSON processing functions but it is not possible to be more precise without knowing the structure of the JSON data.

或者，我最近发布了一个软件包，可以轻松地将jsonB描述中的JSONB字段展平，我很乐意得到一些反馈:

Alternatively, I recently released a package to easily flatten JSONB fields from a description of the json data, I would be happy to have some feedbacks: pg_jsonb_flattener

这篇关于来自jsonb的SQLAlchemy Pandas read_sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！