使用`sqlalchemy.types.JSON`数据类型使用SQLAlchemy查询JSON

时间:2022-03-14 17:14:30

I have the following table in SQLAlchemy:

我在SQLAlchemy中有以下表:

class FooModel(Base):
    __tablename__ = 'table'
    id = sqla.Column('id', INTEGER, primary_key=True)
    info = sqla.Column(JSON)

And I want to execute the following query:

我想执行以下查询:

result = session.query(FooModel).filter(
    FooModel.info['key1'].astext.cast(INTEGER) == 1).all()

The above query works just fine as long as the info column in my table is of type sqlalchemy.dialects.postgresql.JSON. However, if I use JSON from sqlalchemy.JSON I get the following error: AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'astext'.

只要我的表中的info列是sqlalchemy.dialects.postgresql.JSON类型,上面的查询就可以正常工作。但是,如果我使用sqlalchemy.JSON中的JSON,我会收到以下错误:AttributeError:'BinaryExpression'对象和'Comparator'对象都没有属性'astext'。

Since the above is the most basic filter one can have, does this mean that in order to do any kind of querying you have to use types from a specific SQL dialect? In other words: Is it possible to modify the above query and make it run when I use the sqlalchemy.JSON type? (which will allow the code to work on both PostreSQL and MySQL)

由于以上是最基本的过滤器,这是否意味着为了进行任何类型的查询,您必须使用特定SQL方言中的类型?换句话说:当我使用sqlalchemy.JSON类型时,是否可以修改上述查询并使其运行? (这将允许代码在PostreSQL和MySQL上工作)

1 个解决方案

#1


0  

Maybe you can try it as follow from sqlalchemy import JSON from sqlalchemy.sql.expression import cast result = session.query(FooModel).filter(FooModel.info['key1'] == cast(1, JSON).all()

也许你可以尝试从sqlalchemy导入JSON从sqlalchemy.sql.expression导入转换结果= session.query(FooModel).filter(FooModel.info ['key1'] == cast(1,JSON).all()

#1


0  

Maybe you can try it as follow from sqlalchemy import JSON from sqlalchemy.sql.expression import cast result = session.query(FooModel).filter(FooModel.info['key1'] == cast(1, JSON).all()

也许你可以尝试从sqlalchemy导入JSON从sqlalchemy.sql.expression导入转换结果= session.query(FooModel).filter(FooModel.info ['key1'] == cast(1,JSON).all()