On May 18, 2012, at 3:38 PM, Demitri Muna wrote: > Hi Michael, > > On May 18, 2012, at 9:19 AM, Michael Bayer wrote: > >> When using cross-schema reflection, you have the option of either using only >> "public" in your schema search path, or *not* schema-qualifying the tables. >> This is because when you have the alternate schemas in your search path, >> Postgresql does not tell SQLAlchemy about the schema name when it returns >> foreign key information - it returns just the tablename, columnnname, but >> not the schema. Therefore when you have your Table objects which schema >> names in them, SQLAlchemy can't match them up and instead makes another >> Table that you aren't seeing as the target of each cross-schema foreign key, >> which has no schema name. > > Thanks for the explanation. This unfortunately makes things difficult for me > as I have at least two schema that have nearly the same tables, the schema > model providing a clean separation of the data. Removing the schema from > search_path introduces ambiguities.
If you've created all your Table objects with an explicit "schema" definition, you shouldn't need anything in your search path - technically not even "public". If the issue is that your database connection defaults to a certain search path, change the search path just within your SQLAlchemy application to not include defaults. A "connect" event listener can achieve this. > Where would be the best place to try to find a solution? Is it psycopg2 > that's not returning the schema information? It's Postgresql's pg_catalog.pg_get_constraintdef() function. > The information is of course in the database, so it sounds like maybe the SQL > query to get the foreign keys could be updated to explicitly include the > schema? Consider that an application which *does* want to use a custom search path, and does *not* specify "schema" inside of each Table, would want SQLAlchemy to *disregard* the schema information from the foreign key. The behavior of PG here, omitting the schema information if that schema is in fact already in the search path, is quite natural. The choice here is simple - either your tablenames are unique across schemas, in which case you can use a composite search path to place them all into one namespace and you don't use "schema" inside of each Table, or they are not, in which case you should be schema-qualifying things and not have those schemas in your search path. The two approaches can't reasonably be mixed without complicating things. > Also, given the Python classes in my example code, how do I print out the > foreign keys from a table object? you'd see this in the table.constraints collection. -- 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.
