[sqlalchemy] Re: Boolean column in MS SQL
Hi,
On 31 Юли, 02:33, Paul Johnston [EMAIL PROTECTED] wrote:
Yes, please do. I think you'll find some tweak to MssqlCompiler can
achieve this.
BTW, AND/OR are not broken generally; I think it's just BIT columns they
have a problem with.
Paul
I dont know how to restrict my changes only to where clause as mssql
has bit weird behavior -
in select like this it is ok:
select * from manager where not ( tobeornot = 1)
if the boolean expression is in the select part - sorry - error:
select not ( tobeornot = 1) from manager BUMMM
anyway the patch seems to work (at least for me).
below is the patch if it can be useful for someone (also not sure that
this is the proper way)
regards,
stefan
Index: databases/mssql.py
===
--- databases/mssql.py (revision 2997)
+++ databases/mssql.py (working copy)
@@ -824,6 +824,33 @@
else:
super(MSSQLCompiler, self).visit_alias(alias)
+#TODO restrict changes in visit_unary, visit_clauselist to the
whereclause only!!!
+__BOOL_HACK = ' =1'
+
+def _isSelect( self):
+return not self.isinsert and not self.isupdate #and
isinstance( self.dialect, MSSQLDialect_pymssql)
+
+def visit_unary( self, unary):
+if (self._isSelect() and 'NOT' == getattr(unary,
'operator','')
+and isinstance( unary.element, schema.Column)
+and isinstance( unary.element.type,
sqltypes.Boolean) ):
+unary.element = sql._TextClause( str(unary.element) +
self.__BOOL_HACK)
+self.traverse( unary.element)
+super(MSSQLCompiler, self).visit_unary(unary)
+
+def visit_clauselist(self, list):
+if self._isSelect():
+column = None
+for each in list.clauses:
+if isinstance( each, schema.Column) and
isinstance( each.type, sqltypes.Boolean):
+column = each
+break
+if column and list.operator in ('AND', 'OR'):
+newClause = sql._TextClause( str(column) +
self.__BOOL_HACK)
+self.traverse( newClause)
+self.strings[ column] = str( newClause)
+super(MSSQLCompiler, self).visit_clauselist(list)
+
def visit_column(self, column):
# translate for schema-qualified table aliases
super(MSSQLCompiler, self).visit_column(column)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Boolean column in MS SQL
On 2 Авг, 17:00, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 2, 2007, at 5:07 AM, che wrote: you might want to try a. working with the 0.4 trunk, which has a simpler compilation scheme and b. just overriding visit_select() to pass a flag down into sub-visit_XXX calls. also what specifically do you want to convert select not (tobeornot=1) from manager to be ? seems nothing here can be done, except... to issue some proper error message. currently in such case error is issued by the mssql itself which is clear enough maybe. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Boolean column in MS SQL
On Aug 2, 2007, at 5:07 AM, che wrote: Hi, On 31 Юли, 02:33, Paul Johnston [EMAIL PROTECTED] wrote: Yes, please do. I think you'll find some tweak to MssqlCompiler can achieve this. BTW, AND/OR are not broken generally; I think it's just BIT columns they have a problem with. Paul I dont know how to restrict my changes only to where clause as mssql has bit weird behavior - in select like this it is ok: select * from manager where not ( tobeornot = 1) if the boolean expression is in the select part - sorry - error: select not ( tobeornot = 1) from manager BUMMM anyway the patch seems to work (at least for me). below is the patch if it can be useful for someone (also not sure that this is the proper way) you might want to try a. working with the 0.4 trunk, which has a simpler compilation scheme and b. just overriding visit_select() to pass a flag down into sub-visit_XXX calls. also what specifically do you want to convert select not (tobeornot=1) from manager to be ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Boolean column in MS SQL
On Aug 2, 2007, at 10:26 AM, che wrote: On 2 Авг, 17:00, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 2, 2007, at 5:07 AM, che wrote: you might want to try a. working with the 0.4 trunk, which has a simpler compilation scheme and b. just overriding visit_select() to pass a flag down into sub-visit_XXX calls. also what specifically do you want to convert select not (tobeornot=1) from manager to be ? seems nothing here can be done, except... to issue some proper error message. currently in such case error is issued by the mssql itself which is clear enough maybe. thats generally the position we take...dont second guess the database's own compiler. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: right outer join (newbie)
There are good reasons to support full outer join. Some are suggested in the comments to that one article. :) I don't miss right joins though, I find left join more natural. On 8/1/07, Michael Bayer [EMAIL PROTECTED] wrote: we currently dont have a right outer join. but this is not by accident...theres really no use case for a right outer join, which are more easily written as left outer joins. some good articles on whats wrong with RIGHT outer join as well as FULL outer join: http://weblogs.sqlteam.com/jeffs/archive/2006/03/14/9289.aspx http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer- Joins.aspx?x=1 On Aug 1, 2007, at 5:56 PM, mc wrote: Hi, I read in the docs about joins and saw an example for left outer join, though I didn't understand where the left was specified. How is a right outer join specified? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: all() or list() ?
On 8/1/07, Gaetan de Menten [EMAIL PROTECTED] wrote: On 8/1/07, Alexandre CONRAD [EMAIL PROTECTED] wrote: I'm realizing that I've been using .list() to query objects. But all the examples in the docs talk about .all(). What's the difference ? Should I switch to .all() rather than .list() ? Will list be deprecated in 0.4 ? Exactly. list() is the old way, all() is the 0.4 way. .list() is the old new way, or short-lived new way. .all() is the real new way. -- Mike Orr [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] confusing populate_existing
Hi!
I'm using queries with populate_existing modifier, and found this
problem:
I issue a query with populate_existing and some eagerload chain,
ending with class C.
C is refreshed, and have some foreign key attribute changed correctly.
But relation based on that key, if already loaded, will not lazy load
again, and still points to WRONG (old) instance.
This is test:
def test_populate_existing_with_eager(self):
s = create_session()
addr = s.query(Address).get(1)
order = s.query(Order).get(1)
old_user = s.query(User).get(7)
assert addr.user == old_user #lazy loading user attribute
#Now change the address
addresses.update(addresses.c.id == 1).execute(user_id=9)
new_user = s.query(User).get(9)
#Refreshing Odrder and Address instances
order =
s.query(Order).populate_existing().options(eagerload('address')).filter_by(id=1).one()
#Important! addr.user was already loaded!
assert addr.user_id == 9# refreshed ok
assert addr.user == new_user , 'Fail! still == old_user, need
to lazy load it again...'
Same problem with many_to_many relations, but it's more complex to
solve.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] 'Column' object has no attribute 'tables'
Hi all,
I'm just working on upgrading my db code to use the new 0.4 api, and
I've hit a problem
Based on the following table def (there's lots more in there, but I've
commented everything out but this table and am still getting the same
error),
sysMessageTable = Table(
'sysmessage',
Column('id', Integer, primary_key=True),
Column('timestamp', DateTime, nullable=False,
default=datetime.datetime.now()),
Column('summary', String(100), nullable=False),
Column('message', TEXT()),
Column('viewed', Boolean, nullable=False, default=False),
when I import my db.py file (literally at the time of import), I get
this error,
Traceback (most recent call last):
File scripts/testdata, line 7, in module
from mymod.db import *
File ./mymod/db.py, line 129, in module
Column('viewed', Boolean, nullable=False, default=False),
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.0dev_r3150-py2.5.egg/sqlalchemy/schema.py,
line 146, in __call__
table = metadata.tables[key]
AttributeError: 'Column' object has no attribute 'tables'
Any suggestions ?
Cheers
Dave
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
