On Aug 16, 2010, at 4:56 PM, Eric N wrote:
This problem involves Pylons as well, but I believe the problem
involves how I'm using SQLAlchemy and therfore I'm posting here first.
I have a peculiar situation where I am trying to dynamically flip back
and forth between data models and running into problems. I am using
PostgreSQL with multiple identically named tables in different schemas
with a single master table in the public schema with the tables linked
by a serial foreign key.
Here are the basics:
public.master_table.master_serial = producta.sub_table.master_serial
public.master_table.master_serial = productb.sub_table.master_serial
All tables are defined using the declarative model and use
__table_args__ = {'schema':'schema name'}. I have tried importing the
master_table into the __init__ functions of the separate product
models and leaving it static but just re-mapping the foreign key to
the product each time I switch products. Neither method works.
Inside of my processing code I call function set_product_model which
does a simple assignment operation:
model.master_table = producta.master_table
model.sub_table = producta.sub_table
Everything works beautifully until I try to go back and use a product
that has already been processed. So in a sequence of:
1) process producta file
2) process productb file
3) process productc file
4) process producta file
step 4 fails when I try to append the sub_table object to the
master_table object with an AssertionError(Attribute sub_table on
class MasterTable doesn't handle objects of type class SubTable
If re-mapping means you're trying to move ForeignKeys at runtime, that's not
the intended use case for table metadata, nor for class mappings- table
metadata and class mappings should be considered to be immutable (just like you
wouldnt issue ALTER TABLE statements dynamically in order to get the same SQL
statement to have a different effect - its hardly efficient or safe for
concurrent access).
A very simple way to deal with arbitrary tables backing a single class is at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName , whereby you can
generate classes for the sub-schemas automatically. A little extra code can
add the ForeignKey back to the master table, as well as any appropriate
relationship()s you're looking for. There is no switching of classes,
mappers, or tables at runtime, the static datamodel can read and write to any
of those schemas as needed.
If OTOH your application needs to only talk to one sub-schema at a time, then
you'd just configure the Session to talk to one schema or another, work with
it, then close it. Again, table metadata and/or mapper mutation is not used.
You'd do this by calling SET search_path on the Session at the start of
each transaction. http://www.postgresql.org/docs/8.2/static/ddl-schemas.html
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
