That should be working now in r5203. The reflection code was missing an
edge case where an explicit schema= is the same as the connection's
schema. Switching those to schema=None should work as intended if you
need a workaround on a released version.
Cheers,
Jason
Martijn Faassen wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
