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