On May 17, 2012, at 12:02 PM, Demitri Muna wrote:
> Hi,
>
> I'd like to revive this thread from a little while back as I'm still having
> problems. Thanks again to Michael for the help.
>
> In short, I'm having problems with SQLAlchemy determining the foreign key
> relationship between two tables in two different schemas. For example, this
>
> VisitSpectrum.catalogObject = relation(CatalogObject, backref="visitSpectra")
>
> gives me the error:
>
> ArgumentError: Could not determine join condition between parent/child tables
> on relationship VisitSpectrum.fitsHeaderValues. Specify a 'primaryjoin'
> expression. If 'secondary' is present, 'secondaryjoin' is needed as well.
>
> while this
>
> CatalogObject.visitSpectra = relation(VisitSpectrum, backref="catalogObject")
>
> works. I am using SQLAlchemy 0.7.7 and Python 2.7.
they are equivalent and I'd consider here that perhaps in the second case you
aren't actually testing the mapping ? The error is only raised once mappers
go through the deferred "configure" step, which is the first time you use the
mappings.
A bug report for SQLA would look like this:
t1 = Table("table1", metadata, schema="schema1", autoload=True,
autoload_with=some_engine)
t2 = Table("table2", metadata, schema="schema2", autoload=True,
autoload_with=some_engine)
assert t1.c.some_column.references(t2.c.some_other_column)
that is, "some_column" is expected to have a ForeignKey to "some_other_column"
based on the reflection.
Please provide that including the raw "CREATE TABLE" statements, removing any
extraneous columns and data not needed to reproduce the issue.
>
> I would have thought that these two lines should be functionally identical.
> SA is able to determine the relationship one direction, so the information is
> certainly there. The problem has to do, as Michael noted, with the schema
> search path, but even so I don't see where the ambiguity lies.
>
> I've removed the "public" schema from my database, and in my Python model
> classes where I always explicitly set the "schema" value in __table_args__ to
> avoid any ambiguity. The tables are each in a different schema:
>
> apogeedb.VisitSpectrum
> catalogdb.CatalogObject
>
> When the search path is this:
>
> catalogdb, apogeedb
>
> this works:
>
> VisitSpectrum.catalogObject = relation(CatalogObject, backref="visitSpectra")
>
> When the search path is
>
> apogeedb, catalogdb
>
> this works:
>
> CatalogObject.visitSpectra = relation(VisitSpectrum, backref="catalogObject")
>
> Flipping any of these gives the error above. No other schema (there are two
> more) contain tables with these names. This still strikes me as a bug
> somewhere. Any thoughts?
>
> Michael's second suggestion (below) of not explicitly specifying the schema
> won't work in my case as I have a few schemas that do have overlapping names
> (hence, the schema separation).
>
> Cheers,
> Demitri
>
> ---
>
> On Nov 4, 2011, at 5:25 PM, Michael Bayer wrote:
>
>> I think the important thing here is that the table definition on the Python
>> side needs to represent the table in the same way that the foreign key def
>> will represent it from PG.
>>
>> It's based on this fact:
>>
>>> I was reading the descriptions from PGAdmin3 - apparently they drop the
>>> schema in the display when the table is on the search path. There's nothing
>>> that I can do to further put the table in the schema, as it were.
>>
>>
>> So SQLA does this:
>>
>> 1. reflect boss.spectrum_header
>> 2. see that boss.spectrum_header has a foreign key - to a table called
>> "survey". No schema is given for this FK def. So SQLAlchemy creates a new
>> table called "survey" in the metadata collection. The schema is "None".
>> 3. SQLAlchemy then reflects "survey", all its columns. PG's liberal search
>> path allows this to work without issue.
>> 4. The application then goes to reflect what is, from SQLAlchemy's
>> perspective, an entirely different table called platedb.survey. Populates
>> that table with things too.
>> 5. The MetaData now has three tables: boss.spectrum_header, platedb.survey,
>> survey. Errors ensue since boss.spectrum_header points to "survey" and not
>> "platedb.survey".
>>
>> Solutions:
>>
>> 1. set the search path to be only "public" for the application's connection.
>> Use explicit schema names for all constructs outside of "public". A
>> connection event that emits "SET search_path TO public" on each new
>> connection will achieve this without affecting the database outside of the
>> app.
>>
>> 2. Leave the liberal search path in place. Remove the usage of "schema"
>> within the SQLAlchemy application and let PG's liberal search path find
>> things.
>
>
> --
> 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.
>
--
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.
