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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [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.
