Let's say that I have a table such as this:
CREATE TABLE foo (
id SERIAL PRIMARY KEY,
attribute_toggles INT DEFAULT NULL
);
CREATE INDEX idx_attribute_toggles ON foo(CAST(attribute_toggles AS
BIT(32))) WHERE attribute_toggles <> 0;
represented by such a class
class Foo():
__tablename__ = 'foo'
id = Column(Integer(primary_key=True))
attribute_toggles = Column(Integer())
and I need to generate the sql:
SELECT * FROM foo WHERE (CAST(attribute_toggles AS BIT(32)) & CAST(1
AS BIT(32)) <> CAST (0 AS BIT(32))) AND attribute_toggles > 0;
The casting and `>0` comparison are used to filter on the index instead of
a sequential scan. that drops a query from around 4000ms to 30ms.
Is it ok/reliable just doing this:
foos = session.query(Foo)\
.filter("""(cast(attribute_toggles as bit(32)) & cast(:bit_wanted
as bit(32)) <> cast(0 as bit(32))) AND attribute_toggles > 0""",
)\
.params(bit_wanted = bit_wanted,
)\
.order_by(Foo.id.desc())\
.limit(limit)\
.offset(offset)\
.all()
Writing out the filter in SqlAlchemy was getting messy, so I went for a
text filter.
My concern is that the textual filter doesn't specify what
`attribute_toggles` is. The sqlalchemy core has a somewhat similar
`columns` construct for statements, but that's not applicable here.
i made a few attempts to specify the columns using various sqlalchemy
features that I'm familiar with, but i had no luck.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
