[sqlalchemy] Re: sqlalchemy and twisted
Original MessageĀ From: "allan bailey" <[EMAIL PROTECTED]> Or, can anyone recommend another sqlalchemy wrapper for twisted? There is another wrapper, you can try it: http://developer.berlios.de/projects/nadbapi/ Alessandro --~--~-~--~~~---~--~~ 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: Generative style on SQL-API layer
Michael Bayer wrote: > On May 9, 2007, at 4:08 PM, Rick Morrison wrote: > > >> Hey Mike, >> >> I've really gotten to like the generative style of query building >> that the ORM layer uses, and I find myself developing a number of >> patterns that use that style to >> good advantage. >> >> Today I found myself struggling with a query in the low-level SQL- >> API layer that the generative approach would make really easy -- is >> there a way to get the same kind of generative effect in the lower >> layers? >> >> Right now I'm kind of hacking something that uses copy.copy() on >> the select(), and that surprisingly seems to work, but makes me >> think there must be a better way. >> >> >> > > yeah i really should have just imitated Hibernate more closely when i > started this thing. > Well, I can tell you that I am so glad SA does not have HQL (or does it ?). > theres no technical reason generativeness couldnt be applied to > ClauseElements except for potential API messiness. we do have > methods on select() that modify it, such as append_whereclause(), > order_by(), and such...if they just returned "self", then you could > act more "generatively" with it, i.e. select(...).append_whereclause > ().order_by()..etc. > > then again, true "generativeness" would have simpler method names > like just "where()", "from()", etc. and I suppose those arent a big > deal to add. Generative select clauses would be awesome. append_whereclause always did sound slightly verbose to me. Huy --~--~-~--~~~---~--~~ 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: Viewing SQL with Data Mapper
On May 10, 2007, at 5:21 PM, m h wrote: > > I'm playing around with doing queries with the data mapper and am > wondering how to view the SQL generated for them. (Since there is no > intermediate query or select instance, I'm at a loss). > > thanks, > > matt typically using echoing/logging. if you want the actual Select objects generated, use query.compile(). --~--~-~--~~~---~--~~ 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] sqlalchemy and twisted
The only library I can find for using sqlalchemy in a twisted application is sAsync. However, the site seems to be down since tuesday. Does anyone know where I can find the sAsync-0.4.tar.gz (last known release)? Or, can anyone recommend another sqlalchemy wrapper for twisted? thanks, -a --~--~-~--~~~---~--~~ 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] Viewing SQL with Data Mapper
I'm playing around with doing queries with the data mapper and am wondering how to view the SQL generated for them. (Since there is no intermediate query or select instance, I'm at a loss). thanks, matt --~--~-~--~~~---~--~~ 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: Maybe possibly a mapper bug
On May 10, 2007, at 12:05 PM, Chris Perkins wrote: > if 0: > # This works fine: > pjoin = (T1_select.c.id==T2_select.c.parent_id) & > (T1_select.c.ver==T2_select.c.parent_ver) > fks = [T1_select.c.id, T1_select.c.ver] > > else: > # But this does not: > pjoin = (T1.c.id==T2.c.parent_id) & (T1.c.ver==T2.c.parent_ver) > fks = [T1.c.id, T1.c.ver] > > m1.add_property('my_twos', relation(Two, primaryjoin=pjoin, > foreign_keys=fks)) > the second version is not going to work because you are expressing the joins and foreign keys in terms of the underlying tables, not the relations to which the mappers are bound (i.e. T1_select, T2_select). so the mapper cant do anything with that (more specifically, it decides not to try and interpolate the columns into the corresponding columns on the selectable...since that doesnt really work for relationships between selectables that both include the same underlying tables). it works when you have the foreign keys directly on the tables because the select()s you create propigate the constraints from their underlying selected tables into the appropriate constraints for that select object (and also know how to join each other). i.e.: >>> [(f.column, f.parent) for f in T2_select.foreign_keys] [('T1.id', 'T2_select.parent_id'), ('T1.ver', 'T2_select.parent_ver')] >>> T1_select.join(T2_select).onclause "T1_select".id = "T2_select".parent_id AND "T1_select".ver = "T2_select".parent_ver so that produces the same effective arguments you have in the first version. --~--~-~--~~~---~--~~ 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: Viewing SQL of literals....
On May 10, 2007, at 1:07 PM, m h wrote: > > On 5/9/07, Michael Bayer <[EMAIL PROTECTED]> wrote: >> >> >> currently: >> >> statement.compile().construct_params() >> >> > > Thanks for the response. > > construct_params takes a params argument. Where do I find that? (the > statement.compile() instance has a parameters attribute but it is > None) > > I'm assumming the "currently" means that this API is subject to > change? currently meaning, i dont think thats a particularly convenient way to get at the params...send it a blank dict as an argument. --~--~-~--~~~---~--~~ 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: Viewing SQL of literals....
On 5/9/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > > > currently: > > statement.compile().construct_params() > > Thanks for the response. construct_params takes a params argument. Where do I find that? (the statement.compile() instance has a parameters attribute but it is None) I'm assumming the "currently" means that this API is subject to change? --~--~-~--~~~---~--~~ 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] Maybe possibly a mapper bug
This behavior stumped me for quite a while. At the bottom of the code, there are two ways of adding a property to a mapper. One of them works, and the other seems to me like it should probably work, but it doesn't. Is this a bug? from sqlalchemy import * meta = MetaData() T1 = Table('T1', meta, Column('id', Integer, primary_key=True), Column('ver', Integer, primary_key=True), Column('otherstuff', String(100)), ) T2 = Table('T2', meta, Column('id', Integer, primary_key=True), Column('ver', Integer, primary_key=True), Column('parent_id', Integer, nullable=False), Column('parent_ver', Integer, nullable=False), Column('otherstuff', String(100)), #This foreign key is implied, but not enforced in the database: #ForeignKeyConstraint(['parent_id', 'parent_ver'], ['T1.id', 'T1.ver']), ) # In both tables, for any given ID, only the row with the highest version is relevant. # All others are history, and should be effectively invisible. T1_maxvers = select( [T1.c.id, func.max(T1.c.ver).label('ver')], group_by=[T1.c.id], ) T1_select = select( columns = [T1], from_obj = [T1, T1_maxvers], whereclause = and_(T1_maxvers.c.id==T1.c.id, T1_maxvers.c.ver==T1.c.ver), ).alias('T1_select') T2_maxvers = select( [T2.c.id, func.max(T2.c.ver).label('ver')], group_by=[T2.c.id], ) T2_select = select( columns = [T2], from_obj = [T2, T2_maxvers], whereclause = and_(T2_maxvers.c.id==T2.c.id, T2_maxvers.c.ver==T2.c.ver), ).alias('T2_select') class One(object): pass class Two(object): pass m1 = mapper(One, T1_select) m2 = mapper(Two, T2_select) if 0: # This works fine: pjoin = (T1_select.c.id==T2_select.c.parent_id) & (T1_select.c.ver==T2_select.c.parent_ver) fks = [T1_select.c.id, T1_select.c.ver] else: # But this does not: pjoin = (T1.c.id==T2.c.parent_id) & (T1.c.ver==T2.c.parent_ver) fks = [T1.c.id, T1.c.ver] m1.add_property('my_twos', relation(Two, primaryjoin=pjoin, foreign_keys=fks)) m1.compile() If I uncomment the ForeignKeyConstraint in the table definition and remove the extra arguments to relation(), everything works. I would have though that by passing primaryjoin and foreign_keys to relation, I am providing the same information that having the FK constraint on the table does. Let me know if I should log it. Chris Perkins --~--~-~--~~~---~--~~ 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: auto-load ForeignKey references?
Hi, On 5/10/07, King Simon-NFHD78 <[EMAIL PROTECTED]> wrote: > > > Nope, it doesn't work. At least, I can't get it to work. > > > > If I use backref='author' new attribute 'author' appears but equals > > None even though the author_id is something like 123. > > > > You're not getting caught by this, are you: > > http://www.sqlalchemy.org/trac/wiki/WhyDontForeignKeysLoadData > > Basically, setting author_id to a number won't automatically cause the > author to be loaded. > > If that's not the case in your situation, I'm out of ideas. Do you have > a test case? Actually, it works. More precisely, I was getting None because the corresponding entry in wp_users was missing (I love this ref. integrity in MySQL). If post_author was valid then FK was loaded (if backref were specified). Thanks for helping me out. Max. --~--~-~--~~~---~--~~ 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: auto-load ForeignKey references?
Max Ischenko wrote: > > On May 10, 4:38 pm, "King Simon-NFHD78" <[EMAIL PROTECTED]> > wrote: > > You're halfway there with your 'posts' relation. I think if you pass > > backref='author' in your relation, then WordpressPost > objects will get > > an 'author' property which points back to the WordpressUser. > > Nope, it doesn't work. At least, I can't get it to work. > > If I use backref='author' new attribute 'author' appears but equals > None even though the author_id is something like 123. > You're not getting caught by this, are you: http://www.sqlalchemy.org/trac/wiki/WhyDontForeignKeysLoadData Basically, setting author_id to a number won't automatically cause the author to be loaded. If that's not the case in your situation, I'm out of ideas. Do you have a test case? Simon --~--~-~--~~~---~--~~ 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: auto-load ForeignKey references?
Hello, On May 10, 4:38 pm, "King Simon-NFHD78" <[EMAIL PROTECTED]> wrote: > You're halfway there with your 'posts' relation. I think if you pass > backref='author' in your relation, then WordpressPost objects will get > an 'author' property which points back to the WordpressUser. Nope, it doesn't work. At least, I can't get it to work. If I use backref='author' new attribute 'author' appears but equals None even though the author_id is something like 123. Max. --~--~-~--~~~---~--~~ 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: auto-load ForeignKey references?
Max Ischenko wrote: > > Hi, > > If I have two tables related via foreign key how can I tell SA that > accessing foreign key should fetch related object automatically? By > default it simply gives me the FK as integer which is not what I want. > > Here are my mappers: > > wp_users_tbl = Table('wp_users', meta, autoload=True) > wp_posts_tbl = Table('wp_posts', meta, > Column('post_author', Integer, > ForeignKey(wp_users_tbl.c.ID)), > autoload=True) > > mapper(WordpressPost, wp_posts_tbl) > mapper(WordpressUser, wp_users_tbl, properties={ > 'posts' : relation(WordpressPost), >}) > > >>> post = db.select_one(...) > >>> print post.post_author # prints 123 instead of > WordpressUser instance > > Thanks, > Max. You're halfway there with your 'posts' relation. I think if you pass backref='author' in your relation, then WordpressPost objects will get an 'author' property which points back to the WordpressUser. Relations are described more fully here: http://www.sqlalchemy.org/docs/datamapping.html#datamapping_relations The property pointing to the WordpressUser instance is separate from the property containing the foreign key value. If you want the property to be named 'post_author', you'll need to rename the foreign key relationship to prevent them clashing. You can either rename your ForeignKey column, or override the column name as described here: http://www.sqlalchemy.org/docs/adv_datamapping.html#advdatamapping_prope rties_colname Hope that helps, Simon --~--~-~--~~~---~--~~ 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] auto-load ForeignKey references?
Hi, If I have two tables related via foreign key how can I tell SA that accessing foreign key should fetch related object automatically? By default it simply gives me the FK as integer which is not what I want. Here are my mappers: wp_users_tbl = Table('wp_users', meta, autoload=True) wp_posts_tbl = Table('wp_posts', meta, Column('post_author', Integer, ForeignKey(wp_users_tbl.c.ID)), autoload=True) mapper(WordpressPost, wp_posts_tbl) mapper(WordpressUser, wp_users_tbl, properties={ 'posts' : relation(WordpressPost), }) >>> post = db.select_one(...) >>> print post.post_author # prints 123 instead of WordpressUser instance Thanks, Max. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---