[sqlalchemy] Convert JOIN to WHERE for old MySQLs
Hi, I'm connecting to an ancient version of MySQL (3.23.58) that (as far as I can tell) doesn't support basic JOIN table ON condition syntax Ie. this gives a syntax error: SELECT * FROM productversions pv JOIN producttypes pt ON pv.producttype = pt.producttype Whereas this is fine: SELECT * FROM productversions pv JOIN producttypes pt WHERE pv.producttype = pt.producttype Can anyone think of anything I can do (perhaps by subclassing or monkeypatching MySQLDialect) to automatically convert queries like the former into the latter? SQLAlchemy seems to be infitely flexible in dealing with syntax quirks of various databases, so I've got my fingers crossed... Thanks a lot, 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: LIMIT in queries
Felix Schwarz wrote: Hi, after reading the docs [1] I thought that something like session.query(User).filter(User.c.id 3)[0] should work even if the filter clause does not return any rows. But - compliant with Python's behavior - SQLAlchemy raises an IndexError. (...) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 577, in __getitem__ return list(self[item:item+1])[0] IndexError: list index out of range I expected that [0] applied to query without results would return None. Did I miss another possibility for LIMIT'ing queries (using sqlalchemy.orm)? fs [1] http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping _querying I think being matching Python's behaviour is definitely the right way to go. If you want to get None specifically for index [0], you can use the 'first' method on query. If you are happy get an empty list, you could use a slice and then call .all() Eg: session.query(User).filter(User.c.id 3)[:1].all() - [] 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] Re: LIMIT in queries
Or you could use .limit(1). I've always found that more clear than the Python indexing notation because I think of it slicing a list, not applying a limit to a query. --~--~-~--~~~---~--~~ 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: Undeferring attributes off joined entities
On further consideration of the interface, what about adding two new keywords to options() - mapper, and id? That way someone with a large group of options to set won't have to type the entity name and/or id on every single option. session.query(Class).options(options for main entity).options(options for other entity, mapper=SomeClass, id=class1) On Dec 11, 8:26 pm, Chris M [EMAIL PROTECTED] wrote: Don't care as long as the functionality is available somehow :) Unless others have objections I say go with whatevers currently implemented. On Dec 11, 7:52 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Dec 11, 2007, at 6:21 PM, Chris M wrote: Okay, does this mean that options() doesn't go off the last entity or is this interface just in addition to that behavior? this currently is in lieu of the options goes off the last entity idea. i tried it that way but this way felt less surprising to me. we can do a poll if you want. --~--~-~--~~~---~--~~ 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: Column defaults in MapperExtension.after_insert
Michael Bayer wrote: On Dec 11, 2007, at 10:07 AM, King Simon-NFHD78 wrote: I assume the problem is that my date_created column isn't immediately available at the 'after_insert' stage, because it is generated in the SQL INSERT statement, but hasn't been read back from the database yet. Is there a more suitable hook-point than after_insert, where I can safely read values like this? this is a thorny issue, while the immediate issue youre having is something that can possibly be smoothed over (probably even by sticking on the _instance_key yourself, not sure if i should recommend that though), its not going to work if say you wanted to look at the foriegn key attributes in an attached collection since they havent been set up yet. also the API youre using with get_history() has changed in trunk, itll give you a tuple now of (added, unchanged, deleted). there is the notion of SessionExtension which has an after_commit() hook, not sure if that is a viable option here. I cant go too nuts with all these extension hooks since they all add latency to everything (sorta wishing there were a lot less hooks as it is). Thanks - I've ignored your warning and stuck on the _instance_key, in the full knowledge that when it breaks it'll be my own silly fault ;-) I'll investigate the SessionExtension as a longer-term fix. If after_commit runs when all attributes are completely up-to-date, and I can still access the previous and new state via get_history, it sounds like the right thing to use. I fully understand your worries about hooks slowing SQLAlchemy down, but I also think that they are one of the features that makes the ORM so flexible, and it would be a great shame to lose them. I wonder if there could be a way of caching all the extension methods so that applying them would be as fast as possible. checks code OK, I should have guessed. You've already done that - I see the ExtensionCarrier checks which methods have been overridden when each extension is added. Thanks for your help, 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: Confused about relations
u.Xs is going to have exactly those X's which have u's id as their user_id attribute. the X which you appended to u.Ys[0] is from a different relationship. so yeah you have to set the user_id attribute on the X, which is entirely legal. However the legit way to do it is to just add the X to u's Xs collection and have the ORM take care of the user_id attribute for you. just like: x = X() u.Ys[0].Xs.append(x) u.Xs.append(x) Ah, I see, thanks. I hadn't thought of doing it that way since it sort of looks like it'd be inserting it twice. But, makes sense now that I see it. no you dont need to do all that. even if you are just setting user_id as you are now, just expire the attribute: session.expire(u, ['Xs']), and it will reload when you touch it again. but if you think in terms of collections instead of foreign keys like above, then you dont even need that, it would just all work out. Thanks again, I should have rtfm'd better for this one. scott --~--~-~--~~~---~--~~ 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: Convert JOIN to WHERE for old MySQLs
On Dec 12, 2007, at 9:04 AM, King Simon-NFHD78 wrote: Hi, I'm connecting to an ancient version of MySQL (3.23.58) that (as far as I can tell) doesn't support basic JOIN table ON condition syntax Ie. this gives a syntax error: SELECT * FROM productversions pv JOIN producttypes pt ON pv.producttype = pt.producttype Whereas this is fine: SELECT * FROM productversions pv JOIN producttypes pt WHERE pv.producttype = pt.producttype Can anyone think of anything I can do (perhaps by subclassing or monkeypatching MySQLDialect) to automatically convert queries like the former into the latter? SQLAlchemy seems to be infitely flexible in dealing with syntax quirks of various databases, so I've got my fingers crossed... this is possible, we do it in the oracle module when the ansi=False flag is set. if you look at the visit_join code in oracle.py, you could probably lift that up and stick it right in mysql.py, have it activated by a dialect flag, and it would work. however, if the database doesnt support JOIN..ON, that indicates that its impossible to do an OUTER JOIN.oracle used a special operator (+) to indicate the outerness of a join..does this old mysql have anything for that ? otherwise it doesnt seem much worth it to support JOIN at all when you could just select from two tables. --~--~-~--~~~---~--~~ 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: Convert JOIN to WHERE for old MySQLs
Michael Bayer wrote On Dec 12, 2007, at 9:04 AM, King Simon-NFHD78 wrote: Hi, I'm connecting to an ancient version of MySQL (3.23.58) that (as far as I can tell) doesn't support basic JOIN table ON condition syntax Ie. this gives a syntax error: SELECT * FROM productversions pv JOIN producttypes pt ON pv.producttype = pt.producttype Whereas this is fine: SELECT * FROM productversions pv JOIN producttypes pt WHERE pv.producttype = pt.producttype Can anyone think of anything I can do (perhaps by subclassing or monkeypatching MySQLDialect) to automatically convert queries like the former into the latter? SQLAlchemy seems to be infitely flexible in dealing with syntax quirks of various databases, so I've got my fingers crossed... this is possible, we do it in the oracle module when the ansi=False flag is set. if you look at the visit_join code in oracle.py, you could probably lift that up and stick it right in mysql.py, have it activated by a dialect flag, and it would work. however, if the database doesnt support JOIN..ON, that indicates that its impossible to do an OUTER JOIN.oracle used a special operator (+) to indicate the outerness of a join..does this old mysql have anything for that ? otherwise it doesnt seem much worth it to support JOIN at all when you could just select from two tables. I knew SA could do it! Thanks! This version of MySQL does support LEFT JOIN and RIGHT JOIN, so things like eager loads work correctly (as far as I can tell). Does that mean that in visit_join, I should defer to the normal implementation if join.isouter is True? This came up because I was using query.join to filter a query based on a child's attributes, and suddenly the query stopped working on MySQL. I was about to try and find all occurrences of query.join and change them to explicit filter conditions, but if I can make the visit_join method work, I would much prefer to do it that way. I'll let you know if it works - it may be worth adding as an optional behaviour to MySQLDialect (although if I'm the only one that's come across the problem, maybe no-one else is using a version of MySQL that's this old) Thanks, 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: Convert JOIN to WHERE for old MySQLs
King Simon-NFHD78 wrote: Hi, I'm connecting to an ancient version of MySQL (3.23.58) that (as far as I can tell) doesn't support basic JOIN table ON condition syntax Ie. this gives a syntax error: SELECT * FROM productversions pv JOIN producttypes pt ON pv.producttype = pt.producttype Whereas this is fine: SELECT * FROM productversions pv JOIN producttypes pt WHERE pv.producttype = pt.producttype Can anyone think of anything I can do (perhaps by subclassing or monkeypatching MySQLDialect) to automatically convert queries like the former into the latter? SQLAlchemy seems to be infitely flexible in dealing with syntax quirks of various databases, so I've got my fingers crossed... This is in r3916. It's a simple change that can be easily monkey patched into any version of SA- these just need to be emitted as INNER JOIN .. ON instead. -j --~--~-~--~~~---~--~~ 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: alias problem with polymorphic joined table inheritance
Alexandre Conrad wrote: Here is the code I'm working on (sqlite and mysql errors at the bottom): http://rafb.net/p/HlZw3P26.html Here are the relations for clarity: client(company) -- one-to-many - site(company) client(company) -- one-to-many - channel(object) query == site - client - channels Got it, I had to add 'aliased=True' to make the query happend. print session.query(Channel).join([client, sites], aliased=True).filter(Site.c.id==2).all() Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: Undeferring attributes off joined entities
On Dec 12, 2007, at 10:07 AM, Chris M wrote: On further consideration of the interface, what about adding two new keywords to options() - mapper, and id? That way someone with a large group of options to set won't have to type the entity name and/or id on every single option. session.query(Class).options(options for main entity).options(options for other entity, mapper=SomeClass, id=class1) thats OK, except it starts getting into more than one way to do it territory. also i wonder if there would someday be some option that doesnt apply to any particular mapper. theres an implementation reason also that very much favors the mapper- embedded-in-the-option approach, which is that the options on the query get re-used on an entirely different query when lazy and deferred loaders load their results. the original query could bundle the options with the mappers sure but at the moment its a little simpler than that (and i really am trying to not complicate things these days) --~--~-~--~~~---~--~~ 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: Convert JOIN to WHERE for old MySQLs
Jason kirtland wrote: King Simon-NFHD78 wrote: Hi, I'm connecting to an ancient version of MySQL (3.23.58) that (as far as I can tell) doesn't support basic JOIN table ON condition syntax Ie. this gives a syntax error: SELECT * FROM productversions pv JOIN producttypes pt ON pv.producttype = pt.producttype Whereas this is fine: SELECT * FROM productversions pv JOIN producttypes pt WHERE pv.producttype = pt.producttype Can anyone think of anything I can do (perhaps by subclassing or monkeypatching MySQLDialect) to automatically convert queries like the former into the latter? SQLAlchemy seems to be infitely flexible in dealing with syntax quirks of various databases, so I've got my fingers crossed... This is in r3916. It's a simple change that can be easily monkey patched into any version of SA- these just need to be emitted as INNER JOIN .. ON instead. -j Thanks - that looks much easier than the oracle version. I _almost_ understand this one! Monkeypatching now... (I also hadn't spotted that adding 'INNER' was enough to satisfy MySQL...) Cheers, 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: Convert JOIN to WHERE for old MySQLs
King Simon-NFHD78 wrote: Jason kirtland wrote: King Simon-NFHD78 wrote: Hi, I'm connecting to an ancient version of MySQL (3.23.58) that (as far as I can tell) doesn't support basic JOIN table ON condition syntax This is in r3916. It's a simple change that can be easily monkey patched into any version of SA- these just need to be emitted as INNER JOIN .. ON instead. Thanks - that looks much easier than the oracle version. I _almost_ understand this one! Monkeypatching now... (I also hadn't spotted that adding 'INNER' was enough to satisfy MySQL...) For hand-patching older SA versions, the recipe is: find the base implementation of visit_join (in compiler.py or wherever, not in databases/), copy it into the MySQL dialect and change 'JOIN' to 'INNER JOIN'. Cheers, Jason --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Hey Fabio, would you please post a full non-working copy with the new schema and all the PKs that you want set up? There are a few too many variants in this thread to see what's going on now. Your earlier versions didn't include 'station' as a PK, but did include 'start', while this one's the opposite. --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
yeah SQLAlchemy started, like Rick was getting at, with a much closer to the metal idea than that, that if you made a new object and put it in the session, youd know that it wasnt flushed yet. My experience with hibernate is identical, actually, nothing gets generated or anything in our environment over here until the flush happens. But another thing, is that the whole idea of save/update/save-or- update, which we obviously got from hibernate, is something ive been considering ditching, in favor of something more oriented towards a container like add(). since i think even hibernate's original idea of save/update has proven to be naive (for example, this is why they had to implement saveOrUpdate()). we like to keep things explicit as much as possible since thats a central philosophical tenet of Python. To be honest, I think a lot of people aren't thrilled with that aspect of Hibernate. Actually, I don't think I've ever used update() or saveOrUpdate(), since everything else seems just work (i.e. changes made to entities loaded from the DB get automatically saved when the transaction commits). So back to my original thoughts regarding the ability to fetch db-generated primary keys (and other DefaultGenerator things) after a save() but before an explicit/implicit flush... I would think it could be an option on the Table (err, Column) itself and not a mapper option. Something like pending_flush=True (which can default to False to keep the current behavior...though that name probably isn't very good, but flush_when_pending_on_read is too long ;). Does that sound like a reasonable idea? You don't have to commit to any work if you don't want, I wouldn't mind trying my hand at it if necessary. Thanks, -Adam Batkin --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
I'm not on my pc right now so I can send you the non working copy only tomorrow I've tried several schemas changes to try and see if the problem always occurs or if there cases that it works, not necessary because i need all those schemas In the former table schema, as i said, i've included only identifier and station as PKs, but the table in the database is set with PKs on identifier, START and station... and save_or_update works fine... I'll post some code tomorrow... On 12 Dic, 17:54, Rick Morrison [EMAIL PROTECTED] wrote: Hey Fabio, would you please post a full non-working copy with the new schema and all the PKs that you want set up? There are a few too many variants in this thread to see what's going on now. Your earlier versions didn't include 'station' as a PK, but did include 'start', while this one's the opposite. --~--~-~--~~~---~--~~ 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: Lazy ID Fetching/generation
On Dec 12, 2007, at 4:49 PM, Adam Batkin wrote: I would think it could be an option on the Table (err, Column) itself and not a mapper option. Something like pending_flush=True (which can default to False to keep the current behavior...though that name probably isn't very good, but flush_when_pending_on_read is too long ;). Does that sound like a reasonable idea? You don't have to commit to any work if you don't want, I wouldn't mind trying my hand at it if necessary. Tables and Columns dont know anything about flushes, theyre external to the ORM package. such a flag would be meaningless on a 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] ordering_list for many to many relations
Greetings,
I've tried doing the following using ordering_list for many to many
relations but it didn't work:
post_table = Table('posts', metadata,
Column('id', Integer, primary_key=True),
Column('headline', String(255), nullable=False),
Column('body', String)
)
post_keywords = Table('post_keywords', metadata,
Column('post_id', Integer, ForeignKey('posts.id')),
Column('keyword_id', Integer, ForeignKey('keywords.id')),
Column('position', Integer))
keywords_table = Table('keywords', metadata,
Column('id', Integer, primary_key=True),
Column('keyword', String(50), nullable=False, unique=True))
mapper(Keyword, keywords_table)
mapper(BlogPost, post_table, properties={
'keywords':relation(Keyword, secondary=post_keywords,
collection_class=ordering_list('position'),
order_by=[post_keywords.c.position] )
})
...
bp = BlogPost('head', 'body')
bp.keywords = [Keyword('k0'), Keyword('k1')]
session.save(bp)
session.flush()
For what I could see in the docs, this extension only works for one to many
relations.
Is there something like the ordering_list extension that works in many to many
relations?
--
Fernando J. Zunino
Zauber S.A.
http://www.zauber.com.ar/
signature.asc
Description: OpenPGP digital signature
[sqlalchemy] Re: Matching a DateTime-field
On Dec 11, 10:55 am, King Simon-NFHD78 [EMAIL PROTECTED] wrote: It may not matter to you, but I wouldn't have thought this would be a very efficient query, because the database is going to have to call the DATE_FORMAT function twice for every row in your table. I would have thought a more efficient version would be one that asks for all rows between the first of one month and the first of another month (especially if the date column is indexed). Something like: from datetime import date session.query(List).filter( and_(List.expire = date(2007, 12, 1), List.expire date(2008, 1, 1)) ).all() Adding one month to a date is pretty easy, but if you wanted to do any more complicated date calculations, the dateutil library is very good: http://labix.org/python-dateutil Ah yes, was so obsessed with the solution. Letting the mysql work is much more efficient. It will matter under heavy load. I will check out dateutil, thanks. br Adam --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
