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. 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.
