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 [email protected]
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
