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'.
Is there another step required to detach the object, or is another
approach necessary to handle multiple dbs?
--
Dr Paul-Michael Agapow: VieDigitale / Inst. for Animal Health
[EMAIL PROTECTED] / [EMAIL PROTECTED]
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
