On Jan 17, 2008, at 6:40 PM, Paul-Michael Agapow wrote:
>
>
> I'm puzzling out how to connect to multiple databases that have the
> same tables (and the same table definitions). The use case is that
> data may be retrieved from two or more similar dbs and perhaps copied
> between them. Following earlier advice, I've used a single metadata
> and 'bind' in the session creation to indicate which engine. However
> there's a problem. Example code below::
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
>
> uri1 = # blah blah
> uri2 = # blah blah
>
> class Dummy (object):
> "simple mapped class"
> pass
>
> class DbConnection (object):
> "encapsulate engine & session"
> def __init__ (self, uri, dbschema):
> engine = create_engine (uri)
> Session = sessionmaker (bind=engine, autoflush=False,
> transactional=False)
> self._session = Session()
> dbschema.metadata.create_all (bind=engine,
> checkfirst=True)
>
> def __del__ (self):
> self._session.close()
>
>
> class DbSchema (object):
> "metadata & table defintions"
> def __init__ (self):
> self.metadata = MetaData()
> self.dummy_table = Table('dummy', self.metadata,
> Column('id', Integer, primary_key=True),
> Column('name', Unicode(40)),
> )
> mapper (Dummy, self.dummy_table)
>
> # define tables and connect to both dbs
> schema = DbSchema()
> conn1 = DbConnection (uri1, schema)
> conn2 = DbConnection (uri2, schema)
>
> # create object
> x = Dummy()
> x.name = u'testname'
>
> # save to one and detach object
> conn1._session.save_or_update (x)
> conn1._session.flush ()
> conn1._session.expunge (x)
>
> # save to two silently fails!
> conn2._session.save_or_update (x)
> conn2._session.flush ()
> conn2._session.expunge (x)
>
>
> The object saves obediently to the first db, but fails to save to the
> second (although tables are created correctly). The successful write
> is always the first one, regardless of the order of opening
> connections. It doesn't depend on how the primary key is defined:
> autogenerated or supplied. I therefore surmise this is because
> 'save_or_update' detects the object as already being persistent,
> despite the use of 'expunge'.
thats correct, an object has an "_instance_key" attribute which marks
it as "persistent".
> Is there another step required to detach the object, or is another
> approach necessary to handle multiple dbs?
the particular way youd like to handle "multiple dbs" here is that
youre trying to replicate a full set of operations between two of
them. we dont have a "recommended" way to approach this, other than
usually replication is handled at the database level, not the python
level, using the replication abilities of the database itself or
external tools designed for this purpose. But Im accustomed to
people rejecting that suggestion when this kind of thing comes up, so
heres some things you can try in python:
you can try putting objects into the session using session.merge(),
which creates a copy of the object in the session, leaving the given
object unchanged, where it could then be merged into a second
session. Another approach would be to manually remove the
"_instance_key" attribute before saving twice, but this can lead to
complexities when instances with relations to other instances are
manipulated (mostly because we dont currently "support" that use case
and havent created tests against it). Finally, you can wrap up the
full set of operations (i.e. the full sequence of create object,
populate, save) into a single callable which is called twice, being
passed two different sessions.
Another approach which is technically feasable but is not implemented
right now is to create a proxying engine (or really, just a proxying
Connection would do) which distributes all SQL calls to more than one
engine. If you were willing to write a little more code you could go
this route as well, binding just a single Session to your proxying
Connection which routes all "execute" calls to two or more embedded,
actual Connection objects. This would provide the most seamless
experience but would require a little more tinkering.
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
