sql server expression negation -- > "Statement(s) could not be prepared."

[dweitzenfeld]

class Member(MssqlBase):

    __tablename__ = "MemberFacts"

    __table_args__ = {'autoload': True, 'autoload_with': mssql.engine}

    @hybrid_property

    def is_legacy(self):

        return self.Rtid[:4] == '0000'

    @is_legacy.expression

    def is_legacy(cls):

        return cls.Rtid.startswith('0000')

This works just fine:

m = session.query(Member).filter(Member.is_legacy).limit(20).all()  

But when I negate the expression, it fails.  I've bolded the relevant part of the query produced by sqlalchemy below.  

m = session.query(Member).filter(Member.is_legacy == False).limit(20).all()

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2300, in all

    return list(self)

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2412, in __iter__

    return self._execute_and_instances(context)

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2427, in _execute_and_instances

    result = conn.execute(querycontext.statement, self._params)

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute

    return meth(self, multiparams, params)

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 321, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement

    compiled_sql, distilled_params

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context

    context)

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in _handle_dbapi_exception

    exc_info

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb)

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context

    context)

  File "/Users/weitzenfeld/Envs/wilbur/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL Server]Statement(s) could not be prepared. (8180) (SQLExecDirectW)') "SELECT anon_1.[MemberFacts_MemberId] AS [anon_1_MemberFacts_MemberId], anon_1.[MemberFacts_AffinionId] AS [anon_1_MemberFacts_AffinionId], anon_1.[MemberFacts_DateCreated] AS [anon_1_MemberFacts_DateCreated], anon_1.[MemberFacts_DateModified] AS [anon_1_MemberFacts_DateModified], anon_1.[MemberFacts_Email] AS [anon_1_MemberFacts_Email], anon_1.[MemberFacts_Rtid] AS [anon_1_MemberFacts_Rtid], anon_1.[MemberFacts_BiographicsId] AS [anon_1_MemberFacts_BiographicsId], anon_1.[MemberFacts_EnrollmentStateId] AS [anon_1_MemberFacts_EnrollmentStateId], anon_1.[MemberFacts_LegacyStateId] AS [anon_1_MemberFacts_LegacyStateId], anon_1.[MemberFacts_MemberStateId] AS [anon_1_MemberFacts_MemberStateId], anon_1.[MemberFacts_IsLegacy] AS [anon_1_MemberFacts_IsLegacy], anon_1.[MemberFacts_EnrollmentState] AS [anon_1_MemberFacts_EnrollmentState], anon_1.[MemberFacts_Attendant] AS [anon_1_MemberFacts_Attendant], anon_1.[MemberFacts_KioskId] AS [anon_1_MemberFacts_KioskId], anon_1.[MemberFacts_EnrollmentCompletedDate] AS [anon_1_MemberFacts_EnrollmentCompletedDate], anon_1.[MemberFacts_EnrollmentSource] AS [anon_1_MemberFacts_EnrollmentSource], anon_1.[MemberFacts_SegmentationGroup] AS [anon_1_MemberFacts_SegmentationGroup], anon_1.[MemberFacts_IsActive] AS [anon_1_MemberFacts_IsActive], anon_1.[MemberFacts_IsWebEnabled] AS [anon_1_MemberFacts_IsWebEnabled], anon_1.[MemberFacts_HasCardFirstUsed] AS [anon_1_MemberFacts_HasCardFirstUsed], anon_1.[MemberFacts_IsAirportActivated] AS [anon_1_MemberFacts_IsAirportActivated], anon_1.[MemberFacts_IsDoNotRenew] AS [anon_1_MemberFacts_IsDoNotRenew], anon_1.[MemberFacts_TermCounter] AS [anon_1_MemberFacts_TermCounter], anon_1.[MemberFacts_Pricepoint] AS [anon_1_MemberFacts_Pricepoint], [AffinionCodes_1].[Rtid] AS [AffinionCodes_1_Rtid], [AffinionCodes_1].[ReferrenceCode] AS [AffinionCodes_1_ReferrenceCode], [AffinionCodes_1].[CommissionCode] AS [AffinionCodes_1_CommissionCode], [AffinionCodes_1].[PromoCode] AS [AffinionCodes_1_PromoCode], [AffinionCodes_1].[FamilyCode] AS [AffinionCodes_1_FamilyCode], [AffinionCodes_1].[PromoCodeCategory] AS [AffinionCodes_1_PromoCodeCategory], [AffinionCodes_1].[ReferAFriendCode] AS [AffinionCodes_1_ReferAFriendCode] \nFROM (SELECT TOP 20 [MemberFacts].[MemberId] AS [MemberFacts_MemberId], [MemberFacts].[AffinionId] AS [MemberFacts_AffinionId], [MemberFacts].[DateCreated] AS [MemberFacts_DateCreated], [MemberFacts].[DateModified] AS [MemberFacts_DateModified], [MemberFacts].[Email] AS [MemberFacts_Email], [MemberFacts].[Rtid] AS [MemberFacts_Rtid], [MemberFacts].[BiographicsId] AS [MemberFacts_BiographicsId], [MemberFacts].[EnrollmentStateId] AS [MemberFacts_EnrollmentStateId], [MemberFacts].[LegacyStateId] AS [MemberFacts_LegacyStateId], [MemberFacts].[MemberStateId] AS [MemberFacts_MemberStateId], [MemberFacts].[IsLegacy] AS [MemberFacts_IsLegacy], [MemberFacts].[EnrollmentState] AS [MemberFacts_EnrollmentState], [MemberFacts].[Attendant] AS [MemberFacts_Attendant], [MemberFacts].[KioskId] AS [MemberFacts_KioskId], [MemberFacts].[EnrollmentCompletedDate] AS [MemberFacts_EnrollmentCompletedDate], [MemberFacts].[EnrollmentSource] AS [MemberFacts_EnrollmentSource], [MemberFacts].[SegmentationGroup] AS [MemberFacts_SegmentationGroup], [MemberFacts].[IsActive] AS [MemberFacts_IsActive], [MemberFacts].[IsWebEnabled] AS [MemberFacts_IsWebEnabled], [MemberFacts].[HasCardFirstUsed] AS [MemberFacts_HasCardFirstUsed], [MemberFacts].[IsAirportActivated] AS [MemberFacts_IsAirportActivated], [MemberFacts].[IsDoNotRenew] AS [MemberFacts_IsDoNotRenew], [MemberFacts].[TermCounter] AS [MemberFacts_TermCounter], [MemberFacts].[Pricepoint] AS [MemberFacts_Pricepoint] \nFROM [MemberFacts] \nWHERE ([MemberFacts].[Rtid] LIKE ? + '%%') = 0) AS anon_1 LEFT OUTER JOIN [AffinionCodes] AS [AffinionCodes_1] ON anon_1.[MemberFacts_Rtid] = [AffinionCodes_1].[Rtid]" ('0000',)

