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 [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
-~----------~----~----~----~------~----~------~--~---
