Hi there,
I have a problem with foreign keys that seems to occur when I combine
reflection and explicit schemas, in the context of MySQL. I've confirmed
this problem with both rc2 and the trunk. It's best demonstrated with
some failing code:
Imagine the following MySQL database 'somedb':
CREATE TABLE somedb.a (
id int PRIMARY KEY auto_increment NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE somedb.b (
id int PRIMARY KEY auto_increment NOT NULL,
a_id int NOT NULL,
FOREIGN KEY (a_id) REFERENCES somedb.a(id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
And the following code:
from sqlalchemy import *
from sqlalchemy.orm import mapper, relation, sessionmaker
engine = create_engine('mysql:///somedb')
meta = MetaData()
meta.bind = engine
a_table = Table(
'a',
meta,
schema='somedb',
autoload=True)
b_table = Table(
'b',
meta,
schema='somedb',
autoload=True)
class A(object):
pass
class B(object):
pass
mapper(A, a_table,
properties={'bs': relation(B)})
mapper(B, b_table)
Session = sessionmaker(bind=engine)
session = Session()
print session.query(A).all()
When executing this code, the last line fails with the following error:
Traceback (most recent call last):
File "bin/devpython", line 138, in ?
execfile(sys.argv[0])
File "experiment.py", line 33, in ?
print session.query(A).all()
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/session.py",
line 914, in query
return self._query_cls(entities, self, **kwargs)
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/query.py",
line 95, in __init__
self.__setup_aliasizers(self._entities)
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/query.py",
line 109, in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity)
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/util.py",
line 454, in _entity_info
mapper = class_mapper(entity, compile)
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/util.py",
line 531, in class_mapper
mapper = mapper.compile()
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/mapper.py",
line 371, in compile
mapper.__initialize_properties()
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/mapper.py",
line 393, in __initialize_properties
prop.init(key, self)
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/interfaces.py",
line 384, in init
self.do_init()
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/properties.py",
line 531, in do_init
self._determine_joins()
File
"/home/faassen/.buildout/eggs/SQLAlchemy-0.5.0rc2-py2.4.egg/sqlalchemy/orm/properties.py",
line 604, in _determine_joins
raise sa_exc.ArgumentError("Could not determine join condition
between "
sqlalchemy.exc.ArgumentError: Could not determine join condition between
parent/child tables on relation A.bs. Specify a 'primaryjoin'
expression. If this is a many-to-many relation, 'secondaryjoin' is
needed as well.
This code *only* fails if I designate an explicit 'schema' in the table
statements. If I leave these out, things work as expected. Since I'm
interested in working with reflected tables that reside in multiple
schemas, this is a problem.
Digging around indicates this that _search_for_join, defined in
_determine_joins, does not actually find the join clause. Going deeper
traces the failure down to the Join class in sqlalchemy.sql.expression,
which fails in self._match_primaries in its __init__ method. This in
turn brings us to sqlalchemy.sql.util.join_condition, which has
fk.get_referent() return None if schemas are explicitly specified, and
work fine if not.
fk.get_referent() uses corresponding_column, and this in turn tries to
use contains_column() which returns False in the schema case, but true
if 'schema' is not explicitly verified.
Why I don't know. The repr of the column passed into contains_column
looks the same as the repr of the column in the table, but apparently
it's not exactly the same instance. Something somewhere is making the
column to be different.
Is this a bug? If so, how would we go around solving it?
Regards,
Martijn
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
