OK MySQL can't tell SQLAlchemy what foreign keys you've set up, since by
default MySQL doesn't have any support for them and ignores what you've told it
- nothing comes back. The ORM uses foreign keys to figure out how it should
join between tables during configuration.
If you use InnoDB tables then the problem will resolve:
Table('parent', metadata, ..., mysql_engine='innoDB')
metadata.create_all()
otherwise it will most prefer if you just give the foreign keys in the table:
child_table = table('child', metadata,
Column('parent_id', Integer, ForeignKey('parent.id')),
autoload=True
)
either of those two approaches is probably best. Otherwise you'd need to tell
relationship() about the "primaryjoin" and the "foreign_keys" and I've observed
that the "foregin_keys" setting seems to confuse new users more than just
establishing the FK data on the Table object, which is why I'm recommending
that in this case.
On Sep 8, 2010, at 7:05 AM, Massi wrote:
> Hi everyone, I'm getting in troubles doing some experiments with
> sqlalchemy (0.6.3) orm. Here are two code snippets to show the
> problems I'm encountering.
>
> ## map_1.py
> engine = create_engine("mysql://user:passw...@localhost/mydb")
> metadata = MetaData(engine)
>
> parent_table = Table('parent', metadata,
>Column('id', Integer, primary_key=True))
> child_table = Table('child', metadata,
>Column('id', Integer, primary_key=True),
>Column('parent_id', Integer, ForeignKey('parent.id')))
>
> metadata.create_all(engine)
>
> engine.execute(parent_table.insert(), [{'id':1}, {'id':2}, {'id':3},
> {'id':4}])
> engine.execute(child_table.insert(), [{'parent_id':1}, {'parent_id':
> 2}, {'parent_id':2}, {'parent_id':2}])
>
> ## map_2.py
> engine = create_engine("mysql://user:passw...@localhost/mydb")
> metadata = MetaData(engine)
>
> parent_table = Table("parent", metadata, autoload=True)
> child_table = Table("child", metadata, autoload=True)
>
> class Parent(object):
>pass
> class Child(object):
>pass
>
> mapper(Parent, parent_table, properties={'children':
> relationship(Child)})
> mapper(Child, child_table)
>
> Session = sessionmaker()
> Session.configure(bind=engine)
> sess = Session()
> res = sess.query(Parent).all()
> print res[0].children
>
> Everything works fine for map_1.py, but when I run map_2.py I get the
> following error:
>
> sqlalchemy.exc.ArgumentError: Could not determine join condition
> between parent/child tables on relationship Parent.children. Specify
> a 'primaryjoin' expression. If this is a many-to-many relationship,
> 'secondaryjoin' is needed as well.
> Process terminated with an exit code of 1
>
> Can anyone point me out what I am doing wrong? Thanks in advance!
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
