Does sqlalchemy have some limit in handling composite primary keys with autoload?
I've tried postgres 8.1 and SA 0.3 or trunk. I have a 'tree' of four tables, and SA does not create the full join to follow composite foreign keys. The schema and test model is in the attachment. As I've seen, the last query executed by in model.py is: SELECT factories.cod_fctry AS factories_cod_fctry, factories.cod_company AS factories_cod_company, factories.cod_bl AS factories_cod_bl, factories.cod_practice AS factories_cod_practice FROM factories WHERE factories.cod_company = %(lazy_7eca)s AND factories.cod_fctry = %(lazy_eaaf)s ORDER BY factories.cod_company This means only two of the four columns composing the foreign key are used. When I explicitly set the join, with the following patch to model.py mapper(User, tbl['users'], properties = { - 'factory' : relation(Factory, backref='users') + 'factory' : relation(Factory, backref='users', + primaryjoin = and_( + tbl['factories'].c.cod_company == tbl['users'].c.cod_company, + tbl['factories'].c.cod_practice == tbl['users'].c.cod_practice, + tbl['factories'].c.cod_bl == tbl['users'].c.cod_bl, + tbl['factories'].c.cod_fctry == tbl['users'].c.cod_fctry + ) + ) I have the correct query, i.e. SELECT factories.cod_fctry AS factories_cod_fctry, factories.cod_company AS factories_cod_company, factories.cod_bl AS factories_cod_bl, factories.cod_practice AS factories_cod_practice FROM factories WHERE factories.cod_company = %(factories_cod_company)s AND factories.cod_practice = %(factories_cod_practice)s AND factories.cod_bl = %(factories_cod_bl)s AND factories.cod_fctry = %(factories_cod_fctry)s ORDER BY factories.cod_company >From the FAQ: ... Lots of scenarios simply are not possible in certain scenarios, such as case-sensitive schemas, foreign key reflection, etc. ... this makes me think there has to be some (possibly old) issue. Any idea? Thanks --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
CREATE TABLE companies ( cod_company VARCHAR(8) PRIMARY KEY ); CREATE TABLE practices ( cod_company VARCHAR(8) NOT NULL REFERENCES companies, cod_practice VARCHAR(8) NOT NULL, PRIMARY KEY (cod_company, cod_practice) ); CREATE TABLE businesslines ( cod_company VARCHAR(8) NOT NULL, cod_practice VARCHAR(8) NOT NULL, cod_bl VARCHAR(8) NOT NULL, FOREIGN KEY (cod_company, cod_practice) REFERENCES practices (cod_company, cod_practice) MATCH FULL, PRIMARY KEY (cod_company, cod_practice, cod_bl) ); CREATE TABLE factories ( cod_company VARCHAR(8) NOT NULL, cod_practice VARCHAR(8) NOT NULL, cod_bl VARCHAR(8) NOT NULL, cod_fctry VARCHAR(8) NOT NULL, FOREIGN KEY (cod_company, cod_practice, cod_bl) REFERENCES businesslines (cod_company, cod_practice, cod_bl) MATCH FULL, PRIMARY KEY (cod_company, cod_practice, cod_bl, cod_fctry) ); CREATE TABLE users ( uid VARCHAR(32) PRIMARY KEY, cod_company VARCHAR(8) NOT NULL, cod_practice VARCHAR(8) NOT NULL, cod_bl VARCHAR(8) NOT NULL, cod_fctry VARCHAR(8) NOT NULL, FOREIGN KEY (cod_company, cod_practice, cod_bl, cod_fctry) REFERENCES factories (cod_company, cod_practice, cod_bl, cod_fctry) MATCH FULL );
#!/usr/bin/env python from sqlalchemy import Table, relation, create_engine, create_session, BoundMetaData, mapper, and_ pgeng = create_engine('postgres://user:[EMAIL PROTECTED]:5432/dbname') pgmetadata = BoundMetaData(pgeng) pgeng.echo = False session = create_session() tbl = {} for name, in pgeng.execute("SELECT tablename FROM pg_tables WHERE schemaname='public'").fetchall(): tbl[name] = Table(name, pgmetadata, autoload=True) class Company(object): pass mapper(Company, tbl['companies']) class Practice(object): pass mapper(Practice, tbl['practices'], properties = { 'company' : relation(Company, backref='practices') }) class BusinessLine(object): pass mapper(BusinessLine, tbl['businesslines'], properties = { 'practice' : relation(Practice, backref='businesslines') }) class Factory(object): pass mapper(Factory, tbl['factories'], properties = { 'businessline' : relation(BusinessLine, backref='factories') }) class User(object): pass mapper(User, tbl['users'], properties = { 'factory' : relation(Factory, backref='users') }) com = Company() com.cod_company = 'COM' pra = Practice() pra.cod_company='COM' pra.cod_practice='PRA' bl = BusinessLine() bl.cod_company='COM' bl.cod_practice='PRA' bl.cod_bl='BL' fac = Factory() fac.cod_company='COM' fac.cod_practice='PRA' fac.cod_bl='BL' fac.cod_fctry='FAC' usr = User() usr.uid = 'john' usr.cod_company='COM' usr.cod_practice='PRA' usr.cod_bl='BL' usr.cod_fctry='FAC' session.save(com) session.save(pra) session.save(bl) session.save(fac) session.save(usr) session.flush() session.clear() us = session.query(User).select()[0] pgeng.echo = True us.factory