Simple relationship 1:1 fails with "Foreign key assocated with column ------- could not find table"
Hector Blanco
I am still dealing with the relationship I asked before
(http://groups.google.com/group/sqlalchemy/browse_thread/thread/c1d46daf35116999).
To tell the truth, I'm not even sure if this is a question I should
ask in the SqlAlchemy forum because I'm also dealing with Megrok.rdb
(http://pypi.python.org/pypi/megrok.rdb) and I don't know if that's
what is causing the problem, but I'm pretty lost... As I explained in
my other question, I think the rdb.Model thing that appears in the
classes is just a tool to create the mapper "class <--> table" in a
slightly more transparent way for the programmer. That's why I thought
I may get some help here.
In this message, I have simplified the code (compared to my former
question) to make it clearer, but well... The fact is that now I'm
getting a problem with a simple 1:1 relationship (if I can fix it, I
will be able to move to the more complicated stuff as I detailed in
the former question)
I am getting this error:
Foreign key assocated with column 'children_table.id' could not find
table 'parents_table' with which to generate a foreign key to target
column 'id'
I have a file, called Tables.py where all the classes and auxiliary
(or intermediate) tables that I'm going to use in my application are
defined:
Tables.py >>
class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("parents_table")
id = Column("id", Integer, primary_key=True)
_whateverField= Column("whatever_field", String(16)) #Irrelevant
child1 = relationship("Child", uselist=False)
class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("children_table")
id = Column("id", Integer, ForeignKey(Parent.id), primary_key = True)
type = Column("type", String(2)) #Irrelevant (for this example)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
And then I have two different Python .py files (Parent.py and
Child.py) where the methods that manage said classes are implemented.
In those files, the static area of each class is copied from Tables.py
with some changes in the quotes (where I can use the object itself, I
use it):
Parent.py >>
from child import Child
metadata = rdb.MetaData()
class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("parents_table")
id = Column("id", Integer, primary_key=True)
_whateverField= Column("whatever_field", String(16)) #Irrelevant
child1 = relationship(Child, uselist=False) #No quotation marks on this Child
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
And
Child.py > >
metadata = rdb.MetaData()
class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("children_table")
id = Column("id", Integer, ForeignKey("parent_table.id"), primary_key = True)
type = Column("type", String(2)) #Irrelevant (for this example)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
When I try to use these classes, I get:
Foreign key assocated with column 'children_table.id' could not find
table 'parents_table' with which to generate a foreign key to target
column 'id'
But if I take a look to the tables with a MySQL Query Browser, the
table "parents_table" is there, happily and properly created.
In some other places, I have had similar problems, but I've been able
to fix them by delaying the imports. I had been able to (kind of)
import the Parent type in the Child file so I can use the Parent
object directly. It would be a little bit as if in this case I was
able to do:
from parent import Parent
[ . . . ]
class Child(rdb.Model):
[ . . . ]
id = Column("id", Integer, ForeignKey(Parent.id), primary_key = True)
and that usually fixed the problem but in this specific case, I can't
really do that: In the Parent file I need to import the Child and that
gives a very, very nasty circular dependency problem.
Is there a way to tell the Child.py file something like "Hey, dude...
Here's the parent_table that you need!" ? (Well... In a more Pythonic
way, of course... I don't think 'dude'is a reserved keywork in Python,
or an SqlAlchemy type). I don't know, something like:
from whatever.repository.of.tables import parent_table
so I can, without quotes, use:
id = Column("id", Integer, ForeignKey(parent_table.id), primary_key = True)
(I guess that may work)
Thank you all.
Conor
The target of a ForeignKey should be a string, e.g.:
ForeignKey("parents_table.id")
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
And then I have two different Python .py files (Parent.py and
Child.py) where the methods that manage said classes are implemented.
In those files, the static area of each class is copied from Tables.py
with some changes in the quotes (where I can use the object itself, I
use it):
Parent.py >>
from child import Child
metadata = rdb.MetaData()
class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("parents_table")
id = Column("id", Integer, primary_key=True)
_whateverField= Column("whatever_field", String(16)) #Irrelevant
child1 = relationship(Child, uselist=False) #No quotation marks on this Child
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
And
Child.py > >
metadata = rdb.MetaData()
class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("children_table")
id = Column("id", Integer, ForeignKey("parent_table.id"), primary_key = True)
type = Column("type", String(2)) #Irrelevant (for this example)
These class definitions should be merged with those in Tables.py.
You should only have one "class Parent" statement and one "class
Child" statement. You may be confusing this with the
non-declarative class setup, where you define the table first,
class 2nd, and mapper 3rd. It looks like rdb uses the declarative
approach, where the table and mapper are defined as part of the
class in one step.
Also, it is a good idea to make the first argument to
relationship() a string, as it lets you avoid worrying about which
order classes are defined. Example:
# This works even if Child hasn't been defined yet.
child1 = relationship("Child", uselist=False)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - When I try to use these classes, I get: Foreign key assocated with column 'children_table.id' could not find table 'parents_table' with which to generate a foreign key to target column 'id'
This is probably due to the foreign key issue above.
But if I take a look to the tables with a MySQL Query Browser, the
table "parents_table" is there, happily and properly created.
In some other places, I have had similar problems, but I've been able
to fix them by delaying the imports. I had been able to (kind of)
import the Parent type in the Child file so I can use the Parent
object directly. It would be a little bit as if in this case I was
able to do:
from parent import Parent
[ . . . ]
class Child(rdb.Model):
[ . . . ]
id = Column("id", Integer, ForeignKey(Parent.id), primary_key = True)
and that usually fixed the problem but in this specific case, I can't
really do that: In the Parent file I need to import the Child and that
gives a very, very nasty circular dependency problem.
Is there a way to tell the Child.py file something like "Hey, dude...
Here's the parent_table that you need!" ? (Well... In a more Pythonic
way, of course... I don't think 'dude'is a reserved keywork in Python,
or an SqlAlchemy type). I don't know, something like:
from whatever.repository.of.tables import parent_table
so I can, without quotes, use:
id = Column("id", Integer, ForeignKey(parent_table.id), primary_key = True)
(I guess that may work)
Thank you all.
In SQLAlchemy you get around circular dependencies by:
- Using strings as the target of ForeignKey()
- Using class name strings as the target of a relation (declarative only)
- Using strings or callables as primaryjoin/secondaryjoin
arguments in a relationship()
-Conor
Hector Blanco
know the results.
I kind of suspected that having the classes defined in two different
places was making the whole thing go nuts, but that's the way I found
it (and I was trying to keep it consistent with what was there) but I
don't think that under any concept re-writing code is a good idea so
I'll try to change it.
Thank you for the very well explained and detailed reply.
2010/10/29 Conor <conor.edw...@gmail.com>:
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
Hector Blanco
I think I got it!
Investigating the "metadata" instance (printing the contents of
metadata.__dict__), I realized that in it appear references to the
tables although not as "table_name" but "schema_name.table_name".
Let's say my "schema" (in MySQL terminology) is called "test":
The following line:
id = Column("id", Integer, ForeignKey("parent_table.id"), primary_key = True)
fails, but this:
id = Column("id", Integer, ForeignKey("test.parent_table.id"),
primary_key = True)
works fine.
If I do:
for fieldKey, fieldVal in metadata.__dict__.iteritems():
print("::engine_created > field metadata." + str(fieldKey) +
"== " + str(fieldVal))
I get (among other things) this:
::engine_created > field metadata.tables == {'test.children_table':
Table('children_table', MetaData(None), Column('id', Integer(),
ForeignKey('test.parents_table.id'), table=<children_table>,
primary_key=True, nullable=False), Column('type', String(length=2,
convert_unicode=False, assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False), table=<children_table>), schema='test'),
'test.parents_table': Table('parents_table', MetaData(None),
Column('id', Integer(), table=<parents_table>, primary_key=True,
nullable=False), Column('whatever_field', String(length=16,
convert_unicode=False, assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False), table=<parents_table>), schema='test')}
As you can see, the parent table appears as "test.parents_table", not
"parents_table".
I don't think this was SqlAlchemy "fault", but more the megrok.rdb
thing that I'm using, but well... Maybe this will help someone else.
2010/10/29 Conor <conor.edw...@gmail.com>:
Hector Blanco
>
> These class definitions should be merged with those in Tables.py. You should
> only have one "class Parent" statement and one "class Child" statement. You
> may be confusing this with the non-declarative class setup, where you define
> the table first, class 2nd, and mapper 3rd. It looks like rdb uses the
> declarative approach, where the table and mapper are defined as part of the
> class in one step.
You were right again. I was creating a new instance of rdb.metadata()
in every class, and that's wrong (let me rephrase it... I THINK its
wrong). If my understanding is correct, that "metadata" thing is what
keeps track of the mapping (what classes are mapped to what tables,
etcetera). It actually needs to be global.