[sqlalchemy] how to make a query for multilevel reference?
hi. Lets have a Person having .address having .country having .name. How would give-me-persons-which-live-in France be expressed in SA (sorry for my SQL ignorance)? e.g. all-persons-for-which person.address.country.name == 'France' thanks svil --~--~-~--~~~---~--~~ 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 to make a query for multilevel reference?
Lets have a Person having .address having .country having .name. How would give-me-persons-which-live-in France be expressed in SA (sorry for my SQL ignorance)? e.g. all-persons-for-which person.address.country.name == 'France' http://www.sqlalchemy.org/docs/datamapping.myt#datamapping_selectrelations_relselectby Unfortunately I think if your Person has a name attribute too you have no alternative but to write the join out the long way. (I'd love to find out I'm wrong though. :) ahha. Maybe the underlying query._locate_prop() can be tweaked to accept hierarchical keys in some form (e.g. a.b.c.d.e.f ), and then not search but just follow the hierarchy... e.g. #put in orm.query.Query #will search for the spec.hierarchycal key as a subkey, not just as root) def _locate_prop(self, key, start=None): import properties keys = [] seen = util.Set() def search_for_prop(mapper_, fullkey): if mapper_ in seen: return None seen.add(mapper_) key = fullkey[0] if mapper_.props.has_key(key): prop = mapper_.props[key] if len(fullkey)==1: if isinstance(prop, properties.SynonymProperty): prop = mapper_.props[prop.name] if isinstance(prop, properties.PropertyLoader): keys.insert(0, prop.key) return prop else: props = [prop] fullkey = fullkey[1:] for prop in mapper_.props.values(): if not isinstance(prop, properties.PropertyLoader): continue x = search_for_prop(prop.mapper, fullkey) if x: keys.insert(0, prop.key) return x return None p = search_for_prop(start or self.mapper, key.split('.') ) if p is None: raise exceptions.InvalidRequestError(Cant locate property named '%s' % key) return [keys, p] .. person = Person() person.address = Address() person.address.country = Country() person.address.country.name = 'france' session.save(person) q1 = session.query(D).select_by( name='france') q2 = session.query(D).select_by( **{'country.name':'france'} ) q3 = session.query(D).select_by( **{'address.country.name':'france'} ) assert q1 == q2 == q3 ... do try it, it seems to work... have fun svil --~--~-~--~~~---~--~~ 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 to make a query for multilevel reference?
how about q = session.query(D) q.select(q.join_via([address, country]) (Country.c.name == 'france')) or q = SelectResults(session.query(D)) q.join_to(address).join_to(country).select(Country.c.name=='france') im not down with shoving literal strings into **kwargs at all --~--~-~--~~~---~--~~ 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 to make a query for multilevel reference?
Hey what's with the bitwise AND operator? Does that actually work to AND together parts of WHERE clauses? On 1/21/07, Michael Bayer [EMAIL PROTECTED] wrote: how about q = session.query(D) q.select(q.join_via([address, country]) (Country.c.name == 'france')) or q = SelectResults(session.query(D)) q.join_to(address).join_to(country).select(Country.c.name=='france') im not down with shoving literal strings into **kwargs at all --~--~-~--~~~---~--~~ 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: ORM ramblings 2 - and announcing some o2r wrapper
the concretes fail because concrete is not a completed feature at the momentim not even looking at those yet. the session.clear() should be done in all cases, the tests are completely pointless without it since you are just re-displaying the relationships you already constructed. so i made it just session.clear() in all cases. add this to line 110: remote_side=(Alink=='A' and table_A.c.id or None), and all non-concrete tests pass when no_sisters is turned on. um, whats no_sisters=False, is that taking A and pointing it to itself ? or a different instance of A ? if you want to help me out, creating non-dynamic forms of the failing tests, like 2 or 3 of them (i.e. one or two concretes, one sisters=True test), will save me the trouble of going through this script and extracting them myself. then again ive said that in every email... also this app is becoming close to something useful so it may become part of the unit tests at some point, although i cant help but think its overly exhaustive for a small range of conditions (like, what if we have A-B-C, 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: how to make a query for multilevel reference?
yes, although its a little inconvenient because it takes a high precedence in the order of operations (thus you have to put () around all expressions) --~--~-~--~~~---~--~~ 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: ORM ramblings 2 - and announcing some o2r wrapper
the concretes fail because concrete is not a completed feature at the momentim not even looking at those yet. the session.clear() should be done in all cases, the tests are completely pointless without it since you are just re-displaying the relationships you already constructed. so i made it just session.clear() in all cases. add this to line 110: remote_side=(Alink=='A' and table_A.c.id or None), and all non-concrete tests pass when no_sisters is turned on. um, whats no_sisters=False, is that taking A and pointing it to itself ? yes. or a different instance of A ? if you want to help me out, creating non-dynamic forms of the failing tests, like 2 or 3 of them (i.e. one or two concretes, one sisters=True test), will save me the trouble of going through this script and extracting them myself. then again ive said that in every email... yes-yes-yes. just wait a little bit.. i'm generating them all. also this app is becoming close to something useful so it may become part of the unit tests at some point, although i cant help but think its overly exhaustive for a small range of conditions (like, what if we have A-B-C, etc.). this is not app, just a testcase ;-) Well, my idea is: i want to prove that all primitive operations needed for straight-forward o2r mapping, work in SA - in the way specified in this case. i have more tests for the multilevel inheritance, mixed type inheritance, etc, 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: PickleType too small
I have tried adding max_allowed_packet=16M to my.ini under [mysqld] but it didn't work. As far as I have seen BLOB type is used when mapping PickleType to MySQL. I would like to use LONGBLOB, so I did: class MyPickle(PickleType): impl = mysql.MSLongBlob and Column('data',MyPickle()) in a table specification, but I got error message: AttributeError: 'module' object has no attribute 'MSLongBlob'. Any further suggestions? --~--~-~--~~~---~--~~ 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: PickleType too small
there is no MSLongBlob at the moment. there is an MSMediumBlob which comes out as MEDIUMBLOB, mysql says they are 16 megs in size. or try doing this: class MSLongBlob(MSBinary): def get_col_spec(self): return LONGBLOB On Jan 21, 2007, at 5:03 PM, milena wrote: I have tried adding max_allowed_packet=16M to my.ini under [mysqld] but it didn't work. As far as I have seen BLOB type is used when mapping PickleType to MySQL. I would like to use LONGBLOB, so I did: class MyPickle(PickleType): impl = mysql.MSLongBlob and Column('data',MyPickle()) in a table specification, but I got error message: AttributeError: 'module' object has no attribute 'MSLongBlob'. Any further suggestions? --~--~-~--~~~---~--~~ 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: ORM ramblings 2 - and announcing some o2r wrapper
creating non-dynamic forms of the failing tests, like 2 or 3 of them (i.e. one or two concretes, one sisters=True test), will save me the trouble of going through this script and extracting them myself. here. The thing generates them now itself. $python sa_ref_A_B_A_all.py relink generate_testfile 34 of 115 failed $ python _test_AB_cases.py 34 of 115 failed (u can give -v, echo, debug, dump on commandline of _test*.py) maybe simplest one of them: tableinh, A=None B=A1 BA=None $ py _test_AB_cases.py AB.test_AB_inh_tableinh__poly_1__Alink_None__Blink_A1__BAlink_None config: dump=False, echo=False, session_clear=True, debug=False -- Traceback (most recent call last): File _test_AB_cases.py, line 5077, in test_AB_inh_tableinh__poly_1__Alink_None__Blink_A1__BAlink_None me.query( session, A,B, table_A,table_B, a.id, b.id, sa,sb, samulti, sbmulti ) File /home/az/src/hor-trunk/model/db/sa_o2rm/tests/sa_gentestbase.py, line 40, in query me.assertEqual( sb, str(q)) AssertionError: 'B( id=2 name=ben data2=gun link2=a1 )' != 'B( id=2 name=ben data2=gun link2=anna )' -- all of the failures are of this kind. hmm... extractng the failed case from that 110-functions file is not a pleasant job... Maybe i should put each in separate file then... tomorrow. ciao svil --~--~-~--~~~---~--~~ 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_AB_combinations.tbz Description: application/tbz
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
im playing with some code to do this automatically, but looking back at sa_ref_A_B_A_all.py, change your primaryjoins to: mapper_A/link1: primaryjoin= table_A.c.link1_id==(Alink=='A' and (poly and Ajoin or table_A) or table_B).c.id, mapper_B/link2: primaryjoin= table_B.c.link2_id==(Blink=='A' and (poly and Ajoin or table_A) or table_B).c.id, i.e. when using a polymorphic union to select, make the primary join go from the parent table to the child selectable, instead of a table deep inside the child selectable (the selectable is the polymorphic union). then I get no failures outside of the concrete test cases. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---