On 09/27/2016 05:12 PM, Jonathan Vanasco wrote:
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.
well it will work as long as that column name is non-ambiguous. If you started using Foo + FooAlias together, then it would have problems. In that case using plain cast() / BIT / op() would be the quickest solution. Or a @compiles construct if you really want the string thing to be present.
-- 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 sqlalchemy+unsubscr...@googlegroups.com <mailto:sqlalchemy+unsubscr...@googlegroups.com>. To post to this group, send email to sqlalchemy@googlegroups.com <mailto:sqlalchemy@googlegroups.com>. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
-- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.