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

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

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（）