[sqlalchemy] Session.merge and multiple databases
Hi, I have a sqlite database A and a MySQL database B which share the same schema, where I want to synchronize the contents of certain tables in A with those in B at certain times. Except for the synchronizing step the contents on A's side don't change, while those on B's side might. I'm trying to do this using Session.merge, which works if the row doesn't exist yet in A, or does but hasn't changed in B. If it has changed in B, I get a ConcurrentModificationError when the merge is flushed. #Suppose this individual doesn't yet exist in A individual = session_B.query(Individual).filter(...).one() session_A.merge(individual) #This will work and add the individual session_A.commit() individual.firstname = 'test' session_B.commit() individual = session_B.query(Individual).filter(...).one() session_A.merge(individual) #This will throw the ConcurrentModificationError session_A.commit() The error is: Traceback (most recent call last): File stdin, line 1, in module File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ session.py, line 673, in commit self.transaction.commit() File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ session.py, line 378, in commit self._prepare_impl() File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ session.py, line 362, in _prepare_impl self.session.flush() File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ session.py, line 1356, in flush self._flush(objects) File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ session.py, line 1434, in _flush flush_context.execute() File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ unitofwork.py, line 261, in execute UOWExecutor().execute(self, tasks) File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ unitofwork.py, line 753, in execute self.execute_save_steps(trans, task) File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ unitofwork.py, line 768, in execute_save_steps self.save_objects(trans, task) File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ unitofwork.py, line 759, in save_objects task.mapper._save_obj(task.polymorphic_tosave_objects, trans) File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg \sqlalchemy\orm\ mapper.py, line 1401, in _save_obj raise exc.ConcurrentModificationError(Updated rowcount %d does not match nu mber of objects updated %d % (rows, len(update))) sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0 does not matc h number of objects updated 1 Am I trying to use Session.merge the wrong way? Kind regards, Raf Geens -- 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.
Re: [sqlalchemy] Session.merge and multiple databases
On Aug 25, 2010, at 10:50 AM, Raf Geens wrote:
Hi,
I have a sqlite database A and a MySQL database B which share the same
schema, where I want to synchronize the contents of certain tables in
A with those in B at certain times. Except for the synchronizing step
the contents on A's side don't change, while those on B's side might.
I'm trying to do this using Session.merge, which works if the row
doesn't exist yet in A, or does but hasn't changed in B. If it has
changed in B, I get a ConcurrentModificationError when the merge is
flushed.
can't reproduce in 0.5.6 nor in 0.6.3, so a full test script that reproduces
will be needed. Alternatively, you might want to look at your SQL output and
see what primary key is attempting to be updated:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
firstname = Column(String)
e1 = create_engine('sqlite://', echo=True)
e2 = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e1)
Base.metadata.create_all(e2)
session_B = sessionmaker(e1)()
session_A = sessionmaker(e2)()
individual = A(firstname='foo')
session_B.add(individual)
session_B.commit()
#Suppose this individual doesn't yet exist in A
individual = session_B.query(A).one()
session_A.merge(individual)
#This will work and add the individual
session_A.commit()
individual.firstname = 'test'
session_B.commit()
individual = session_B.query(A).one()
session_A.merge(individual)
#This will throw the ConcurrentModificationError
session_A.commit()
#Suppose this individual doesn't yet exist in A
individual = session_B.query(Individual).filter(...).one()
session_A.merge(individual)
#This will work and add the individual
session_A.commit()
individual.firstname = 'test'
session_B.commit()
individual = session_B.query(Individual).filter(...).one()
session_A.merge(individual)
#This will throw the ConcurrentModificationError
session_A.commit()
The error is:
Traceback (most recent call last):
File stdin, line 1, in module
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
session.py, line 673, in commit
self.transaction.commit()
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
session.py, line 378, in commit
self._prepare_impl()
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
session.py, line 362, in _prepare_impl
self.session.flush()
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
session.py, line 1356, in flush
self._flush(objects)
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
session.py, line 1434, in _flush
flush_context.execute()
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
unitofwork.py, line 261, in execute
UOWExecutor().execute(self, tasks)
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
unitofwork.py, line 753, in execute
self.execute_save_steps(trans, task)
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
unitofwork.py, line 768, in execute_save_steps
self.save_objects(trans, task)
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
unitofwork.py, line 759, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\orm\
mapper.py, line 1401, in _save_obj
raise exc.ConcurrentModificationError(Updated rowcount %d does
not match nu
mber of objects updated %d % (rows, len(update)))
sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0
does not matc
h number of objects updated 1
Am I trying to use Session.merge the wrong way?
Kind regards,
Raf Geens
--
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.
--
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.
Re: [sqlalchemy] Session.merge and multiple databases
On 25/08/2010 17:15, Michael Bayer wrote: On Aug 25, 2010, at 10:50 AM, Raf Geens wrote: Hi, I have a sqlite database A and a MySQL database B which share the same schema, where I want to synchronize the contents of certain tables in A with those in B at certain times. Except for the synchronizing step the contents on A's side don't change, while those on B's side might. I'm trying to do this using Session.merge, which works if the row doesn't exist yet in A, or does but hasn't changed in B. If it has changed in B, I get a ConcurrentModificationError when the merge is flushed. can't reproduce in 0.5.6 nor in 0.6.3, so a full test script that reproduces will be needed. Alternatively, you might want to look at your SQL output and see what primary key is attempting to be updated: Thanks for the quick reply. I've looked at the SQL output of the last commit and it appears to match on the correct primary key. However, the Individual has a version_id_col defined in the mapper, and it's trying to match on the wrong value there, which seems to cause the update to fail. I'll try to reproduce it in a full script. Raf -- 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.
Re: [sqlalchemy] Session.merge and multiple databases
On 25/08/2010 17:47, Raf Geens wrote:
On 25/08/2010 17:15, Michael Bayer wrote:
On Aug 25, 2010, at 10:50 AM, Raf Geens wrote:
Hi,
I have a sqlite database A and a MySQL database B which share the same
schema, where I want to synchronize the contents of certain tables in
A with those in B at certain times. Except for the synchronizing step
the contents on A's side don't change, while those on B's side might.
I'm trying to do this using Session.merge, which works if the row
doesn't exist yet in A, or does but hasn't changed in B. If it has
changed in B, I get a ConcurrentModificationError when the merge is
flushed.
can't reproduce in 0.5.6 nor in 0.6.3, so a full test script that
reproduces will be needed. Alternatively, you might want to look at
your SQL output and see what primary key is attempting to be updated:
Thanks for the quick reply. I've looked at the SQL output of the last
commit and it appears to match on the correct primary key. However,
the Individual has a version_id_col defined in the mapper, and it's
trying to match on the wrong value there, which seems to cause the
update to fail. I'll try to reproduce it in a full script.
Raf
This one reproduces it for me:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
stamp = Column(Integer, default = 0)
__mapper_args__ = {'version_id_col' : stamp}
firstname = Column(String)
e1 = create_engine('sqlite://', echo=True)
e2 = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e1)
Base.metadata.create_all(e2)
session_B = sessionmaker(e1)()
session_A = sessionmaker(e2)()
individual = A(firstname='foo')
session_B.add(individual)
session_B.commit()
#Suppose this individual doesn't yet exist in A
individual = session_B.query(A).one()
session_A.merge(individual)
#This will work and add the individual
session_A.commit()
individual.firstname = 'test'
session_B.commit()
individual = session_B.query(A).one()
session_A.merge(individual)
#This will throw the ConcurrentModificationError
session_A.commit()
--
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.
RE: [sqlalchemy] Session.merge and multiple databases
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Raf Geens Sent: 25 August 2010 16:48 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Session.merge and multiple databases On 25/08/2010 17:15, Michael Bayer wrote: On Aug 25, 2010, at 10:50 AM, Raf Geens wrote: Hi, I have a sqlite database A and a MySQL database B which share the same schema, where I want to synchronize the contents of certain tables in A with those in B at certain times. Except for the synchronizing step the contents on A's side don't change, while those on B's side might. I'm trying to do this using Session.merge, which works if the row doesn't exist yet in A, or does but hasn't changed in B. If it has changed in B, I get a ConcurrentModificationError when the merge is flushed. can't reproduce in 0.5.6 nor in 0.6.3, so a full test script that reproduces will be needed. Alternatively, you might want to look at your SQL output and see what primary key is attempting to be updated: Thanks for the quick reply. I've looked at the SQL output of the last commit and it appears to match on the correct primary key. However, the Individual has a version_id_col defined in the mapper, and it's trying to match on the wrong value there, which seems to cause the update to fail. I'll try to reproduce it in a full script. Raf The version_id_col is likely not to work - the whole point of the column is that SA adds the current version to the WHERE clause, and then checks to see if any rows were updated. If they were, the object was still at the same version that SA loaded from the database. If no rows were updated, it assumes it was because someone else modified the object and incremented the version number (hence the ConcurrentModificationError). SA increments the version number every time a change to the object is flushed to the database. So when your object is modified in B, the version number no longer matches the version in A and no rows match the criteria. I don't know how you fix this if you want to continue using the version_id_col feature - is there any chance that you could do without it (perhaps by implementing similar functionality in a SessionExtension which only gets attached to the primary session)? Simon -- 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.
Re: [sqlalchemy] Session.merge and multiple databases
On 25/08/2010 18:03, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Raf Geens Sent: 25 August 2010 16:48 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Session.merge and multiple databases On 25/08/2010 17:15, Michael Bayer wrote: On Aug 25, 2010, at 10:50 AM, Raf Geens wrote: Hi, I have a sqlite database A and a MySQL database B which share the same schema, where I want to synchronize the contents of certain tables in A with those in B at certain times. Except for the synchronizing step the contents on A's side don't change, while those on B's side might. I'm trying to do this using Session.merge, which works if the row doesn't exist yet in A, or does but hasn't changed in B. If it has changed in B, I get a ConcurrentModificationError when the merge is flushed. can't reproduce in 0.5.6 nor in 0.6.3, so a full test script that reproduces will be needed. Alternatively, you might want to look at your SQL output and see what primary key is attempting to be updated: Thanks for the quick reply. I've looked at the SQL output of the last commit and it appears to match on the correct primary key. However, the Individual has a version_id_col defined in the mapper, and it's trying to match on the wrong value there, which seems to cause the update to fail. I'll try to reproduce it in a full script. Raf The version_id_col is likely not to work - the whole point of the column is that SA adds the current version to the WHERE clause, and then checks to see if any rows were updated. If they were, the object was still at the same version that SA loaded from the database. If no rows were updated, it assumes it was because someone else modified the object and incremented the version number (hence the ConcurrentModificationError). SA increments the version number every time a change to the object is flushed to the database. So when your object is modified in B, the version number no longer matches the version in A and no rows match the criteria. I don't know how you fix this if you want to continue using the version_id_col feature - is there any chance that you could do without it (perhaps by implementing similar functionality in a SessionExtension which only gets attached to the primary session)? Simon I'll see if I can do without it. Thanks for the clarification. Raf -- 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.
