[sqlalchemy] Re: eagerloading polymorphic mapper
well, i tried it manualy and it works (sqlite). here the eagerloading query: model-description (joined_inheritance): class A: name = Text() class B(A): address = reference( Adress) nom = reference( Nomerator) class C(A): pass select * from A left outer join B on A.db_id = B.db_id left outer join C on A.db_id = C.db_id left outer join Address as a1 on p.address_id = address.db_id left outer join Nomerator as a2 on p.nom_id = a2.db_id ; or with subselects: select * from A left outer join B on A.db_id = B.db_id left outer join C on A.db_id = C.db_id left outer join ( select * from Address ) as a1 on p.address_id = address.db_id left outer join ( select * from Nomerator) as a2 on p.nom_id = a2.db_id ; also, i dont see a reason for it not to work if the (A jon B join C) is a polymunion - all the same, all columns will be present there, having None where missing. 0.4.3? On Monday 14 January 2008 18:56:16 svilen wrote: On Monday 14 January 2008 18:35:40 Michael Bayer wrote: On Jan 14, 2008, at 11:29 AM, svilen wrote: On Monday 14 January 2008 17:19:14 Michael Bayer wrote: On Jan 14, 2008, at 8:41 AM, svilen wrote: i have, say, base class A, inherited by two children B and C. B has an attribute/relation 'address', A and C do not have it. So i had a query(A).eagerload( 'address') and that did work before r3912. But later it gives an error - mapper|A has no property 'address'. Any hint how to do it now? what kind of inheritance/mapping from A-B ? i cant really imagine any way that kind of eager load could have worked since the address property of B does not (and has never) get consulted in that case. plain joined?... hmm. maybe it did not really work (eagerly) but lazy-load has fired instead... seems that's the case. anyway. some way to accomplish such thing? no ! this the same issue with the Channel-CatalogChannel thing, yes i guessed it.. your query is against A...attributes that are only on B don't enter into the equation here. this is somewhat different, my query/filter is on attributes that do exist in A; i only want the ORM to postprocess certain things... there will be 'address' column in the result-set anyway (empty or not), why it cannot be eagerloaded via B.address? But also, if youre using select_table, we dont yet support eager loads from a polymorphic-unioned mapper in any case (though we are close). it is not polymunion, joined_inh works via left-outer-join. well, no is no. --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
Michael Bayer wrote: that doesnt sound right. taking out select_table, and doing: print Media .query .select_from (media_table .join (catalog_table ).join (catalog_channel_table )).filter(CatalogChannel.c.id_channel==playlist.id_channel).all() leads to the SQL: SELECT medias.id AS medias_id, medias.name AS medias_name, medias.id_catalog AS medias_id_catalog FROM medias JOIN catalogs ON catalogs.id = medias.id_catalog JOIN catalog_channels ON catalogs.id = catalog_channels.id WHERE catalog_channels.id_channel = ? ORDER BY medias.oid which is entirely acceptable (and works in mysql). Okay, my fault. I was editing the wrong code in my test case concerning the select_from alternative. Let's go back to that new feature from r4060 you've proposed, concerning joins directly from classes. Attached is my updated test case. If I wanted to join only from classes, I'd suppose we'd have the following syntax: Media.query.join([Media.catalog, CatalogChannel.id, CatalogChannel.channel]).filter(CatalogChannel.c.id_channel==playlist.id_channel).all() This would mean join Media on Catalog, join CatalogChannel on Catalog, join CatalogChannel on Channel; WHERE CatalogChannel.c.id_channel==foo. This is just an idea, but here the join with CatalogChannel.id would mean that is need to figure out that PK is also FK to Catalog. 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 -~--~~~~--~~--~--~--- SA_joined_inherited_class.py Description: application/python
[sqlalchemy] Re: Is there a way to replace object in DB?
On Jan 11, 2008 8:41 PM, Michael Bayer [EMAIL PROTECTED] wrote: what that looks like to me is that you're attempting to query the database for object ID #1 using merge(). when you merge(), its going to treat the object similarly to how it does using session.save_or_update(). that is, it looks for an _instance_key attribute to determine if the object represents a transient or persisted instance. So you could hack the way youre doing it like: obj2 = ModelObject(1, u'title2') obj2._instance_key = session.identity_key(instance=obj2) session.merge(obj2) session.commit() we have yet to define a completely public API for the above operation, i.e. treat this object as though its persistent. im not sure yet how we could define one that has a straightforward use case which wouldn't add confusion. Sometimes this doesn't work: ---8--- import sqlalchemy as sa, logging from sqlalchemy.orm import mapper, sessionmaker, relation logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) logging.basicConfig() class ModelObject(object): def __init__(self, id, title): self.id = id self.title = title class ModelReferer(object): def __init__(self, id, object): self.id = id self.object = object metadata = sa.MetaData() objectsTable = sa.Table( 'Objects', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('title', sa.String(255), nullable=False), ) referersTable = sa.Table( 'Referers', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('objectId', sa.Integer, sa.ForeignKey('Objects.id'), nullable=False), ) objectsMapper = mapper(ModelObject, objectsTable) referersMapper = mapper(ModelReferer, referersTable, properties={'object': relation(ModelObject)}) engine = sa.create_engine('sqlite://') metadata.create_all(engine, checkfirst=True) session = sessionmaker(bind=engine)() def replace(session, obj): identityKey = session.identity_key(instance=obj) oldObj = session.get(*identityKey[:2]) if oldObj is None: session.save(obj) return obj else: obj._instance_key = identityKey return session.merge(obj) obj1 = ModelObject(1, u'title1') replace(session, obj1) ref1 = ModelReferer(1, obj1) replace(session, ref1) session.commit() session.clear() # Another program. We have to insure that object with id=1 exists in DB and has # certain properties. obj2 = replace(session, ModelObject(1, u'title2')) session.commit() ref2 = ModelReferer(1, obj2) replace(session, ref2) session.commit() ---8--- The last commit fails with: sqlalchemy.exceptions.IntegrityError: (IntegrityError) Referers.objectId may not be NULL u'UPDATE Referers SET objectId=? WHERE Referers.id = ?' [None, 1] --~--~-~--~~~---~--~~ 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: Doing a dynamic Update
Rick, On Jan 10, 7:02 pm, Rick Morrison [EMAIL PROTECTED] wrote: For a stepwise migration from raw, SQL, it will probably be easier to get your mind around the SQL-expression side of the library, and then adopt ORM features as you feel comfortable with them. On the SQL-expression side of the library, you'll find that your Table() object has a collection called c (for Columns). It's a dict-like collection that supports retrieving the column by name: Table.update() takes a dictionary of updates, so the name-based access is already in there: tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname = newvalue)) or using sessions: S.execute(tbl.update(tbl.c.dateworked == mydate), dict(columnname = newvalue)) HTH, Rick Sorry I didn't reply sooner. I got busy with other things at work last week. To use your first suggestion, I would have to use a series of if statements like below, correct? if colName == 'someColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname =newvalue)) elif colName == 'anotherColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(othercolumnname =newvalue)) else: pass This will work, but it's not quite as elegant as I had hoped. The sessions method looks like it would have the same issue. Thanks for the advise. Mike --~--~-~--~~~---~--~~ 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] parent table
Hello all, Can I get the parent table from an inherited table? something like: table_a = Table() table_b = Table() mapper(TableA, table_a) mapper(TableB, table_b, inherits=TableA) table_b.get_parent() the reason I need it, is that I need to get information about the master table, because when I get the table from an fk, the table.c atribute get just columns from that table, and not all columns including the master tables, in other words, getting table by this way fk_table = column.foreign_keys[0].column.table I get a shema.Table object and not the join object that represents the entire fk table. any suggestion? Att -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) --~--~-~--~~~---~--~~ 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] object has no attribute '_sa_session_id' error on session.clear()
Hi, I've an error when I try to execute a clear() method from session object. Traceback (most recent call last): File V:\nacer\bin\gui\planilla.py, line 280, in on_btn_apply_clicked self._savePlanilla() File V:\nacer\bin\gui\planilla.py, line 200, in _savePlanilla PlanillaService().save(planilla) File V:\nacer\bin\common\services.py, line 16, in save session.clear() File c:\programs\python25\lib\site-packages\sqlalchemy-0.4.2p3- py2.5.egg\sqlalchemy\orm\session.py, line 577, in clear self._unattach(instance) File c:\programs\python25\lib\site-packages\sqlalchemy-0.4.2p3- py2.5.egg\sqlalchemy\orm\session.py, line 1039, in _unattach if instance._sa_session_id == self.hash_key: AttributeError: 'PlanillaDet' object has no attribute '_sa_session_id' I do, session.save_or_update(dataset) session.flush([dataset]) session.clear() Why this error ocurr? TIA. --~--~-~--~~~---~--~~ 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: eagerloading polymorphic mapper
On Jan 15, 2008, at 4:33 AM, svilen wrote: also, i dont see a reason for it not to work if the (A jon B join C) is a polymunion - all the same, all columns will be present there, having None where missing. 0.4.3? unlikely, I dont see how it could work from a generic standpoint. the query generates SQL based on the attributes attached to A. if it had to also loop through all the attributes of B, C, D, E, F, etc. and attempt to have all of those add their clauses to the SQL, theyd all have to assume that the selectable for A even supports receiving their joins, etc. --~--~-~--~~~---~--~~ 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: Doing a dynamic Update
Hey Mike You don't need to go through that. Just populate the dictionary used for the execute() with the appropriate column name as the key, and you're good to go. On Jan 15, 2008 10:05 AM, Mike [EMAIL PROTECTED] wrote: Rick, On Jan 10, 7:02 pm, Rick Morrison [EMAIL PROTECTED] wrote: For a stepwise migration from raw, SQL, it will probably be easier to get your mind around the SQL-expression side of the library, and then adopt ORM features as you feel comfortable with them. On the SQL-expression side of the library, you'll find that your Table() object has a collection called c (for Columns). It's a dict-like collection that supports retrieving the column by name: Table.update() takes a dictionary of updates, so the name-based access is already in there: tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname = newvalue)) or using sessions: S.execute(tbl.update(tbl.c.dateworked == mydate), dict(columnname = newvalue)) HTH, Rick Sorry I didn't reply sooner. I got busy with other things at work last week. To use your first suggestion, I would have to use a series of if statements like below, correct? if colName == 'someColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname =newvalue)) elif colName == 'anotherColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(othercolumnname =newvalue)) else: pass This will work, but it's not quite as elegant as I had hoped. The sessions method looks like it would have the same issue. Thanks for the advise. Mike --~--~-~--~~~---~--~~ 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: Is there a way to replace object in DB?
On Jan 15, 2008, at 8:20 AM, Denis S. Otkidach wrote: On Jan 11, 2008 8:41 PM, Michael Bayer [EMAIL PROTECTED] wrote: what that looks like to me is that you're attempting to query the database for object ID #1 using merge(). when you merge(), its going to treat the object similarly to how it does using session.save_or_update(). that is, it looks for an _instance_key attribute to determine if the object represents a transient or persisted instance. So you could hack the way youre doing it like: obj2 = ModelObject(1, u'title2') obj2._instance_key = session.identity_key(instance=obj2) session.merge(obj2) session.commit() we have yet to define a completely public API for the above operation, i.e. treat this object as though its persistent. im not sure yet how we could define one that has a straightforward use case which wouldn't add confusion. Sometimes this doesn't work: The last commit fails with: sqlalchemy.exceptions.IntegrityError: (IntegrityError) Referers.objectId may not be NULL u'UPDATE Referers SET objectId=? WHERE Referers.id = ?' [None, 1] right thats because the instance doesnt exist yet. its better for you to just use the straight ahead query.get(), if None then save() approach. --~--~-~--~~~---~--~~ 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: object has no attribute '_sa_session_id' error on session.clear()
On Jan 15, 2008, at 9:59 AM, maxi wrote: Hi, I've an error when I try to execute a clear() method from session object. Traceback (most recent call last): File V:\nacer\bin\gui\planilla.py, line 280, in on_btn_apply_clicked self._savePlanilla() File V:\nacer\bin\gui\planilla.py, line 200, in _savePlanilla PlanillaService().save(planilla) File V:\nacer\bin\common\services.py, line 16, in save session.clear() File c:\programs\python25\lib\site-packages\sqlalchemy-0.4.2p3- py2.5.egg\sqlalchemy\orm\session.py, line 577, in clear self._unattach(instance) File c:\programs\python25\lib\site-packages\sqlalchemy-0.4.2p3- py2.5.egg\sqlalchemy\orm\session.py, line 1039, in _unattach if instance._sa_session_id == self.hash_key: AttributeError: 'PlanillaDet' object has no attribute '_sa_session_id' I do, session.save_or_update(dataset) session.flush([dataset]) session.clear() Why this error ocurr? thats a bug. Can you provide a full test case ? --~--~-~--~~~---~--~~ 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: eagerloading polymorphic mapper
On Tuesday 15 January 2008 17:19:49 Michael Bayer wrote: On Jan 15, 2008, at 4:33 AM, svilen wrote: also, i dont see a reason for it not to work if the (A jon B join C) is a polymunion - all the same, all columns will be present there, having None where missing. 0.4.3? unlikely, I dont see how it could work from a generic standpoint. the query generates SQL based on the attributes attached to A. if it had to also loop through all the attributes of B, C, D, E, F, etc. and attempt to have all of those add their clauses to the SQL, theyd all have to assume that the selectable for A even supports receiving their joins, etc. hmmm, specify explicitly? e.g. query(A).eagerload( B.address) joined-inh via left-outer-join is enough, no need for polymunion. IMO this will be big plus for the ORM - eagerloading polymorphical child attributes - moving further away from SQL-like-looking stuff. i dont know how the current machinery for eagerload works, but imo knowing your level of lookahead-design, it should not be hard to apply that machinery over a polymorphic mapper/query? ciao svilen --~--~-~--~~~---~--~~ 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] SelectResults and group_by
Hi all for some reason i cannot work over the code before the result. The result was a SelectResults qry. Now i must do something like: MySelectResults.group_by( table.c.colname ).having( func.count( table.c.colname ) x ) Does anyone have any suggestion? Thank you Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054 ++ --~--~-~--~~~---~--~~ 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: eagerloading polymorphic mapper
On Jan 15, 2008, at 10:49 AM, svilen wrote: hmmm, specify explicitly? e.g. query(A).eagerload( B.address) joined-inh via left-outer-join is enough, no need for polymunion. uh well i supposeeagerload options dont really affect the traversal that way and it would take some non-trivial rearrangement of internals. IMO this will be big plus for the ORM - eagerloading polymorphical child attributes - moving further away from SQL-like-looking stuff. we *like* SQL ! we dont want to become OQL. i dont know how the current machinery for eagerload works, but imo knowing your level of lookahead-design, it should not be hard to apply that machinery over a polymorphic mapper/query? theres plenty of much higher priority issues than this one in the queue...considering that you can already get the results you want with this one using direct SQL. --~--~-~--~~~---~--~~ 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: two scoped sessions in one app?
On Jan 15, 2008, at 7:37 AM, Max Ischenko wrote: Hello, I need to access two databases from my app. I'm trying to use scoped_session helper: SessionA = scoped_session(sessionmaker(autoflush=True, transactional=False)) SessionB = scoped_session(sessionmaker(autoflush=True, transactional=False)) ... mapper(SessionA.mapper, ClassFromA ...) mapper(SessionB.mapper, ClassFromB ...) But when I try to query against SessionA it looks for a table_a in database B. How do I separate two sessions properly? if you are using multiple scoped sessions you won't be able to use Session.mapper - the idea of Session.mapper is that all instances get tied to a single contextual session. --~--~-~--~~~---~--~~ 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: Doing a dynamic Update
On Jan 15, 9:20 am, Rick Morrison [EMAIL PROTECTED] wrote: Hey Mike You don't need to go through that. Just populate the dictionary used for the execute() with the appropriate column name as the key, and you're good to go. On Jan 15, 2008 10:05 AM, Mike [EMAIL PROTECTED] wrote: content snipped for brevity if colName == 'someColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname =newvalue)) elif colName == 'anotherColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(othercolumnname =newvalue)) else: pass This will work, but it's not quite as elegant as I had hoped. The sessions method looks like it would have the same issue. Thanks for the advise. Mike Ok...so here goes: # Column names: #netname #pref_name #pref_value def main(tblName, key): engine = create_engine('mssql://dbName:[EMAIL PROTECTED]/%s' % tblName) conn = engine.connect() # create MetaData meta = MetaData() # bind to an engine meta.bind = engine # create metadata meta.create_all() tbl.update(tbl.c.netname=='saw').execute(dict(key = 'New Val')) if __name__ == '__main__': tblName = 'tbl_Acct_Prefs' pref_value = 'someval' main(tblName, pref_value) This returns Incorrect syntax near the keyword 'WHERE'. DB-Lib error message 10007, severity 5: General SQL Server error: Check messages from the SQL Server. 'UPDATE [tbl_Acct_Prefs] SET WHERE [tbl_Acct_Prefs].netname = % (tbl_Acct_Prefs_netname_1)s' {'tbl_Acct_Prefs_netname_1': 'saw'} If I just do this: if __name__ == '__main__': tblName = 'tbl_Acct_Prefs' main(tblName, pref_value) I get a variable undefined error, which I understand. I think I know what you mean, but I don't know how to pass in an object that doesn't exist yet. I tried creating a dict inside the main() where I did this: colDict = {'netname':netname, 'pref_name':pref_name, 'pref_value':pref_value} tbl.update(tbl.c.netname=='saw').execute(dict(colDict[key] = 'New Val')) where key was one of the string keys in colDict...but it didn't like that much either. ORM appears to be harder to grasp than I had originally thought. Mike --~--~-~--~~~---~--~~ 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: Doing a dynamic Update
Two issues: a) You need to give SA a table definition for the table you're trying to update. b) You need to specify the name of the column to update in the dict(), not the string 'key' I've updated the script to work by passing in both the column name to update and the update value to use. I've made my changes in bold below: def main(*key, val*): engine = create_engine('mssql://dbName:[EMAIL PROTECTED]') * # No table name in URI !! /%s' % tblName)* conn = engine.connect() # create MetaData meta = MetaData() # bind to an engine meta.bind = engine * # specify table definition tbl = Table('tbl_Acct_prefs', meta, Column('netname', VARCHAR(20)), Column('pref_name', VARCHAR(40)), Column('pref_value', VARCHAR(40)) )* # create metadata *#* meta.create_all() * # === you need this only if you're creating the table with your program* tbl.update(tbl.c.netname=='saw').execute(*{key:val}*) --~--~-~--~~~---~--~~ 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: eagerloading polymorphic mapper
hmmm, specify explicitly? e.g. query(A).eagerload( B.address) joined-inh via left-outer-join is enough, no need for polymunion. i dont know how the current machinery for eagerload works, but imo knowing your level of lookahead-design, it should not be hard to apply that machinery over a polymorphic mapper/query? theres plenty of much higher priority issues than this one in the queue...considering that you can already get the results you want with this one using direct SQL. right.. i've hacked something that seems to work; It's about 20 lines split in orm.query and orm.interfaces: - such special eagerloaders are requested as query.eagerload( B.address) - and not just the name/path - query-compile calling context.exec_withpath(...) iterates over all self.mapper properties (not only select_mapper's), plus all eagerloaders of above type (i.e. non-names). Thus the 4 cases are covered: A has address / query(B).eagerload('address') #works before A has address / query(A).eagerload('address') #new - did not work before B has address / query(B).eagerload('address') #works before B has address / query(A).eagerload(B.address) #new - not possible before (in all these B inherits A via joined inheritance; A is polymorphic via left-outer-joins) i'm absolutely sure that this is not the completely right thing - that's what i got from the machinery-src in 2 hours - but it is something as a start... sure it needs correctness tests etc of the sorts. g'night svilen --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- Index: orm/query.py === --- orm/query.py (revision 4032) +++ orm/query.py (working copy) @@ -991,10 +991,20 @@ # give all the attached properties a chance to modify the query # TODO: doing this off the select_mapper. if its the polymorphic mapper, then # it has no relations() on it. should we compile those too into the query ? (i.e. eagerloads) -for value in self.select_mapper.iterate_properties: + +for value in self.mapper.iterate_properties: +if self._only_load_props and value.key not in self._only_load_props: +continue +context.exec_with_path(self.mapper, value.key, value.setup, context, only_load_props=self._only_load_props) +for (mp, key) in self._eager_loaders: +if isinstance( key, str): continue #plain +value = key.property if self._only_load_props and value.key not in self._only_load_props: continue -context.exec_with_path(self.select_mapper, value.key, value.setup, context, only_load_props=self._only_load_props) +context.exec_with_path(self.mapper, key, value.setup, context, only_load_props=self._only_load_props) # additional entities/columns, add those to selection criterion for tup in self._entities: Index: orm/interfaces.py === --- orm/interfaces.py (revision 4032) +++ orm/interfaces.py (working copy) @@ -594,6 +599,7 @@ raise exceptions.ArgumentError(Can't find entity %s in Query. Current list: %r % (str(mapper), [str(m) for m in [query.mapper] + query._entities])) else: mapper = query.mapper +if isinstance( self.key,str): for token in self.key.split('.'): if current_path and token == current_path[1]: current_path = current_path[2:] @@ -604,6 +610,16 @@ path = build_path(mapper, prop.key, path) l.append(path) mapper = getattr(prop, 'mapper', None) +else: +ia = self.key +key = ia.impl.key +#from sqlalchemy.orm import class_mapper +#mp = mapper#class_mapper( ia.impl.class_) #assert mp inherits mapper? +self.key = key +path = build_path(mapper, ia, path) +l.append(path) return l PropertyOption.logger = logging.class_logger(PropertyOption)
[sqlalchemy] Re: eagerloading polymorphic mapper
On Jan 15, 2008, at 5:17 PM, [EMAIL PROTECTED] wrote: hmmm, specify explicitly? e.g. query(A).eagerload( B.address) joined-inh via left-outer-join is enough, no need for polymunion. i dont know how the current machinery for eagerload works, but imo knowing your level of lookahead-design, it should not be hard to apply that machinery over a polymorphic mapper/query? theres plenty of much higher priority issues than this one in the queue...considering that you can already get the results you want with this one using direct SQL. right.. i've hacked something that seems to work; It's about 20 lines split in orm.query and orm.interfaces: - such special eagerloaders are requested as query.eagerload( B.address) - and not just the name/path - query-compile calling context.exec_withpath(...) iterates over all self.mapper properties (not only select_mapper's), plus all eagerloaders of above type (i.e. non-names). Thus the 4 cases are covered: A has address / query(B).eagerload('address') #works before A has address / query(A).eagerload('address') #new - did not work before B has address / query(B).eagerload('address') #works before B has address / query(A).eagerload(B.address) #new - not possible before (in all these B inherits A via joined inheritance; A is polymorphic via left-outer-joins) i'm absolutely sure that this is not the completely right thing - that's what i got from the machinery-src in 2 hours - but it is something as a start... sure it needs correctness tests etc of the sorts. yeah thats the idea but it needs more work than that. for one thing you might be hitting the same MappedProperty twice using that iteration (therefore joining twice), and also it doesn't account for eager loaders like eagerload(foo.bar.bat); i.e. deeper level properties which would need to have an adjusted path sent to them (or ignored in that part of the iteration). also i can see a lot of cases where the eager loader from B is going to generate invalid SQL, such as joined table inheritance with no select_table, the query is only generated against A. B.address is going to try generating an eager join against the B table which isnt present, and youll get some kind of mess as a result. checking for this condition beforehand is bound to add lots of complexity and i only want to add features like these if they can be smoothly integrated, not lots of extra if/thens bolted 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] Re: eagerloading polymorphic mapper
Michael Bayer wrote: On Jan 15, 2008, at 5:17 PM, [EMAIL PROTECTED] wrote: hmmm, specify explicitly? e.g. query(A).eagerload( B.address) joined-inh via left-outer-join is enough, no need for polymunion. i dont know how the current machinery for eagerload works, but imo knowing your level of lookahead-design, it should not be hard to apply that machinery over a polymorphic mapper/query? theres plenty of much higher priority issues than this one in the queue...considering that you can already get the results you want with this one using direct SQL. right.. i've hacked something that seems to work; It's about 20 lines split in orm.query and orm.interfaces: - such special eagerloaders are requested as query.eagerload( B.address) - and not just the name/path - query-compile calling context.exec_withpath(...) iterates over all self.mapper properties (not only select_mapper's), plus all eagerloaders of above type (i.e. non-names). Thus the 4 cases are covered: A has address / query(B).eagerload('address') #works before A has address / query(A).eagerload('address') #new - did not work before B has address / query(B).eagerload('address') #works before B has address / query(A).eagerload(B.address) #new - not possible before (in all these B inherits A via joined inheritance; A is polymorphic via left-outer-joins) i'm absolutely sure that this is not the completely right thing - that's what i got from the machinery-src in 2 hours - but it is something as a start... sure it needs correctness tests etc of the sorts. yeah thats the idea but it needs more work than that. for one thing you might be hitting the same MappedProperty twice using that iteration (therefore joining twice), and also it doesn't account for eager loaders like eagerload(foo.bar.bat); i.e. deeper level properties which would need to have an adjusted path sent to them (or ignored in that part of the iteration). pure textual paths are going same (old) way; it needs some extra-syntax/API for multilevel descriptor-specified eagers; e.g eagerload( (B.foo, 'bar', C.bat ) ) also i can see a lot of cases where the eager loader from B is going to generate invalid SQL, such as joined table inheritance with no select_table, the query is only generated against A. B.address is going to try generating an eager join against the B table which isnt present, and youll get some kind of mess as a result. checking for this condition beforehand is bound to add lots of complexity and i only want to add features like these if they can be smoothly integrated, not lots of extra if/thens bolted on. yeahhh i know i dont see the wider sql picture... i may put all this as a ticket to remind.. maybe one day you'll be in better mood (;-) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---