It seems to be the " = 0 " that sql server is complaining about.  Is this a known issue? 

I've created a silly workaround, by baking the negation in: 

    @hybrid_property

    def is_not_legacy(self):

        return self.Rtid[:4] != '0000'

    @is_not_legacy.expression

    def is_not_legacy(cls):

        return cls.Rtid.notlike('0000%')

  

[Michael Bayer]

On Aug 7, 2014, at 11:34 AM, dweitzenfeld <dweitz...@gmail.com> wrote:

\nFROM [MemberFacts] \nWHERE ([MemberFacts].[Rtid] LIKE ? + '%%') = 0) AS anon_1 LEFT OUTER JOIN [AffinionCodes] AS [AffinionCodes_1] ON anon_1.[MemberFacts_Rtid] = [AffinionCodes_1].[Rtid]" ('0000',)

It seems to be the " = 0 " that sql server is complaining about.  Is this a known issue? 

Well SQL Server doesn’t have a “False” construct, so the best we can do for “false” is 0 in SQL server, which isn’t going to work in more elaborate situations such as the above.  

if you want to negate things, just use sqlalchemy.not_() or ~:

from sqlalchemy.sql import column

from sqlalchemy import String

from sqlalchemy.dialects import mssql

c = column('x', String)

print (~c.startswith("foo")).compile(dialect=mssql.dialect())

classic$ python test.py

x NOT LIKE :x_1 + '%%'

I've created a silly workaround, by baking the negation in: 

    @hybrid_property

    def is_not_legacy(self):

        return self.Rtid[:4] != '0000'

    @is_not_legacy.expression

    def is_not_legacy(cls):

        return cls.Rtid.notlike('0000%')

  

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Daniel Weitzenfeld]

ah ok, I didn’t know that SQL server lacked Falseness.  thanks.

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/81b7A9eDz9U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.