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] \n*WHERE
([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%')
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
