[sqlalchemy] sqlalchemy failing to commit somewhere (but fails silently) when trying to update data
Like all CRUD goes, I need to write some data to a table. when I write new
data to the table, everything works like charm. the problem starts when I
need to write data already existing in the table (actually updating some
data with the same primary key).
the data just doesn't seem to be written to the table! I started with
trying to update the data with session.merge(), but later tried a more
brute force approach, of querying for the same primary_key in the table,
deleting it and the adding and flushing the changed objects.
some where, if the basic add and flush failes the rest doesn't work. I'll
be glad for a clue here.
The code:
def flush(obj_Instance, id):
taking care of the sqlalchemy flushing
params:
Instance: an object Instance to flush into
id: the unique object instance id
DBSession2.add(obj_Instance)
try:
try:
DBSession2.flush()
print (flushed:, str(obj_Instance))
except (FlushError, IntegrityError) as err:
DBSession2.rollback()
if ('conflicts with persistent instance' in str(err)) or
('Duplicate key was ignored' in str(err)):
transaction.begin()
#my original slick take:
DBSession2.merge(obj_instance) # but after it failed to
update correctly I changed to a more brute force approach
#DBSession2.flush() #to save the merge
#from here on trying to brute force it
#saving for further reference - another try
newInstance = deepcopy(obj_Instance)
print (deleting: %s % id)
DBSession2.query(type(obj_Instance)).filter_by(ids =
id).delete()
DBSession2.flush() #at this point, I was so desperate for
this to work I literated the code with flush commands.
DBSession2.add(newInstance)
DBSession2.flush()
return
else:
raise #handling the case of the same key problem isn't the
source of conflicts
except Exception as err: # supposed to find out the error type and
message
# the code doesn't get here, only in real exceptions it was planned to
catch, 3 rows in 10,000 uploaded to the db
#TODO: make this less general and more specific
print str(err)
write_log(num=id, msg=some sql or sqlalchemy error use num %s as
id identifier with object: %s % (id, obj_Instance.name), timestamp=
datetime.now(), errtype=sql error, log=str(err))
DBSession2.rollback()
transaction.begin()
maybe this try/fail/rollback/merge or delete/insert new pattern is wrong
(also I think pythonic - try and ask forgiveness,but that would be for
mike to judge)
using sqlalchemy 0.7.3 vs mssql 2005 with pyodbc 2.1.11 and tg 2.1 (the
transaction manager comes with tg and I think is based transaction)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] sqlalchemy failing to commit somewhere (but fails silently) when trying to update data
one thing to note is that deepcopy() is not going to work. It will copy
SQLAlchemy's own accounting information on the object as well and generally
cause confusion.
The easiest way to insert a lot of data while detecting dupes efficiently is to
sort the data, then chunk through it, and for each chunk, pre-load from the
database all those records which reside within the range of that chunk of
pending data. You put those pre-loaded records in a dictionary and check it
for each record.
A simple system I use very often is (this isn't chunked, but could be):
recs = dict(session.query(MyThing.id, MyThing))
for i, newrec in enumerate(my_incoming_stuff):
if newrec['id'] in recs:
rec = recs[newrec['id']]
rec.data = newrec['data']
else:
rec = MyThing(id=newrec['id'], data=newrec['data'])
session.add(rec)
if i % 1000 == 0:
session.flush()
session.commit()
On Mar 18, 2013, at 1:54 AM, alonn alonis...@gmail.com wrote:
Like all CRUD goes, I need to write some data to a table. when I write new
data to the table, everything works like charm. the problem starts when I
need to write data already existing in the table (actually updating some data
with the same primary key).
the data just doesn't seem to be written to the table! I started with trying
to update the data with session.merge(), but later tried a more brute force
approach, of querying for the same primary_key in the table, deleting it and
the adding and flushing the changed objects.
some where, if the basic add and flush failes the rest doesn't work. I'll be
glad for a clue here.
The code:
def flush(obj_Instance, id):
taking care of the sqlalchemy flushing
params:
Instance: an object Instance to flush into
id: the unique object instance id
DBSession2.add(obj_Instance)
try:
try:
DBSession2.flush()
print (flushed:, str(obj_Instance))
except (FlushError, IntegrityError) as err:
DBSession2.rollback()
if ('conflicts with persistent instance' in str(err)) or
('Duplicate key was ignored' in str(err)):
transaction.begin()
#my original slick take:
DBSession2.merge(obj_instance) # but after it failed to
update correctly I changed to a more brute force approach
#DBSession2.flush() #to save the merge
#from here on trying to brute force it
#saving for further reference - another try
newInstance = deepcopy(obj_Instance)
print (deleting: %s % id)
DBSession2.query(type(obj_Instance)).filter_by(ids =
id).delete()
DBSession2.flush() #at this point, I was so desperate for
this to work I literated the code with flush commands.
DBSession2.add(newInstance)
DBSession2.flush()
return
else:
raise #handling the case of the same key problem isn't the
source of conflicts
except Exception as err: # supposed to find out the error type and
message
# the code doesn't get here, only in real exceptions it was planned to
catch, 3 rows in 10,000 uploaded to the db
#TODO: make this less general and more specific
print str(err)
write_log(num=id, msg=some sql or sqlalchemy error use num %s as id
identifier with object: %s % (id, obj_Instance.name), timestamp=
datetime.now(), errtype=sql error, log=str(err))
DBSession2.rollback()
transaction.begin()
maybe this try/fail/rollback/merge or delete/insert new pattern is wrong
(also I think pythonic - try and ask forgiveness,but that would be for mike
to judge)
using sqlalchemy 0.7.3 vs mssql 2005 with pyodbc 2.1.11 and tg 2.1 (the
transaction manager comes with tg and I think is based transaction)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] advices
On Mar 15, 2013, at 3:19 PM, Julien Cigar jci...@ulb.ac.be wrote:
On 03/14/2013 19:56, Michael Bayer wrote:
On Mar 12, 2013, at 5:13 AM, Julien Cigar jci...@ulb.ac.be wrote:
Hello,
I have written a CMS which is, among other, based on the joined load
inheritance feature of SQLAlchemy.
It is quite simple: the user is able to add objects in containers and
can select the default polymorphic loading for a container. In gross it
can dynamically select which tables will be joined. For that I'm using the
new orm.with_polymorphic() stuff of version 0.8.
It works well, and now I would also be able to let the user to set a
default ORDER BY, ... for a container.
Basically I would like to be able to select a default ORDER BY from the
orm.with_polymorphic() join above.
For the moment I'm using a bytea (I'm using PostgreSQL) column, named
default_order which is mapped in a PickleType on the SQLAlchemy side.
This column is just a serialized list which looks like:
[{'column': 'starts', 'polymorphic_identity': 5, 'weight': 1, 'order':
'desc'},
{'column': 'weight', 'polymorphic_identity': None, 'weight': 2, 'order':
'asc'},
{'column': 'last_update', 'polymorphic_identity': None, 'weight': 3,
'order': 'asc'}]
So it tells which column from which primary mapper should be used and in
which order.
I'm using the following code http://pastie.org/6459613 to transform this in
an ORDER BY clause.
Not all columns should be selectable, so I used something like this in my
mapped classes:
Content.__order__ = [Content.weight, Content.last_update]
Event.__order__ = [Event.starts, Event.ends]
File.__order__ = [File.file_size]
I need some advices on how would you do to maintain the consistency of
everything. By consistency I mean that I would like to avoid cases where
the user select a column that doesn't exist or is not allowed, a
polymorphic_identity that doesn't exist, maintain the synchronization
between the polymorphic loading of a container and the serialized list, etc
Would you create a custom type based on PickleType or a simple @validates()
or .. ?
well I'd stay away from PickleType like the plague and at least use a JSON
column instead.
just curious: why would you stay away from the PickleType?
In the future I would like to use the new JSON column type in PostgreSQL
9.1+, but for now we're still on 9.0.x and I thought a bytea + PickleType
would be better/faster than a TEXT + json.dumps() .. ?
you don't need PG's JSON type, just a text one like this one:
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#marshal-json-strings
why JSON and not pickle, basically would you prefer your dictionary of strings
to look like:
(dp0\nS'username'\np1\nS'some user'\np2\nsS'full name'\np3\nS'user b.
user'\np4\nsS'address'\np5\nS'123 anywhere street'\np6\ns.
or
'{username: some user, full name: user b. user, address: 123
anywhere street}'
?
plus would you prefer your data to have code injection vulnerabilities or not.
I can't answer the bigger question here since it's dependent on the flow of
your application. For example, i don't see how you're about to select
some rows, but then there's an object already loaded in memory where you can
pull out these various database-persisted attributes that describe other SQL
queries. Then @validates, that's used for persisting data, so I guess this
means users are persisting objects that describe SQL queries ? Sure, if
someone tries to say set_order_by('somecol'), and you want to check
Content.__order__, @validates seems the most expedient approach.But if
this is a GUI, typically you are not allowing invalid selections from the
GUI in the first place, so something would be scanning those __order__
collections ahead of time its kind of a highly specific application
design question.
Sorry to not have been more accurate on this. It's a webapp based on
CherryPy, the workflow is pretty simple: the user can select, through a
select, which mappers should be joined for a container, and it's saved in a:
'polymorphic_children' : orm.relationship(
ContentType,
secondary = table['folder_polymorphic_loading']
)
When the user selects mappers in the select box above, there is an ajax
call that retrieve which columns are available as possible default ORDER BY
clause for the container (this is the .__order__ stuff). This is saved in the
default_order property (bytea + PickleType)
Once everything is saved and that the user browse the container, those
entities are joined thanks to orm.with_polymorphic(). The webapp checks if
the default_order property is set (which is the one for which I use atm a
bytea + PickleType). So I have to maintain a kind of bidirectional check
between the polymorphic_children and default_order properties, to avoid that
I end's up with a query that use a column from a table which is not joined
(because, for
Re: [sqlalchemy] Error during roll back
that's an old bug that's been fixed. 0.7.1 is ancient you should upgrade to 0.7.10. On Mar 17, 2013, at 1:38 AM, Warwick Prince warwi...@mushroomsys.com wrote: Hi Michael I have some fairly basic code which is moving data from one DB to another. I have trapped errors on inserts just in case there were unexpected duplicates. When I go to commit this transaction, MySQL correctly throws an IntegrityError exception: Duplicate Entry which I trap, and perform a rollback. However, the rollback ALSO receives an IntegrityError exception from MySQL for the same insert query?? File C:\Documents and Settings\wprince\Desktop\PY CODE DEVELOPMENT\CESyncSQL\Sync_AU.py, line 1868, in SYNC_CustomersOUT session.rollback() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 586, in rollback self.transaction.rollback() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 324, in rollback self.close() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 351, in close self.session.begin() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 557, in begin self, nested=nested) File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 137, in __init__ self._take_snapshot() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 197, in _take_snapshot self.session.flush() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 1473, in flush self._flush(objects) File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 1542, in _flush flush_context.execute() File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line 327, in execute rec.execute(self) File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line 471, in execute uow File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line 2163, in _save_obj execute(statement, params) File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1358, in execute params) File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1491, in _execute_clauseelement compiled_sql, distilled_params File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1599, in _execute_context context) File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1592, in _execute_context context) File C:\Python27\lib\site-packages\sqlalchemy\engine\default.py, line 325, in do_execute cursor.execute(statement, parameters) File C:\Python27\lib\site-packages\mysql\connector\cursor.py, line 309, in execute res = self.db().protocol.cmd_query(stmt) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 136, in deco return func(*args, **kwargs) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 474, in cmd_query return self.handle_cmd_result(self._recv_packet()) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 173, in _recv_packet MySQLProtocol.raise_error(buf) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 167, in raise_error raise errors.get_mysql_exception(errno,errmsg) IntegrityError: (IntegrityError) 1062: Duplicate entry '1231-63565' for key 'userID' u'INSERT INTO customer_master bla.. Any ideas? This is 0.7.1 Cheers Warwick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Error during roll back
specifically it occurs when you receive an exception on flush(), but then you keep doing things that change the state of the session before calling rollback(). here's the original test: http://www.sqlalchemy.org/trac/attachment/ticket/2389/sqlalchemy_rollback_bug.py On Mar 17, 2013, at 1:38 AM, Warwick Prince warwi...@mushroomsys.com wrote: Hi Michael I have some fairly basic code which is moving data from one DB to another. I have trapped errors on inserts just in case there were unexpected duplicates. When I go to commit this transaction, MySQL correctly throws an IntegrityError exception: Duplicate Entry which I trap, and perform a rollback. However, the rollback ALSO receives an IntegrityError exception from MySQL for the same insert query?? File C:\Documents and Settings\wprince\Desktop\PY CODE DEVELOPMENT\CESyncSQL\Sync_AU.py, line 1868, in SYNC_CustomersOUT session.rollback() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 586, in rollback self.transaction.rollback() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 324, in rollback self.close() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 351, in close self.session.begin() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 557, in begin self, nested=nested) File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 137, in __init__ self._take_snapshot() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 197, in _take_snapshot self.session.flush() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 1473, in flush self._flush(objects) File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 1542, in _flush flush_context.execute() File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line 327, in execute rec.execute(self) File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line 471, in execute uow File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line 2163, in _save_obj execute(statement, params) File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1358, in execute params) File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1491, in _execute_clauseelement compiled_sql, distilled_params File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1599, in _execute_context context) File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1592, in _execute_context context) File C:\Python27\lib\site-packages\sqlalchemy\engine\default.py, line 325, in do_execute cursor.execute(statement, parameters) File C:\Python27\lib\site-packages\mysql\connector\cursor.py, line 309, in execute res = self.db().protocol.cmd_query(stmt) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 136, in deco return func(*args, **kwargs) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 474, in cmd_query return self.handle_cmd_result(self._recv_packet()) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 173, in _recv_packet MySQLProtocol.raise_error(buf) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 167, in raise_error raise errors.get_mysql_exception(errno,errmsg) IntegrityError: (IntegrityError) 1062: Duplicate entry '1231-63565' for key 'userID' u'INSERT INTO customer_master bla.. Any ideas? This is 0.7.1 Cheers Warwick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] column_property for correlated subquery
Hi, Using declarative here, and I'm trying to create a column_property with a correlated subquery that returns a count of records with a matching value in some other column. Here's what I've tried. Option 1 is the best, option 2 is ugly but second best, option 3 is not a good option since there are many other classes involved and the place where I'd need to put that code is far away from where it logically belongs. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import * from sqlalchemy.ext.declarative import declared_attr Base = declarative_base() option = 1 class Foo(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) bar_id = Column(Integer, ForeignKey(bar.id)) name = Column(String) if option == 1: # does not work (see first traceback below) @declared_attr def name_count(cls): clx = aliased(cls) return column_property( select(func.count([clx.id])) .where(clx.name == cls.name) .correlate(cls.__table__)) if option == 2: # does not work (see second traceback below) _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) class Bar(Base): __tablename__ = 'bar' id = Column(Integer, primary_key=True) name = Column(String) if option == 3: # works, but really not where I want to put this code _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) Option 1 traceback: Traceback (most recent call last): File temp/example.py, line 8, in module class Foo(Base): File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1348, in __init__ _as_declarative(cls, classname, cls.__dict__) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1181, in _as_declarative value = getattr(cls, k) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1554, in __get__ return desc.fget(cls) File temp/example.py, line 15, in name_count clx = aliased(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 385, in aliased return AliasedClass(element, alias=alias, name=name, adapt_on_names=adapt_on_names) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 298, in __init__ self.__mapper = _class_to_mapper(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 673, in _class_to_mapper raise exc.UnmappedClassError(class_or_mapper) sqlalchemy.orm.exc.UnmappedClassError: Class '__main__.Foo' is not mapped Option 2 traceback: Traceback (most recent call last): File temp/example.py, line 16, in module select([func.count(_foo.id)]) File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1229, in __call__ return func(*c, **o) File .../python2.7/site-packages/sqlalchemy/sql/functions.py, line 16, in __call__ args = [_literal_as_binds(c) for c in args] File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1440, in _literal_as_binds return element.__clause_element__() File .../python2.7/site-packages/sqlalchemy/orm/attributes.py, line 117, in __clause_element__ return self.comparator.__clause_element__() File .../python2.7/site-packages/sqlalchemy/util/langhelpers.py, line 506, in oneshot result = self.fget(obj, *args, **kw) File .../python2.7/site-packages/sqlalchemy/orm/properties.py, line 156, in __clause_element__ return self.adapter(self.prop.columns[0]) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 334, in __adapt_element return self.__adapter.traverse(elem).\ File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 185, in traverse return replacement_traverse(obj, self.__traverse_options__, replace) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 281, in replacement_traverse obj = clone(obj, **opts) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 270, in clone newelem = replace(elem) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 182, in replace e = v.replace(elem) File .../python2.7/site-packages/sqlalchemy/sql/util.py, line 720, in replace return self._corresponding_column(col, True) File .../python2.7/site-packages/sqlalchemy/sql/util.py, line 695, in _corresponding_column require_embedded=require_embedded) File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 2492, in corresponding_column if self.c.contains_column(column): File .../python2.7/site-packages/sqlalchemy/util/langhelpers.py, line 485, in __get__ obj.__dict__[self.__name__] = result = self.fget(obj) File
[sqlalchemy] Re: column_property for correlated subquery
Forgot to add, I'm on SA 0.7.8 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] column_property for correlated subquery
the typical hook for this kind of thing is __declare_last__, works after everything has been compiled. you might have an easier time also (or might not matter) if you build against the Table (i.e. Foo.__table__.alias(), etc.) instead of firing up the class with aliased(Foo), but if you're in __declare_last__ might not matter anyway. On Mar 18, 2013, at 12:20 PM, millerdev miller...@gmail.com wrote: Hi, Using declarative here, and I'm trying to create a column_property with a correlated subquery that returns a count of records with a matching value in some other column. Here's what I've tried. Option 1 is the best, option 2 is ugly but second best, option 3 is not a good option since there are many other classes involved and the place where I'd need to put that code is far away from where it logically belongs. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import * from sqlalchemy.ext.declarative import declared_attr Base = declarative_base() option = 1 class Foo(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) bar_id = Column(Integer, ForeignKey(bar.id)) name = Column(String) if option == 1: # does not work (see first traceback below) @declared_attr def name_count(cls): clx = aliased(cls) return column_property( select(func.count([clx.id])) .where(clx.name == cls.name) .correlate(cls.__table__)) if option == 2: # does not work (see second traceback below) _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) class Bar(Base): __tablename__ = 'bar' id = Column(Integer, primary_key=True) name = Column(String) if option == 3: # works, but really not where I want to put this code _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) Option 1 traceback: Traceback (most recent call last): File temp/example.py, line 8, in module class Foo(Base): File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1348, in __init__ _as_declarative(cls, classname, cls.__dict__) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1181, in _as_declarative value = getattr(cls, k) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1554, in __get__ return desc.fget(cls) File temp/example.py, line 15, in name_count clx = aliased(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 385, in aliased return AliasedClass(element, alias=alias, name=name, adapt_on_names=adapt_on_names) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 298, in __init__ self.__mapper = _class_to_mapper(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 673, in _class_to_mapper raise exc.UnmappedClassError(class_or_mapper) sqlalchemy.orm.exc.UnmappedClassError: Class '__main__.Foo' is not mapped Option 2 traceback: Traceback (most recent call last): File temp/example.py, line 16, in module select([func.count(_foo.id)]) File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1229, in __call__ return func(*c, **o) File .../python2.7/site-packages/sqlalchemy/sql/functions.py, line 16, in __call__ args = [_literal_as_binds(c) for c in args] File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1440, in _literal_as_binds return element.__clause_element__() File .../python2.7/site-packages/sqlalchemy/orm/attributes.py, line 117, in __clause_element__ return self.comparator.__clause_element__() File .../python2.7/site-packages/sqlalchemy/util/langhelpers.py, line 506, in oneshot result = self.fget(obj, *args, **kw) File .../python2.7/site-packages/sqlalchemy/orm/properties.py, line 156, in __clause_element__ return self.adapter(self.prop.columns[0]) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 334, in __adapt_element return self.__adapter.traverse(elem).\ File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 185, in traverse return replacement_traverse(obj, self.__traverse_options__, replace) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 281, in replacement_traverse obj = clone(obj, **opts) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 270, in clone newelem = replace(elem) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 182, in replace e = v.replace(elem) File
Re: [sqlalchemy] sqlalchemy failing to commit somewhere (but fails silently) when trying to update data
Thanks Michael for the good advice.
since I don't this chunking solution won't work for this specific use case
(The keys would be hard to sort) would't it be an easier solution just to
move transaction.commit() after each flush, so the DBSession.rollback()
wouldn't lose existing data in the session?
and another thing - is there a simple update contruct? or would
session.merge() do?
On Monday, March 18, 2013 5:37:34 PM UTC+2, Michael Bayer wrote:
one thing to note is that deepcopy() is not going to work. It will copy
SQLAlchemy's own accounting information on the object as well and generally
cause confusion.
The easiest way to insert a lot of data while detecting dupes efficiently
is to sort the data, then chunk through it, and for each chunk, pre-load
from the database all those records which reside within the range of that
chunk of pending data. You put those pre-loaded records in a dictionary
and check it for each record.
A simple system I use very often is (this isn't chunked, but could be):
recs = dict(session.query(MyThing.id, MyThing))
for i, newrec in enumerate(my_incoming_stuff):
if newrec['id'] in recs:
rec = recs[newrec['id']]
rec.data = newrec['data']
else:
rec = MyThing(id=newrec['id'], data=newrec['data'])
session.add(rec)
if i % 1000 == 0:
session.flush()
session.commit()
On Mar 18, 2013, at 1:54 AM, alonn alon...@gmail.com javascript:
wrote:
Like all CRUD goes, I need to write some data to a table. when I write new
data to the table, everything works like charm. the problem starts when I
need to write data already existing in the table (actually updating some
data with the same primary key).
the data just doesn't seem to be written to the table! I started with
trying to update the data with session.merge(), but later tried a more
brute force approach, of querying for the same primary_key in the table,
deleting it and the adding and flushing the changed objects.
some where, if the basic add and flush failes the rest doesn't work. I'll
be glad for a clue here.
The code:
def flush(obj_Instance, id):
taking care of the sqlalchemy flushing
params:
Instance: an object Instance to flush into
id: the unique object instance id
DBSession2.add(obj_Instance)
try:
try:
DBSession2.flush()
print (flushed:, str(obj_Instance))
except (FlushError, IntegrityError) as err:
DBSession2.rollback()
if ('conflicts with persistent instance' in str(err)) or
('Duplicate key was ignored' in str(err)):
transaction.begin()
#my original slick take:
DBSession2.merge(obj_instance) # but after it failed to
update correctly I changed to a more brute force approach
#DBSession2.flush() #to save the merge
#from here on trying to brute force it
#saving for further reference - another try
newInstance = deepcopy(obj_Instance)
print (deleting: %s % id)
DBSession2.query(type(obj_Instance)).filter_by(ids =
id).delete()
DBSession2.flush() #at this point, I was so desperate for
this to work I literated the code with flush commands.
DBSession2.add(newInstance)
DBSession2.flush()
return
else:
raise #handling the case of the same key problem isn't the
source of conflicts
except Exception as err: # supposed to find out the error type and
message
# the code doesn't get here, only in real exceptions it was planned to
catch, 3 rows in 10,000 uploaded to the db
#TODO: make this less general and more specific
print str(err)
write_log(num=id, msg=some sql or sqlalchemy error use num %s as
id identifier with object: %s % (id, obj_Instance.name), timestamp=
datetime.now(), errtype=sql error, log=str(err))
DBSession2.rollback()
transaction.begin()
maybe this try/fail/rollback/merge or delete/insert new pattern is wrong
(also I think pythonic - try and ask forgiveness,but that would be for
mike to judge)
using sqlalchemy 0.7.3 vs mssql 2005 with pyodbc 2.1.11 and tg 2.1 (the
transaction manager comes with tg and I think is based transaction)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the
Re: [sqlalchemy] sqlalchemy failing to commit somewhere (but fails silently) when trying to update data
if you want to go that approach I suggest you use begin_nested() which will
produce a SAVEPOINT, local to a certain scope within the transaction. you'll
have better results with 0.8 using this approach.
On Mar 18, 2013, at 1:54 PM, alonn alonis...@gmail.com wrote:
Thanks Michael for the good advice.
since I don't this chunking solution won't work for this specific use case
(The keys would be hard to sort) would't it be an easier solution just to
move transaction.commit() after each flush, so the DBSession.rollback()
wouldn't lose existing data in the session?
and another thing - is there a simple update contruct? or would
session.merge() do?
On Monday, March 18, 2013 5:37:34 PM UTC+2, Michael Bayer wrote:
one thing to note is that deepcopy() is not going to work. It will copy
SQLAlchemy's own accounting information on the object as well and generally
cause confusion.
The easiest way to insert a lot of data while detecting dupes efficiently is
to sort the data, then chunk through it, and for each chunk, pre-load from
the database all those records which reside within the range of that chunk of
pending data. You put those pre-loaded records in a dictionary and check it
for each record.
A simple system I use very often is (this isn't chunked, but could be):
recs = dict(session.query(MyThing.id, MyThing))
for i, newrec in enumerate(my_incoming_stuff):
if newrec['id'] in recs:
rec = recs[newrec['id']]
rec.data = newrec['data']
else:
rec = MyThing(id=newrec['id'], data=newrec['data'])
session.add(rec)
if i % 1000 == 0:
session.flush()
session.commit()
On Mar 18, 2013, at 1:54 AM, alonn alon...@gmail.com wrote:
Like all CRUD goes, I need to write some data to a table. when I write new
data to the table, everything works like charm. the problem starts when I
need to write data already existing in the table (actually updating some
data with the same primary key).
the data just doesn't seem to be written to the table! I started with trying
to update the data with session.merge(), but later tried a more brute force
approach, of querying for the same primary_key in the table, deleting it and
the adding and flushing the changed objects.
some where, if the basic add and flush failes the rest doesn't work. I'll
be glad for a clue here.
The code:
def flush(obj_Instance, id):
taking care of the sqlalchemy flushing
params:
Instance: an object Instance to flush into
id: the unique object instance id
DBSession2.add(obj_Instance)
try:
try:
DBSession2.flush()
print (flushed:, str(obj_Instance))
except (FlushError, IntegrityError) as err:
DBSession2.rollback()
if ('conflicts with persistent instance' in str(err)) or
('Duplicate key was ignored' in str(err)):
transaction.begin()
#my original slick take:
DBSession2.merge(obj_instance) # but after it failed to
update correctly I changed to a more brute force approach
#DBSession2.flush() #to save the merge
#from here on trying to brute force it
#saving for further reference - another try
newInstance = deepcopy(obj_Instance)
print (deleting: %s % id)
DBSession2.query(type(obj_Instance)).filter_by(ids =
id).delete()
DBSession2.flush() #at this point, I was so desperate for
this to work I literated the code with flush commands.
DBSession2.add(newInstance)
DBSession2.flush()
return
else:
raise #handling the case of the same key problem isn't the
source of conflicts
except Exception as err: # supposed to find out the error type and
message
# the code doesn't get here, only in real exceptions it was planned to
catch, 3 rows in 10,000 uploaded to the db
#TODO: make this less general and more specific
print str(err)
write_log(num=id, msg=some sql or sqlalchemy error use num %s as id
identifier with object: %s % (id, obj_Instance.name), timestamp=
datetime.now(), errtype=sql error, log=str(err))
DBSession2.rollback()
transaction.begin()
maybe this try/fail/rollback/merge or delete/insert new pattern is wrong
(also I think pythonic - try and ask forgiveness,but that would be for mike
to judge)
using sqlalchemy 0.7.3 vs mssql 2005 with pyodbc 2.1.11 and tg 2.1 (the
transaction manager comes with tg and I think is based transaction)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com.
To post to this
[sqlalchemy] Advice: Best practice for working with an existing database
Hi all, I'm new to SQLAlchemy, and looking for some advice on how to approach working with an existing database. The database will not be managed by Python, and I will need to maintain whatever library I write to keep up with the occasional schema change. I am looking to write a more-or-less definitive database access layer for myself and others to use if and when we write applications in Python -- the company is not primarily a Python shop. What's my best approach for building this library? Should I use Declarative, or should I use classical mapping -- which would be better, easier to maintain, easier for others to use? Should I be writing out the classes, or should I rely on reflection? Speaking of reflection, I've been using it to analyze what SQLAlchemy thinks of the schema. For a given table created by reflection, I'm seeing _autoincrement_column, columns, foreign_keys, indexes, and primary_key -- are there other table attributes I should be inspecting? Thanks for your advice! ---Peter -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Error during roll back
Cool - Thanks. Upgrade on the way.. :-) Cheers Warwick specifically it occurs when you receive an exception on flush(), but then you keep doing things that change the state of the session before calling rollback(). here's the original test: http://www.sqlalchemy.org/trac/attachment/ticket/2389/sqlalchemy_rollback_bug.py On Mar 17, 2013, at 1:38 AM, Warwick Prince warwi...@mushroomsys.com wrote: Hi Michael I have some fairly basic code which is moving data from one DB to another. I have trapped errors on inserts just in case there were unexpected duplicates. When I go to commit this transaction, MySQL correctly throws an IntegrityError exception: Duplicate Entry which I trap, and perform a rollback. However, the rollback ALSO receives an IntegrityError exception from MySQL for the same insert query?? File C:\Documents and Settings\wprince\Desktop\PY CODE DEVELOPMENT\CESyncSQL\Sync_AU.py, line 1868, in SYNC_CustomersOUT session.rollback() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 586, in rollback self.transaction.rollback() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 324, in rollback self.close() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 351, in close self.session.begin() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 557, in begin self, nested=nested) File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 137, in __init__ self._take_snapshot() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 197, in _take_snapshot self.session.flush() File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 1473, in flush self._flush(objects) File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 1542, in _flush flush_context.execute() File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line 327, in execute rec.execute(self) File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line 471, in execute uow File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line 2163, in _save_obj execute(statement, params) File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1358, in execute params) File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1491, in _execute_clauseelement compiled_sql, distilled_params File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1599, in _execute_context context) File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1592, in _execute_context context) File C:\Python27\lib\site-packages\sqlalchemy\engine\default.py, line 325, in do_execute cursor.execute(statement, parameters) File C:\Python27\lib\site-packages\mysql\connector\cursor.py, line 309, in execute res = self.db().protocol.cmd_query(stmt) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 136, in deco return func(*args, **kwargs) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 474, in cmd_query return self.handle_cmd_result(self._recv_packet()) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 173, in _recv_packet MySQLProtocol.raise_error(buf) File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 167, in raise_error raise errors.get_mysql_exception(errno,errmsg) IntegrityError: (IntegrityError) 1062: Duplicate entry '1231-63565' for key 'userID' u'INSERT INTO customer_master bla.. Any ideas? This is 0.7.1 Cheers Warwick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit
[sqlalchemy] question about using SAVEPOINTS
from the docshttp://docs.sqlalchemy.org/en/latest/orm/session.html#using-savepoint : begin_nested()http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.Session.begin_nested may be called any number of times, which will issue a new SAVEPOINT with a unique identifier for each call.* For each begin_nested()http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.Session.begin_nested call, a corresponding rollback()http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.Session.rollback or commit()http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.Session.commit must be issued*. Lets say I call session.begin_nested() after each successfull add something like: for myModule in modules; session.add(myModule) session.begin_nested() try: session.flush() except: session.rollback() session.merge(myModule) session.begin_nested() transaction.commit() my question is would a transaction.commit() suffice to commit all of the saved SAVEPOINTS or am I suppose to call a commit on each and everyone of them (as implied in the docs) Thanks for the help -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: column_property for correlated subquery
If I understand the problem correctly your best shot would be using sqlalchemy magical `hybrid_property` , hybrid_method, etc. here: http://docs.sqlalchemy.org/ru/latest/orm/extensions/hybrid.html On Monday, March 18, 2013 9:20:15 PM UTC+2, millerdev wrote: Hi, Using declarative here, and I'm trying to create a column_property with a correlated subquery that returns a count of records with a matching value in some other column. Here's what I've tried. Option 1 is the best, option 2 is ugly but second best, option 3 is not a good option since there are many other classes involved and the place where I'd need to put that code is far away from where it logically belongs. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import * from sqlalchemy.ext.declarative import declared_attr Base = declarative_base() option = 1 class Foo(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) bar_id = Column(Integer, ForeignKey(bar.id)) name = Column(String) if option == 1: # does not work (see first traceback below) @declared_attr def name_count(cls): clx = aliased(cls) return column_property( select(func.count([clx.id])) .where(clx.name == cls.name) .correlate(cls.__table__)) if option == 2: # does not work (see second traceback below) _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) class Bar(Base): __tablename__ = 'bar' id = Column(Integer, primary_key=True) name = Column(String) if option == 3: # works, but really not where I want to put this code _foo = aliased(Foo) Foo.name_count = column_property( select([func.count(_foo.id)]) .where(_foo.name == Foo.name) .correlate(Foo.__table__)) Option 1 traceback: Traceback (most recent call last): File temp/example.py, line 8, in module class Foo(Base): File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1348, in __init__ _as_declarative(cls, classname, cls.__dict__) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1181, in _as_declarative value = getattr(cls, k) File .../python2.7/site-packages/sqlalchemy/ext/declarative.py, line 1554, in __get__ return desc.fget(cls) File temp/example.py, line 15, in name_count clx = aliased(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 385, in aliased return AliasedClass(element, alias=alias, name=name, adapt_on_names=adapt_on_names) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 298, in __init__ self.__mapper = _class_to_mapper(cls) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 673, in _class_to_mapper raise exc.UnmappedClassError(class_or_mapper) sqlalchemy.orm.exc.UnmappedClassError: Class '__main__.Foo' is not mapped Option 2 traceback: Traceback (most recent call last): File temp/example.py, line 16, in module select([func.count(_foo.id)]) File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1229, in __call__ return func(*c, **o) File .../python2.7/site-packages/sqlalchemy/sql/functions.py, line 16, in __call__ args = [_literal_as_binds(c) for c in args] File .../python2.7/site-packages/sqlalchemy/sql/expression.py, line 1440, in _literal_as_binds return element.__clause_element__() File .../python2.7/site-packages/sqlalchemy/orm/attributes.py, line 117, in __clause_element__ return self.comparator.__clause_element__() File .../python2.7/site-packages/sqlalchemy/util/langhelpers.py, line 506, in oneshot result = self.fget(obj, *args, **kw) File .../python2.7/site-packages/sqlalchemy/orm/properties.py, line 156, in __clause_element__ return self.adapter(self.prop.columns[0]) File .../python2.7/site-packages/sqlalchemy/orm/util.py, line 334, in __adapt_element return self.__adapter.traverse(elem).\ File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 185, in traverse return replacement_traverse(obj, self.__traverse_options__, replace) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 281, in replacement_traverse obj = clone(obj, **opts) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 270, in clone newelem = replace(elem) File .../python2.7/site-packages/sqlalchemy/sql/visitors.py, line 182, in replace e = v.replace(elem) File .../python2.7/site-packages/sqlalchemy/sql/util.py, line 720, in replace return self._corresponding_column(col, True) File .../python2.7/site-packages/sqlalchemy/sql/util.py, line 695, in
Re: [sqlalchemy] question about using SAVEPOINTS
On Mar 18, 2013, at 4:22 PM, alonn alonis...@gmail.com wrote: from the docs: begin_nested() may be called any number of times, which will issue a new SAVEPOINT with a unique identifier for each call. For each begin_nested() call, a corresponding rollback() or commit() must be issued. Lets say I call session.begin_nested() after each successfull add something like: for myModule in modules; session.add(myModule) session.begin_nested() try: session.flush() except: session.rollback() session.merge(myModule) session.begin_nested() transaction.commit() my question is would a transaction.commit() suffice to commit all of the saved SAVEPOINTS or am I suppose to call a commit on each and everyone of them (as implied in the docs) you call a commit for every nested that you dont roll back. you shouldn't call begin_nested in your except: there. the pattern is: for item in something: session.begin_nested() try: do something session.commit() except: session.rollback() session.commit() Thanks for the help -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
