Yeah, its a very frustrating aspect of SQL Server. Anyway, a query that works is the following:
SELECT testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags FROM testmodel WHERE (testmodel.flags & 1) > 0 I can get sqlalchemy to emit this like so: session.query(TestModel).filter(TestModel.flag_one) And the negation of it: session.query(TestModel).filter(not_(TestModel.flag_one)) I can't figure out how to emit the required SQL on comparison with a boolean value though. Alex On Tue, Apr 1, 2014 at 1:54 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Apr 1, 2014, at 6:34 AM, Alex <quixop...@googlemail.com> wrote: > > Hmm, looks like I spoke too soon. Testing against a SQLite database the > hybrid attribute approach works fine but I'm having some trouble with SQL > Server. Basically, given the structure that Michael laid out, the following > query: > > model = TestModel( > flags=1 > ) > session.add(model) > session.commit() > result = session.query(TestModel).filter(TestModel.flag_one == > True).first() > > Resullts in this exception: > > ProgrammingError: (ProgrammingError) (102, "Incorrect syntax near > '='.DB-Lib error message 102, severity 15: > General SQL Server error: Check messages from the SQL Server > ") 'SELECT TOP 1 testmodel.id AS testmodel_id, testmodel.flags AS > testmodel_flags > FROM testmodel > WHERE ((testmodel.flags & %(flags_1)s) > %(param_1)s) = 1' {'flags_1': 1, > 'param_1': 0} > > So it looks like the equality comparison is coercing True to 1, I can't > figure out which hook I need to use to change this. I've tried to use > coerce_compared_value with no effect. > > > SQL server doesn't have a "boolean" type, you can only use one and zero. > the issue there is more likely the bitwise comparison operators or the > nesting of the parenthesis. get that query to work first at the SQL > server console to figure out the syntax it wants. > > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/Mu9m1dVU1Gw/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.