Are you using auto-incrementing primary keys?
I would bet that after the first flush(), your mapped object now has a
primary key, and so looks persistent.
Perhaps try a copy.copy() on the mapped object before the first flush, write
one copy to the first database, and the copy to the second, unless Mike has
a better idea.
Rick
On Jan 17, 2008 6:40 PM, Paul-Michael Agapow <[EMAIL PROTECTED]>
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'.
>
> 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 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
-~----------~----~----~----~------~----~------~--~---
