Ran across a weird issue this morning, not sure if its even a SA issue, may just be PostgreSQL (8.3) being weird. I recently merged two pgsql databases into one database with two schemas, and a foreign key connecting two of the tables. he first schema is in the search path, the second is not. The problem occurred when I specified the schema='schamaA' in my Table() calls then SA wasn't able to see any foreign keys referencing it, but things worked just fine when I removed them.
I suspect the problem is that even though I am specifying the schema name in my "add constraint ... foreign key... references schema_name.table.column", PostgreSQL only seems to store the table name, I believe this is because that schema is in the search path. As I said, I have a work-around, just wanted to mention this in case someone else runs into this issue. And of course SQLAlchemy is great and has made my life easier, and in general works like a dream. The error message: "Can't locate any foreign key columns in primary join condition 'job.outdir_assetuid = nodehierarchy.uid' for relationship 'BObj.A (AObj)'. Specify 'foreign_keys' argument to indicate which columns in the join condition are foreign." Test case: ---------------------------------------- from sqlalchemy import * from sqlalchemy.orm import * import sys db_host = 'server' db_name = 'db' db_user = 'sqluser' db_pass = 'notmypassword' db_uri = 'postgres://'+db_user+':'+db_pass+'@'+db_host+'/'+db_name metadata = MetaData(db_uri) tbl_a = Table('nodehierarchy', metadata, autoload=True) # works #tbl_a = Table('nodehierarchy', metadata, schema='asset', autoload=True) # doesn't tbl_b = Table('job', metadata, schema='farm', autoload=True) class AObj(object): def __repr__(self): return self.name class BObj(object): def __repr__(self): return self.name a_mapper = mapper(AObj, tbl_a, properties = {}) b_mapper = mapper(BObj, tbl_b, properties = { 'A' : relation(AObj, primaryjoin=(tbl_b.c.outdir_assetuid==tbl_a.c.uid)) }) def testy(session): bs= session.query(BObj).all() for b in bs: s = b.name if b.A: s += "," + b.A.name print s try: session = create_session() testy(session) except Exception, e: print e sys.exit (1) sys.exit (0) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---