[sqlalchemy] Re: How to define a column as read-only (e.g. computed by column in Firebird)
Mike, Michael Bayer wrote: ... oh sorry, I misread the source code in SA earlier...for an INSERT, we are going to insert None for all columns that are blank but dont have a default. so your two options here are to put another PassiveDefault on the column: Column(u'consumedvalue', Numeric(precision=18,length=2,asdecimal=True), PassiveDefault()), That did the trick. Thanks a lot Werner --~--~-~--~~~---~--~~ 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: mapper/mapperExtension initialization order / mapper.properties
forget the instrument_class(). i do a separate MapExt, and append it to the mapper.extensions manualy. So if this post-mapper() append'ing does not screw things up, all else is ok. i have another option to forget the above auto-approach and add the extension separately, after the mapper(...) is ready - like mapr.extension.append(e). Is there anything wrong or that needs be done over it? --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
On Nov 8, 2007 1:36 PM, Florent Aide [EMAIL PROTECTED] wrote: [...] My patch then works because in fact the object that come back from sql server is a binary string encoded using the aforementioned 'codepage'. I had contact with Christophe de Vienne (see above) who has access to the same databases from the same proprietary editor and confirmed the issue. The patch works when the column is defined in the model as String (because Unicode still returns type str even with my patch). Regards, Florent. --~--~-~--~~~---~--~~ 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: r3727 / AbstractClauseProcessor problem
On Nov 8, 2007, at 2:01 AM, [EMAIL PROTECTED] wrote: i dont really understand why u need the ACP being so different to plain visitor; i mean cant they share some skeleton part of traversing, while putting all the choices (visit* vs convert; onentry/onexit; stop/dont) in their own parts. After all, visitor pattern is twofold, a) Guide + b) Visitor; the Guide doing traversing, the Visitor noting things; choice where to go might be in visitor and/or in guide. some times (one extreme) the visitor is just one dumb functor; other cases (other extreme end) the visitor is very sofisticated and even does guiding/traversing. Here it looks more like second case, u have most of both sides put in the Visitor, and only small part (specific visit_* / copy_internals) left to the actual nodes. And to me, the skeleton is still same between ACP and ClauseVisitor. you cant use plain visitor beacuse you are copying the whole structure in place at the same time, and a method is deciding arbitrarily to not copy certain elements, and instead returns an element that was set from the outside; that element cannot be mutated since its not from the original structure, therefore it cannot be traversed. as it turns out, this visitor still has lots of problems which will continue to prevent more sophisticated copy-and-replace operations...some of the ways that a Select() just works from the ground up just get in the way here. if you are curious why there was a rewrite, heres the test which needed to pass: metadata = MetaData() a = Table('a', metadata, Column('id', Integer, primary_key=True)) b = Table('b', metadata, Column('id', Integer, primary_key=True), Column('aid', Integer, ForeignKey('a.id')), ) c = Table('c', metadata, Column('id', Integer, primary_key=True), Column('bid', Integer, ForeignKey('b.id')), ) d = Table('d', metadata, Column('id', Integer, primary_key=True), Column('aid', Integer, ForeignKey('a.id')), ) j1 = a.outerjoin(b) j2 = select([j1], use_labels=True) j3 = c.join(j2, j2.c.b_id==c.c.bid) j4 = j3.outerjoin(d) self.assert_compile(j4, c JOIN (SELECT a.id AS a_id, b.id AS b_id, b.aid AS b_aid FROM a LEFT OUTER JOIN b ON a.id = b.aid) ON b_id = c.bid LEFT OUTER JOIN d ON a_id = d.aid) j5 = j3.alias('foo') j6 = sql_util.ClauseAdapter(j5).copy_and_process([j4])[0] # this statement takes c join(a join b), wraps it inside an aliased select * from c join(a join b) AS foo. # the outermost right side left outer join d stays the same, except d joins against foo.a_id instead # of plain a_id self.assert_compile(j6, (SELECT c.id AS c_id, c.bid AS c_bid, a_id AS a_id, b_id AS b_id, b_aid AS b_aid FROM c JOIN (SELECT a.id AS a_id, b.id AS b_id, b.aid AS b_aid FROM a LEFT OUTER JOIN b ON a.id = b.aid) ON b_id = c.bid) AS foo LEFT OUTER JOIN d ON foo.a_id = d.aid) --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
Hi Paul and others, I attached a new patch to the ticket (#839). It corrects the comportements I have and I now receive unicode objects in all cases: either when I declared String or Unicode as the column type and whatever the type of my columns in MSSQL (varchar based or nvarchar based) I think this is more correct than the previous patch. BTW: I use ms sql2000, pyodbc 2.0.38. I have other issues with 2.0.39 and SA that I'll post about later on :) --~--~-~--~~~---~--~~ 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] create_instance() documentation typo?
At http://www.sqlalchemy.org/docs/04/sqlalchemy_orm.html#docstrings_sqlalchemy.orm_MapperExtension : def create_instance(self, mapper, selectcontext, row, class_) Receive a row when a new object instance is about to be created from that row. The method can choose to create the instance itself, or it can return None to indicate normal object creation should take place. but in code no 'None' value accepted (mapper.py line 1414): # plugin point if 'create_instance' in extension.methods: instance = extension.create_instance(self, context, row, self.class_) if instance is EXT_CONTINUE: instance = attribute_manager.new_instance(self.class_) else: instance = attribute_manager.new_instance(self.class_) instance._entity_name = self.entity_name if extension.create_instance() produces None, then last string should raise AttributeError exception --~--~-~--~~~---~--~~ 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: add_column behavior inconsistent, 0.4.0
gladly http://www.sqlalchemy.org/trac/ticket/858 On Nov 8, 10:19 am, Michael Bayer [EMAIL PROTECTED] wrote: Hi Chris - can you please assemble a fully reproducing test case and create a new ticket in trac ? I can vaguely think of why the add_column() youre doing there might not work correctly and its probably not that hard of a fix, but we're a little overloaded with issues/enhancements this week and having a short test case with which to assemble a unit test would be helpful. thanks, - mike On Nov 7, 2007, at 11:31 PM, Chris M wrote: I haven't tested with the trunk yet, but at least in 0.4.0 there are some inconsistencies with how Query.add_column works. Assuming I have instrumented class Class: Class.query.add_column(Class.some_data) # The added column is completely ignored Class.query.add_column(Class.c.some_data) # ... but this works? The odd part is that in the first example, there is no error message or anything, just a complete ignore. I was surprised by this behavior - I'm not required to use the .c. prefix on most things in SQLAlchemy, and where I can't I at least get some sort of error message. It took me a few tries to actually figure out what was going on and this isn't mentioned anywhere in the documentation, so I figured I'd bring it up. I figure others will be confused as well since select([Class.some_data]) works fine. --~--~-~--~~~---~--~~ 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: r3727 / AbstractClauseProcessor problem
On Nov 8, 2007, at 11:32 AM, svilen wrote: mmmh. u can think of splitting the Visitor into 3: Guide (who traverses _everything_ given), Visitor (who does things), and intermediate Decisor, who decides where to go / what to do. But this can get complicated (slow) although it would be quite clear who does what. Also, do have both onEntry and onExit for each node; i am sure some operations will require both; first to gather info, second to make a decision about what to do with it while still at that level. i've done quite a lot of tree/expression traversers, and while readonly walking doesnot care much if on entry or on exit (except if u want depth or breadth first), replacements-in-place and copy+replace sometimes needed both entry and exit hooks, + they where conditional like in leafs. i think you should come up with your own ACP and lets take a look at it.while i can get various ACP ideas to work further and further, im still able to come up with plenty of cases where none of them work and its because the structure of a clauseelement really isnt a tree. the same node can be represented many times largely because columns reference their parent table. --~--~-~--~~~---~--~~ 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: r3727 / AbstractClauseProcessor problem
Michael Bayer wrote: On Nov 8, 2007, at 11:32 AM, svilen wrote: mmmh. u can think of splitting the Visitor into 3: Guide (who traverses _everything_ given), Visitor (who does things), and intermediate Decisor, who decides where to go / what to do. But this can get complicated (slow) although it would be quite clear who does what. Also, do have both onEntry and onExit for each node; i am sure some operations will require both; first to gather info, second to make a decision about what to do with it while still at that level. i've done quite a lot of tree/expression traversers, and while readonly walking doesnot care much if on entry or on exit (except if u want depth or breadth first), replacements-in-place and copy+replace sometimes needed both entry and exit hooks, + they where conditional like in leafs. i think you should come up with your own ACP and lets take a look at it.while i can get various ACP ideas to work further and further, im still able to come up with plenty of cases where none of them work and its because the structure of a clauseelement really isnt a tree. the same node can be represented many times largely because columns reference their parent table. u mean a graph? mmm no. IMO an expression is a tree of nodes, each node points to some element of another space (metadata things+bindparams+...), and many nodes can point to same element, and eventualy many elements can hold same value (but be different elements - e.g. literal(1) and another literal(1) - not sure about whether this is usable). So traversing the (original) tree is one thing; what to do with the elements pointed by the nodes is another decision; e.g.. whether to process multiple-referred elements multiple times or just one, etc. i can try... but dont rely on me too much ;-) --~--~-~--~~~---~--~~ 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: how can I do such a sorted query?
Most database engines support a couple of SQL functions that help in cases like this, read your database docs for either the ISNULL or the COALESCE function. Another technique is to use an SQL CASE statement. For all three methods the idea is to supply a default value to substitute when the value in question is NULL. In your case that substituted value would be the value of the 'f_date' column, so in essence the logic would be use the value of 's_date' if it's NOT NULL, otherwise use the value of 'f_date'. SA has constructs for both the SQL function method and the CASE statemen method; all are documented (well, I think the CASE is) Rick On 11/8/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: a table, say like this: name f_dates_dtae name12007-10-01 null name22007-06-03 2007-10-07 name32007-09-20 null name4... ... the 'f_date' column always contains a value but the 's_date' can sometimes be null. now I want to get a collection -- all items will be in it and if the 's_date' is not null it must be ordered with 'f_date' of those the 's_date' is null. Just like this: name f_dates_date --- name3 2007-09-20 null name1 2007-10-01 null name2 2007-06-03 2007-10-07 any idea? use select querymaybe with 'UNION'? thanks in advance! artman --~--~-~--~~~---~--~~ 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: r3727 / AbstractClauseProcessor problem
heres an entirely valid SA expression: subq = t2.select().alias('subq') s = select([t1.c.col1, subq.c.col1], from_obj=[t1, subq, t1.join(subq, t1.c.col1==subq.c.col2)]) the way the above works is, t1.join(subq) sends a message to the enclosing Select to hide t1 and subq individually in the FROM clause. this is some pretty old logic but its still whats in effect today and it makes it easy to build up Select statements without worrying whats already in the FROM clause. traversing that, we will hit subq at least twice. r3755 makes more changes to accomodate the above, where it only clones subq once. the resulting clone still has some old subq references lying around though...instead of trying to make the traversal crazy so that it finds and exactly replaces every occurence of subq or subq- referencing column with the exact correct replacement, i changed Select so that when it constructs the FROM clause it takes into account the old instance of subq as well as the cloned version...since thats really the only place that multiple copies of subq really matter in the final output. --~--~-~--~~~---~--~~ 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: r3727 / AbstractClauseProcessor problem
i dont really understand why u need the ACP being so different to plain visitor; i mean cant they share some skeleton part of traversing, while putting all the choices (visit* vs convert; onentry/onexit; stop/dont) in their own parts. After all, visitor pattern is twofold, a) Guide + b) Visitor; the Guide doing traversing, the Visitor noting things; choice where to go might be in visitor and/or in guide. some times (one extreme) the visitor is just one dumb functor; other cases (other extreme end) the visitor is very sofisticated and even does guiding/traversing. Here it looks more like second case, u have most of both sides put in the Visitor, and only small part (specific visit_* / copy_internals) left to the actual nodes. And to me, the skeleton is still same between ACP and ClauseVisitor. you cant use plain visitor beacuse you are copying the whole structure in place at the same time, and a method is deciding arbitrarily to not copy certain elements, and instead returns an element that was set from the outside; that element cannot be mutated since its not from the original structure, therefore it cannot be traversed. well this is a behavior that can be controlled - to traverse the originals that are to be replaced, or not; and to traverse the replacement AFTER it has been replaced or not. as it turns out, this visitor still has lots of problems which will continue to prevent more sophisticated copy-and-replace operations...some of the ways that a Select() just works from the ground up just get in the way here. mmmh. u can think of splitting the Visitor into 3: Guide (who traverses _everything_ given), Visitor (who does things), and intermediate Decisor, who decides where to go / what to do. But this can get complicated (slow) although it would be quite clear who does what. Also, do have both onEntry and onExit for each node; i am sure some operations will require both; first to gather info, second to make a decision about what to do with it while still at that level. i've done quite a lot of tree/expression traversers, and while readonly walking doesnot care much if on entry or on exit (except if u want depth or breadth first), replacements-in-place and copy+replace sometimes needed both entry and exit hooks, + they where conditional like in leafs. --~--~-~--~~~---~--~~ 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: create_instance() documentation typo?
its a typo. if you could place a ticket in trac (with milestone 0.4xx) that would be helpful. On Nov 8, 2007, at 12:31 PM, Andrew Stromnov wrote: At http://www.sqlalchemy.org/docs/04/sqlalchemy_orm.html#docstrings_sqlalchemy.orm_MapperExtension : def create_instance(self, mapper, selectcontext, row, class_) Receive a row when a new object instance is about to be created from that row. The method can choose to create the instance itself, or it can return None to indicate normal object creation should take place. but in code no 'None' value accepted (mapper.py line 1414): # plugin point if 'create_instance' in extension.methods: instance = extension.create_instance(self, context, row, self.class_) if instance is EXT_CONTINUE: instance = attribute_manager.new_instance(self.class_) else: instance = attribute_manager.new_instance(self.class_) instance._entity_name = self.entity_name if extension.create_instance() produces None, then last string should raise AttributeError exception --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
Hi, I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. Ok, so in this case you'd like SA to return a python unicode object when a VARCHAR is fetched, by decoding using the database's encoding? While I understand your requirement, this seems to me to be a special case. I think most people would expect a normal string in this case. I wonder if you should define a MyString class in your app and use that. Rick - do you have a feel on this one? If we do decide to implement this, does anyone know how python can find out what database encoding MSSQL is using? Paul --~--~-~--~~~---~--~~ 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: r3727 / AbstractClauseProcessor problem
heres the structure of: select(from_obj=[t1, t2, t1.join(t2)]) select +--- t1 -+ |--- t2 | +--- join of t1/t2 ---+ t2 and t1 both have two parents, and there are two paths to each of t1 and t2 from the head select. so its not a tree in the strict sense. or another one: s1 = t1.select().alias('s1') s2 = t1.select().alias('s2') s3 = s1.union(s2) (two paths to t1: s3-s1-t1, s3-s2-t1) any kind of subquery which references a table at a higher level falls into this category. hmm. it's still a tree, just the nodes contain same things (t1). There are no cyclic paths to _same_ node in the expression. like x+y+23*(x+1) --- x is used/pointed twice but the expression is still a tree. is there any case where some t1 (or even subexpr) is translated once in one way, and then in another branch in another way? e.g. like in the above x+y+... first x is to be replaced with its value, but second with its name (say because its in round brackets - in some now-invented syntax of mine ). i think there is some mixup between what the expression is, as grammar, and what it actualy means, and u're trying to solve/combine both in one thing/visitor. maybe also what it should mean _after the processing. While they should be all separate notions, somehow. eh, i'm just throwing ideas / alternative view points to play with... --~--~-~--~~~---~--~~ 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: create_instance() documentation typo?
Done. http://www.sqlalchemy.org/trac/ticket/859 On Nov 8, 8:55 pm, Michael Bayer [EMAIL PROTECTED] wrote: its a typo. if you could place a ticket in trac (with milestone 0.4xx) that would be helpful. On Nov 8, 2007, at 12:31 PM, Andrew Stromnov wrote: Athttp://www.sqlalchemy.org/docs/04/sqlalchemy_orm.html#docstrings_sqla... : def create_instance(self, mapper, selectcontext, row, class_) Receive a row when a new object instance is about to be created from that row. The method can choose to create the instance itself, or it can return None to indicate normal object creation should take place. but in code no 'None' value accepted (mapper.py line 1414): # plugin point if 'create_instance' in extension.methods: instance = extension.create_instance(self, context, row, self.class_) if instance is EXT_CONTINUE: instance = attribute_manager.new_instance(self.class_) else: instance = attribute_manager.new_instance(self.class_) instance._entity_name = self.entity_name if extension.create_instance() produces None, then last string should raise AttributeError exception --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
This is going to be messy, as the support for unicode varies among the various MSSQL DBAPIS (which is in lart part why multiple DBAPI support is needed by the MSSQL driver). ODBC looks to me to tell the best story: The newer ODBC drivers have an autotranslate feature that somehow retrieves the codepage setting of the server, and will translate from unicode on the client side to whatever the database encoding is. There may be a way to get the code page from that, or perhaps if the ODBC connection can be set to autotranslate, the code page fetch might not even be needed. In addition to Florent's case, I would lay odds that there are a fair number of legacy databases out there that store utf-8 data in varchar, binary and varbinary fields, and it would be great to support them too. If there was a way to set the autotranslate feature to utf8 on the database side, it might be possible to support both utf-8 data on any server and native codepage on those other servers. The question is whether pyodbc can do that or not. Any idea? On 11/8/07, Paul Johnston [EMAIL PROTECTED] wrote: Hi, I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. Ok, so in this case you'd like SA to return a python unicode object when a VARCHAR is fetched, by decoding using the database's encoding? While I understand your requirement, this seems to me to be a special case. I think most people would expect a normal string in this case. I wonder if you should define a MyString class in your app and use that. Rick - do you have a feel on this one? If we do decide to implement this, does anyone know how python can find out what database encoding MSSQL is using? Paul --~--~-~--~~~---~--~~ 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: r3727 / AbstractClauseProcessor problem
On Nov 8, 2007, at 2:37 PM, [EMAIL PROTECTED] wrote: heres the structure of: select(from_obj=[t1, t2, t1.join(t2)]) select +--- t1 -+ |--- t2 | +--- join of t1/t2 ---+ t2 and t1 both have two parents, and there are two paths to each of t1 and t2 from the head select. so its not a tree in the strict sense. or another one: s1 = t1.select().alias('s1') s2 = t1.select().alias('s2') s3 = s1.union(s2) (two paths to t1: s3-s1-t1, s3-s2-t1) any kind of subquery which references a table at a higher level falls into this category. hmm. it's still a tree, just the nodes contain same things (t1). There are no cyclic paths to _same_ node in the expression. yes, there are. select-t1 and select-join of t1/t2 - t1 . like x+y+23*(x+1) --- x is used/pointed twice but the expression is still a tree. only if you represent it with two distinct nodes for the two occurences of x. SA's expressions don't do it that way (usually). is there any case where some t1 (or even subexpr) is translated once in one way, and then in another branch in another way? e.g. like in the above x+y+... first x is to be replaced with its value, but second with its name (say because its in round brackets - in some now-invented syntax of mine ). there arent on my end, and its true that this scenario wouldn't be supported with the kinds of translations im doing. i think there is some mixup between what the expression is, as grammar, and what it actualy means, and u're trying to solve/combine both in one thing/visitor. maybe also what it should mean _after the processing. While they should be all separate notions, somehow. eh, i'm just throwing ideas / alternative view points to play with... yeah, im starting to head towards being able to support two distinct nodes for x but they mean the same thing. the current trunk does a litle bit of that now, since the clone operation doesn't make a completely copied result. i think at the end of the day, the only place it matters that we have x represented by three different objects instead of one is when Select prints out the list of FROM clauses (it also might matter when we render the columns clause of the select). --~--~-~--~~~---~--~~ 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: how can I do such a sorted query?
Thanks for your help,Rick. Followed your advice, I've solved the problem. Following is the solution: select([table_name]).order_by(case([(table_name.c.s_date==None,table_name.c.f_date)],else_=table_name.c.s_date)) Thanks again :) On 11月9日, 上午1时48分, Rick Morrison [EMAIL PROTECTED] wrote: Most database engines support a couple of SQL functions that help in cases like this, read your database docs for either the ISNULL or the COALESCE function. Another technique is to use an SQL CASE statement. For all three methods the idea is to supply a default value to substitute when the value in question is NULL. In your case that substituted value would be the value of the 'f_date' column, so in essence the logic would be use the value of 's_date' if it's NOT NULL, otherwise use the value of 'f_date'. SA has constructs for both the SQL function method and the CASE statemen method; all are documented (well, I think the CASE is) Rick On 11/8/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: a table, say like this: name f_dates_dtae name12007-10-01 null name22007-06-03 2007-10-07 name32007-09-20 null name4... ... the 'f_date' column always contains a value but the 's_date' can sometimes be null. now I want to get a collection -- all items will be in it and if the 's_date' is not null it must be ordered with 'f_date' of those the 's_date' is null. Just like this: name f_dates_date --- name3 2007-09-20 null name1 2007-10-01 null name2 2007-06-03 2007-10-07 any idea? use select querymaybe with 'UNION'? thanks in advance! artman- 隐藏被引用文字 - - 显示引用的文字 - --~--~-~--~~~---~--~~ 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] mysql exceptions
Hello sqlalchemists, I posted this on the pylons group, and was referred here, so this is basically a repeat: Running a pylons app under heavy load by several simultaneous (simulated) users all on a single cpu have yielded the following error (several times, but not often): Exception _mysql_exceptions.ProgrammingError: (2014, Commands out of sync; you can't run this command now) in bound method Cursor.__del__ of MySQLdb.cursors.Cursor object at 0x8c9bcac ignored This error appeared on the console where the paste server was loaded. I had been mixing ORM queries with sql queries, but still get the error when using only ORM. Also, am only doing selects - no inserts, updates, etc. Config: started: paster serve --reload development.ini paste behind apache via mod_proxy ubuntu feisty mysql version: 5.0.38 MySQL_python-1.2.2-py2.5-linux-i686.egg SQLAlchemy-0.4.0-py2.5.egg Mako-0.1.8-py2.5.egg PasteScript-1.3.6-py2.5.egg PasteDeploy-1.3.1-py2.5.egg Paste-1.4.2-py2.5.egg Beaker-0.7.5-py2.5.egg WebHelpers-0.3.2-py2.5.egg Routes-1.7-py2.5.egg Pylons-0.9.6.1-py2.5.egg Any ideas on how to fix, or what the nature of the issue is, or how to better isolate/debug would be much appreciated. Thanks! David --~--~-~--~~~---~--~~ 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] mixed joined+concrete inheritance broken/r3735
i have a A-B-C test case where B inherits A via joined, and C inherits B via concrete; anbd there are links to each other, e.g. A points to B. it used to work before r3735. now query(A) gives: NoSuchColumnError: Could not locate column in row for column 'A_tbl.db_id' if A-B link is not assigned, it works. --~--~-~--~~~---~--~~ 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: insert to the database
Table definition: tables['indicators'] = Table('indicators', metadata, autoload=True) tables['indicatorgroups'] = Table('indicatorgroups', metadata, autoload=True) ##indicatorgroups table has two columns: idindicatorgroup, name ##indicators table has several columns: idindicator, idindicatorgroup(FK), ... Mapper: mappers['indicatorgroups'] = mapper(IndicatorGroups, tables['indicatorgroups']) mappers['indicators'] = mapper(Indicators, tables['indicators'], properties = { 'idindicatorgroup' : relation(IndicatorGroups), },allow_column_override=True) Once i do this I have create a method in order to insert data in my mySQL db: class Insert: implements(IInsert) def insert(self, file): import pdb db = getUtility(IDatabase, name='db.query') session = db.session sniffer = csv.Sniffer() dialect = sniffer.sniff(file.read()) file.seek(0) csvReader = csv.reader(file, dialect=dialect) csvReader.next() for i in csvReader: group, subgroup, indicator, code = i indg=IndicatorGroups() indg.name=group session.save(indg) ind=Indicators() ind.code=code ind.name=indicator ind.idindicatorgroup=indg session.save(ind) session.flush() but when execute session.flush() I have this error: Traceback (innermost last): Module ZPublisher.Publish, line 115, in publish Module ZPublisher.mapply, line 88, in mapply Module ZPublisher.Publish, line 41, in call_object Module Products.odr.lugabe_db.browser.csv_insert, line 26, in __call__ Module Products.odr.lugabe_db.insert, line 52, in insert Module sqlalchemy.orm.session, line 681, in flush Module sqlalchemy.orm.unitofwork, line 216, in flush Module sqlalchemy.orm.unitofwork, line 432, in execute Module sqlalchemy.orm.unitofwork, line 1051, in execute Module sqlalchemy.orm.unitofwork, line 1068, in execute_save_steps Module sqlalchemy.orm.unitofwork, line 1081, in execute_dependencies Module sqlalchemy.orm.unitofwork, line 1062, in execute_dependency Module sqlalchemy.orm.unitofwork, line 1017, in execute Module sqlalchemy.orm.dependency, line 282, in process_dependencies Module sqlalchemy.orm.dependency, line 317, in _synchronize Module sqlalchemy.orm.sync, line 91, in execute Module sqlalchemy.orm.sync, line 143, in execute Module sqlalchemy.orm.mapper, line 936, in set_attr_by_column Module sqlalchemy.orm.util, line 101, in __getitem__ KeyError: Column(u'idindicatorgroup', MSInteger(length=11), ForeignKey(u' indicatorgroups.idindicatorgroup'), nullable=False, default=PassiveDefault(u'0')) And i don't know what I am doing wrong thanks a lot 2007/11/7, Michael Bayer [EMAIL PROTECTED]: On Nov 7, 2007, at 5:20 AM, lur ibargutxi wrote: Does anyone knows what am I doing wrong?? nopewould need to see an entire reproducing test case for that one. -- Lur Ibargutxi [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] primary and secondary join
Hi!! I have a IndicatorGroups table(with two columns: id, name). And i have another table to to do the relation between two groups(with three columns: id, idgroupcontainer, idgroupcontained). And when I am going to do the mapper: mappers['groupgroups'] = mapper(GroupGroups, tables['groupgroups'], properties = { 'idindicatorgroupcontainer' : relation(IndicatorGroups, primaryjoin=sql.and_(tables['indicatorgroups'].c.idindicatorgroup==tables['groupgroups'].c.idindicatorgroupcontainer)),'idindicatorgroupcontained' : relation(IndicatorGroups, primaryjoin=sql.and _(tables['indicatorgroups'].c.idindicatorgroup==tables['groupgroups'].c.idindicatorgroupcontained)), },allow_column_override=True) This is well done or I have to put like secondaryjoin one of them?? Thanks a lot -- Lur Ibargutxi [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] mapper/mapperExtension initialization order / mapper.properties
g'day. in the Aggregator i have mapper extension, that needs info from the mapper like local-table and mapping/naming of column properties. It used to hook on instrument_class() for that, but now the mapper.properties in its new get()/iterate() form is not available yet when mapper-extensions are setup (e.g. at extension.instrument_class). Mapper._init__(): ... self._compile_class() self._compile_extensions() self._compile_inheritance() self._compile_tables() self._compile_properties() self._compile_selectable() is there any specific reason for the compile_extensions to be that early in this order? any other hook that i can use ? i have another option to forget the above auto-approach and add the extension separately, after the mapper(...) is ready - like mapr.extension.append(e). Is there anything wrong or that needs be done over it? --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
On Nov 5, 2007 6:50 PM, Paul Johnston [EMAIL PROTECTED] wrote: [...] Are your strings VARCHAR or NVARCHAR? If they're NVARCHAR, all this will just work as-is with SA and MSSQL - are you having any specific problems? If they're VARCHAR, then we need to think some more. I'm still not sure what the semantics should be when trying to save a unicode object in a non-unicode storage area. I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. I tried to force the user defined type to be based on NVARCHAR and it works. BUT... I cannot do this in production since I don't own the dbs and they are used by other applications, I just have a read-only access on them to do some interrogations. My patch then works because in fact the object that come back from sql server is a binary string encoded using the aforementioned 'codepage'. I had contact with Christophe de Vienne (see above) who has access to the same databases from the same proprietary editor and confirmed the issue. Christophe also noted that in Linux since the supports_unicode resolves to False, we don't encounter problems. But since my code will run on Windows I am still searching for a way to sort that mess :) Thanks for the help and time Paul :-) Regards, Florent. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---