[sqlalchemy] What is the proper way to return the deleted records synchronously?
I need to return the ID's of the deleted records on session.query().delete() I've found this for bulk deletes: def after_bulk_delete(session, query, query_context, result): affected_table = query_context.statement.froms[0] affected_rows = query_context.statement.execute().fetchall() sqlalchemy.event.listen(Session, after_bulk_delete, after_bulk_delete) but this is asynchronous as it uses events. Isn't there a parameter I can set to get the records as the result of the delete operation? -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Occasional IntegrityError when identifying model not by its ID
I'm afraid it didn't solve my problem.
Here is my updated method:
@classmethod
def get(cls, bar=None, baz=None, qux=None, **kwargs):
query = session.query(cls).\
filter(cls.bar == bar).\
filter(cls.baz == baz).\
filter(cls.qux == qux)
item = query.first()
updated = False
if not item:
try:
with session.begin_nested(): # run inside a SAVEPOINT
updated = True
item = cls(bar=bar, baz=baz, qux=qux, **kwargs)
session.add(item)
session.flush()
except sa.exc.IntegrityError:
item = query.first()
if not item:
raise Exception(invalidIntegrityError)
except:
raise
if not updated:
for k, v in kwargs.iteritems():
if getattr(item, k) != v:
setattr(item, k, v)
return item
With this code, i'm getting invalidIntegrityError. How is it possible?
(it's also worth pointing out that this solution requires SA 0.8.2
(otherwise, there is a problem with session.begin_nested)
On Tuesday, August 27, 2013 6:40:03 PM UTC+3, Michael Bayer wrote:
I'm not a fan of catching integrity errors, i prefer to try to make sure
they aren't going to happen, or if they are, they aren't a normal
occurrence and the system is such that the particular operation can just
fail (of course it depends on what it is). A problem with catching the
integrity error due to concurrent, conflicting operations is that depending
on backend and isolation level, you can't be totally sure when the error is
going to get raised (e.g. serializable isolation vs. non). Also on a
backend like Postgresql, the database can't recover the transaction after
an integrity error unless you used a savepoint.
But here you're doing the concurrent transactions need row identity X,
so maybe it is appropriate here. Here is a rough idea of a transactional
pattern for that, noting this isn't tested:
try:
my_object = Session.query(MyClass).filter().one()
except NoResultFound:
try:
with Session.begin_nested(): # run inside a SAVEPOINT
my_object = MyClass(...)
Session.add(my_object)
Session.flush()
except IntegrityError:
my_object = Session.query(MyClass).filter().one()
On Aug 27, 2013, at 11:13 AM, herzaso her...@gmail.com javascript:
wrote:
Suppose we are looking at a race condition, do you also think this should
be handled by catching the IntegrityError?
If so, what should I do? only flush and do the operation again?
On Tuesday, August 27, 2013 5:42:23 PM UTC+3, Michael Bayer wrote:
the word occasional is very meaningful. It usually suggests race
conditions.Then with the word tornado, the baysean filters are
strongly leaning towards race condition at that point :).
if an error is occurring only under volume then you have to revisit where
race conditions can occur.
On Aug 27, 2013, at 10:32 AM, herzaso her...@gmail.com wrote:
I'm running a Tornado server without redundancy (only one process,
requests can arrive at the same time but will be handled one at a time)
I do agree that for large volumes, catching the IntegrityError would be
better, but currently I am handling a single request at a time and I want
to fix this problem before I move on ...
On Tuesday, August 27, 2013 5:24:07 PM UTC+3, Simon King wrote:
On Tue, Aug 27, 2013 at 2:31 PM, herzaso her...@gmail.com wrote:
On Tuesday, August 27, 2013 3:55:50 PM UTC+3, Simon King wrote:
On Tue, Aug 27, 2013 at 1:40 PM, herzaso her...@gmail.com wrote:
I have a model with an ID column set as the primary key, though i'd
like
to
be able to identify records by 3 other columns.
For this situation, I've added a classmethod that will fetch the
record
if
found or a new record if not.
The problem i'm having is that every once in a while, I get
IntegrityError
trying to flush a change
class Foo(Base):
__table_args__ = (sa.UniqueConstraint('bar', 'baz', 'qux'),)
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' %
__tablename__),
nullable=False, primary_key=True)
bar = sa.Column(sa.BigInteger)
baz = sa.Column(sa.BigInteger)
qux = sa.Column(sa.BigInteger)
a1 = sa.Column(sa.BigInteger)
a2 = sa.Column(sa.BigInteger)
@classmethod
def get(cls, bar=None, baz=None, qux=None, **kwargs):
item = session.query(cls).\
filter(cls.bar== bar).\
filter(cls.baz == baz).\
filter(cls.qux == qux).\
first()
if item:
for k, v in kwargs.iteritems():
if getattr(item, k) != v
Re: [sqlalchemy] Occasional IntegrityError when identifying model not by its ID
I'm not sure what to make of the results: On the first connection, I ran BEGIN and INSERT and both were successful, but when I tried the INSERT statement on the second connection, I got ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. Running the same query on the first connection produced the required result which is ERROR 1062 (23000): Duplicate entry After the ROLLBACK on the first connection, the INSERT statement worked well on the second connection Regarding your second remark, the answer is yes, the error was due to the unique constraint on those columns BTW: I'm working on MySQL On Monday, September 2, 2013 1:31:12 PM UTC+3, Simon King wrote: I don't really know the answer, but I'd be interested in the results of this experiment: Forget about SQLAlchemy for the moment, and start 2 plain SQL connections to your database. In the first, type something like the following: BEGIN; INSERT foo(bar, baz, qux) VALUES(1, 1, 1); Now in the second connection do the same. I assume it'll fail because of the duplicate values. Now in the first connection issue a ROLLBACK. You should now be in a state where no matching row exists in the database, even though you received an error about constraint violations. The results you see may be different, depending on your transaction isolation level. (It may be that you don't get the constraint violation at all until you try to commit the second connection). Another thing you could look at: are you sure that the error you are getting is due to the unique constraint on bar/baz/qux, and not some other constraint in the database? Simon On Mon, Sep 2, 2013 at 8:45 AM, herzaso her...@gmail.com javascript: wrote: I'm afraid it didn't solve my problem. Here is my updated method: @classmethod def get(cls, bar=None, baz=None, qux=None, **kwargs): query = session.query(cls).\ filter(cls.bar == bar).\ filter(cls.baz == baz).\ filter(cls.qux == qux) item = query.first() updated = False if not item: try: with session.begin_nested(): # run inside a SAVEPOINT updated = True item = cls(bar=bar, baz=baz, qux=qux, **kwargs) session.add(item) session.flush() except sa.exc.IntegrityError: item = query.first() if not item: raise Exception(invalidIntegrityError) except: raise if not updated: for k, v in kwargs.iteritems(): if getattr(item, k) != v: setattr(item, k, v) return item With this code, i'm getting invalidIntegrityError. How is it possible? (it's also worth pointing out that this solution requires SA 0.8.2 (otherwise, there is a problem with session.begin_nested) On Tuesday, August 27, 2013 6:40:03 PM UTC+3, Michael Bayer wrote: I'm not a fan of catching integrity errors, i prefer to try to make sure they aren't going to happen, or if they are, they aren't a normal occurrence and the system is such that the particular operation can just fail (of course it depends on what it is). A problem with catching the integrity error due to concurrent, conflicting operations is that depending on backend and isolation level, you can't be totally sure when the error is going to get raised (e.g. serializable isolation vs. non). Also on a backend like Postgresql, the database can't recover the transaction after an integrity error unless you used a savepoint. But here you're doing the concurrent transactions need row identity X, so maybe it is appropriate here. Here is a rough idea of a transactional pattern for that, noting this isn't tested: try: my_object = Session.query(MyClass).filter().one() except NoResultFound: try: with Session.begin_nested(): # run inside a SAVEPOINT my_object = MyClass(...) Session.add(my_object) Session.flush() except IntegrityError: my_object = Session.query(MyClass).filter().one() On Aug 27, 2013, at 11:13 AM, herzaso her...@gmail.com wrote: Suppose we are looking at a race condition, do you also think this should be handled by catching the IntegrityError? If so, what should I do? only flush and do the operation again? On Tuesday, August 27, 2013 5:42:23 PM UTC+3, Michael Bayer wrote: the word occasional is very meaningful. It usually suggests race conditions.Then with the word tornado, the baysean filters are strongly leaning towards race condition at that point :). if an error is occurring only under
Re: [sqlalchemy] Occasional IntegrityError when identifying model not by its ID
I do have it set as REPEATABLE READ. However, I don't use transactions in sqlalchemy On Monday, September 2, 2013 3:08:58 PM UTC+3, Simon King wrote: Do you know what transaction isolation level you are running at? The default apparently is REPEATABLE READ: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html#isolevel_repeatable-read The important sentence in that link is: All consistent reads within the same transaction read the snapshot established by the first read When you query the database for the first time, to see if the entity already exists, you are setting that initial snapshot. If you run the same query again (such as in your exception handler), you will get the same results, whether or not another connection has inserted a matching row in the meantime. Simon On Mon, Sep 2, 2013 at 12:54 PM, herzaso her...@gmail.com javascript: wrote: I'm not sure what to make of the results: On the first connection, I ran BEGIN and INSERT and both were successful, but when I tried the INSERT statement on the second connection, I got ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. Running the same query on the first connection produced the required result which is ERROR 1062 (23000): Duplicate entry After the ROLLBACK on the first connection, the INSERT statement worked well on the second connection Regarding your second remark, the answer is yes, the error was due to the unique constraint on those columns BTW: I'm working on MySQL On Monday, September 2, 2013 1:31:12 PM UTC+3, Simon King wrote: I don't really know the answer, but I'd be interested in the results of this experiment: Forget about SQLAlchemy for the moment, and start 2 plain SQL connections to your database. In the first, type something like the following: BEGIN; INSERT foo(bar, baz, qux) VALUES(1, 1, 1); Now in the second connection do the same. I assume it'll fail because of the duplicate values. Now in the first connection issue a ROLLBACK. You should now be in a state where no matching row exists in the database, even though you received an error about constraint violations. The results you see may be different, depending on your transaction isolation level. (It may be that you don't get the constraint violation at all until you try to commit the second connection). Another thing you could look at: are you sure that the error you are getting is due to the unique constraint on bar/baz/qux, and not some other constraint in the database? Simon On Mon, Sep 2, 2013 at 8:45 AM, herzaso her...@gmail.com wrote: I'm afraid it didn't solve my problem. Here is my updated method: @classmethod def get(cls, bar=None, baz=None, qux=None, **kwargs): query = session.query(cls).\ filter(cls.bar == bar).\ filter(cls.baz == baz).\ filter(cls.qux == qux) item = query.first() updated = False if not item: try: with session.begin_nested(): # run inside a SAVEPOINT updated = True item = cls(bar=bar, baz=baz, qux=qux, **kwargs) session.add(item) session.flush() except sa.exc.IntegrityError: item = query.first() if not item: raise Exception(invalidIntegrityError) except: raise if not updated: for k, v in kwargs.iteritems(): if getattr(item, k) != v: setattr(item, k, v) return item With this code, i'm getting invalidIntegrityError. How is it possible? (it's also worth pointing out that this solution requires SA 0.8.2 (otherwise, there is a problem with session.begin_nested) On Tuesday, August 27, 2013 6:40:03 PM UTC+3, Michael Bayer wrote: I'm not a fan of catching integrity errors, i prefer to try to make sure they aren't going to happen, or if they are, they aren't a normal occurrence and the system is such that the particular operation can just fail (of course it depends on what it is). A problem with catching the integrity error due to concurrent, conflicting operations is that depending on backend and isolation level, you can't be totally sure when the error is going to get raised (e.g. serializable isolation vs. non). Also on a backend like Postgresql, the database can't recover the transaction after an integrity error unless you used a savepoint. But here you're doing the concurrent transactions need row identity X, so maybe it is appropriate here. Here
Re: [sqlalchemy] Occasional IntegrityError when identifying model not by its ID
I'm sorry, it was a misunderstanding on my part regarding the transactions. So what are you saying? that I should replace the transaction isolation level? On Monday, September 2, 2013 3:29:25 PM UTC+3, Simon King wrote: What exactly do you mean by not using transactions? The Session always works within a transaction: http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#managing-transactions I assume you are also using InnoDB tables. On Mon, Sep 2, 2013 at 1:19 PM, herzaso her...@gmail.com javascript: wrote: I do have it set as REPEATABLE READ. However, I don't use transactions in sqlalchemy On Monday, September 2, 2013 3:08:58 PM UTC+3, Simon King wrote: Do you know what transaction isolation level you are running at? The default apparently is REPEATABLE READ: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html#isolevel_repeatable-read The important sentence in that link is: All consistent reads within the same transaction read the snapshot established by the first read When you query the database for the first time, to see if the entity already exists, you are setting that initial snapshot. If you run the same query again (such as in your exception handler), you will get the same results, whether or not another connection has inserted a matching row in the meantime. Simon On Mon, Sep 2, 2013 at 12:54 PM, herzaso her...@gmail.com wrote: I'm not sure what to make of the results: On the first connection, I ran BEGIN and INSERT and both were successful, but when I tried the INSERT statement on the second connection, I got ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. Running the same query on the first connection produced the required result which is ERROR 1062 (23000): Duplicate entry After the ROLLBACK on the first connection, the INSERT statement worked well on the second connection Regarding your second remark, the answer is yes, the error was due to the unique constraint on those columns BTW: I'm working on MySQL On Monday, September 2, 2013 1:31:12 PM UTC+3, Simon King wrote: I don't really know the answer, but I'd be interested in the results of this experiment: Forget about SQLAlchemy for the moment, and start 2 plain SQL connections to your database. In the first, type something like the following: BEGIN; INSERT foo(bar, baz, qux) VALUES(1, 1, 1); Now in the second connection do the same. I assume it'll fail because of the duplicate values. Now in the first connection issue a ROLLBACK. You should now be in a state where no matching row exists in the database, even though you received an error about constraint violations. The results you see may be different, depending on your transaction isolation level. (It may be that you don't get the constraint violation at all until you try to commit the second connection). Another thing you could look at: are you sure that the error you are getting is due to the unique constraint on bar/baz/qux, and not some other constraint in the database? Simon On Mon, Sep 2, 2013 at 8:45 AM, herzaso her...@gmail.com wrote: I'm afraid it didn't solve my problem. Here is my updated method: @classmethod def get(cls, bar=None, baz=None, qux=None, **kwargs): query = session.query(cls).\ filter(cls.bar == bar).\ filter(cls.baz == baz).\ filter(cls.qux == qux) item = query.first() updated = False if not item: try: with session.begin_nested(): # run inside a SAVEPOINT updated = True item = cls(bar=bar, baz=baz, qux=qux, **kwargs) session.add(item) session.flush() except sa.exc.IntegrityError: item = query.first() if not item: raise Exception(invalidIntegrityError) except: raise if not updated: for k, v in kwargs.iteritems(): if getattr(item, k) != v: setattr(item, k, v) return item With this code, i'm getting invalidIntegrityError. How is it possible? (it's also worth pointing out that this solution requires SA 0.8.2 (otherwise, there is a problem with session.begin_nested) On Tuesday, August 27, 2013 6:40:03 PM UTC+3, Michael Bayer wrote: I'm not a fan of catching integrity errors, i prefer to try to make sure they aren't going to happen, or if they are, they aren't
[sqlalchemy] Re: checking script validity
Not quite related to sqlalchemy, but if you want a one-time check, check out sqlfiddle http://sqlfiddle.com/. On Friday, August 30, 2013 8:30:09 AM UTC+3, monosij...@gmail.com wrote: Hello - Not very familiar with sqlalchemy yet ... Is there a way to check if a script I have generated (not using sqlalchemy) is valid for a particular DBMS (Postgres, MySQL, etc.) It would be an ANSI SQL script and there is module ansisql but not able to find where I can check a script for validity against a particular DBMS (Postgres). I would need to validate a create table script as well index creation script and foreign key constraint script. Thank you. -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Calculate birthdays
I think you were right in the first place. He does want to leave the year out, hence the replace function ... Note to myself - understand the question before you answer ... On Wednesday, August 28, 2013 9:01:14 PM UTC+3, Jonathan Vanasco wrote: sorry, it looks like the OP did want people born on the current month/day/year combo. you should be able to wrap all the comparisons in a date like this : Member.query.filter( sqlalchemy.func.date(Member.dateofbirth) == '2013-08-27' ).all() Member.query.filter( sqlalchemy.func.date(Member.dateofbirth) == sqlalchemy.func.date(datetime.today()) ).all() -- 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. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Occasional IntegrityError when identifying model not by its ID
I have a model with an ID column set as the primary key, though i'd like to be
able to identify records by 3 other columns.
For this situation, I've added a classmethod that will fetch the record if
found or a new record if not.
The problem i'm having is that every once in a while, I get IntegrityError
trying to flush a change
class Foo(Base):
__table_args__ = (sa.UniqueConstraint('bar', 'baz', 'qux'),)
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
nullable=False, primary_key=True)
bar = sa.Column(sa.BigInteger)
baz = sa.Column(sa.BigInteger)
qux = sa.Column(sa.BigInteger)
a1 = sa.Column(sa.BigInteger)
a2 = sa.Column(sa.BigInteger)
@classmethod
def get(cls, bar=None, baz=None, qux=None, **kwargs):
item = session.query(cls).\
filter(cls.bar== bar).\
filter(cls.baz == baz).\
filter(cls.qux == qux).\
first()
if item:
for k, v in kwargs.iteritems():
if getattr(item, k) != v:
setattr(item, k, v)
else:
item = cls(bar=bar, baz=baz, qux=qux, **kwargs)
return item
This is the code I use to add/update records:
foo = Foo.get(**item)
session.merge(foo)
I'm struggling with this problem for some time now, and would appreciate any
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.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Occasional IntegrityError when identifying model not by its ID
Hi Simon,
Thanks for the fast reply.
I tried adding session.add(item) and session.flush() in the else clause in
the past but that didn't solve my problem.
I didn't however remove the merge, do you think that might be the problem?
Regarding the flush, this code is part of an API server where a
scoped_session is committed after each change. I haven't changed the
autoflush parameter, and as I understand the default value is True making a
flush before each commit or query.
As for the UniqueObject recipe, thanks! Amazing that I never found it
searching for a cure. As I see it basically does the same ...
I never managed to reproduce this bug on my development environment. It
only happens in my production environment.
Do you suppose adding a session.add and removing the merge will solve this
issue?
Thanks,
Ofir
On Tuesday, August 27, 2013 3:55:50 PM UTC+3, Simon King wrote:
On Tue, Aug 27, 2013 at 1:40 PM, herzaso her...@gmail.com javascript:
wrote:
I have a model with an ID column set as the primary key, though i'd like
to
be able to identify records by 3 other columns.
For this situation, I've added a classmethod that will fetch the record
if
found or a new record if not.
The problem i'm having is that every once in a while, I get
IntegrityError
trying to flush a change
class Foo(Base):
__table_args__ = (sa.UniqueConstraint('bar', 'baz', 'qux'),)
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__),
nullable=False, primary_key=True)
bar = sa.Column(sa.BigInteger)
baz = sa.Column(sa.BigInteger)
qux = sa.Column(sa.BigInteger)
a1 = sa.Column(sa.BigInteger)
a2 = sa.Column(sa.BigInteger)
@classmethod
def get(cls, bar=None, baz=None, qux=None, **kwargs):
item = session.query(cls).\
filter(cls.bar== bar).\
filter(cls.baz == baz).\
filter(cls.qux == qux).\
first()
if item:
for k, v in kwargs.iteritems():
if getattr(item, k) != v:
setattr(item, k, v)
else:
item = cls(bar=bar, baz=baz, qux=qux, **kwargs)
return item
This is the code I use to add/update records:
foo = Foo.get(**item)
session.merge(foo)
I'm struggling with this problem for some time now, and would appreciate
any
help ...
I'm not sure of the exact problem, but there are a couple of things
that you could investigate.
Firstly, session.merge returns a copy of the object, rather than
adding the object that you supplied into the session. See
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#merging for
details.
Secondly, your get method sometimes returns objects that are already
part of the session (if they were in the database), and sometimes
objects that are not in the session. It would probably be more
consistent to always return objects that are part of the session, by
putting session.add(item) in your else clause. This would get rid
of the need for session.merge(). (If you want to be able to use the
get with non-global sessions, pass the session as a parameter.)
Finally, if your session isn't auto-flushing, it would be possible for
you to call get twice with the same parameters and get 2 different
objects back.
You may want to look at the UniqueObject recipe in the wiki:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject
Hope that helps,
Simon
--
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.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Occasional IntegrityError when identifying model not by its ID
I'm running a Tornado server without redundancy (only one process, requests
can arrive at the same time but will be handled one at a time)
I do agree that for large volumes, catching the IntegrityError would be
better, but currently I am handling a single request at a time and I want
to fix this problem before I move on ...
On Tuesday, August 27, 2013 5:24:07 PM UTC+3, Simon King wrote:
On Tue, Aug 27, 2013 at 2:31 PM, herzaso her...@gmail.com javascript:
wrote:
On Tuesday, August 27, 2013 3:55:50 PM UTC+3, Simon King wrote:
On Tue, Aug 27, 2013 at 1:40 PM, herzaso her...@gmail.com wrote:
I have a model with an ID column set as the primary key, though i'd
like
to
be able to identify records by 3 other columns.
For this situation, I've added a classmethod that will fetch the
record
if
found or a new record if not.
The problem i'm having is that every once in a while, I get
IntegrityError
trying to flush a change
class Foo(Base):
__table_args__ = (sa.UniqueConstraint('bar', 'baz', 'qux'),)
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' %
__tablename__),
nullable=False, primary_key=True)
bar = sa.Column(sa.BigInteger)
baz = sa.Column(sa.BigInteger)
qux = sa.Column(sa.BigInteger)
a1 = sa.Column(sa.BigInteger)
a2 = sa.Column(sa.BigInteger)
@classmethod
def get(cls, bar=None, baz=None, qux=None, **kwargs):
item = session.query(cls).\
filter(cls.bar== bar).\
filter(cls.baz == baz).\
filter(cls.qux == qux).\
first()
if item:
for k, v in kwargs.iteritems():
if getattr(item, k) != v:
setattr(item, k, v)
else:
item = cls(bar=bar, baz=baz, qux=qux, **kwargs)
return item
This is the code I use to add/update records:
foo = Foo.get(**item)
session.merge(foo)
I'm struggling with this problem for some time now, and would
appreciate
any
help ...
I'm not sure of the exact problem, but there are a couple of things
that you could investigate.
Firstly, session.merge returns a copy of the object, rather than
adding the object that you supplied into the session. See
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#merging for
details.
Secondly, your get method sometimes returns objects that are already
part of the session (if they were in the database), and sometimes
objects that are not in the session. It would probably be more
consistent to always return objects that are part of the session, by
putting session.add(item) in your else clause. This would get rid
of the need for session.merge(). (If you want to be able to use the
get with non-global sessions, pass the session as a parameter.)
Finally, if your session isn't auto-flushing, it would be possible for
you to call get twice with the same parameters and get 2 different
objects back.
You may want to look at the UniqueObject recipe in the wiki:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject
Hi Simon,
Thanks for the fast reply.
I tried adding session.add(item) and session.flush() in the else clause
in
the past but that didn't solve my problem.
I didn't however remove the merge, do you think that might be the
problem?
Regarding the flush, this code is part of an API server where a
scoped_session is committed after each change. I haven't changed the
autoflush parameter, and as I understand the default value is True
making a
flush before each commit or query.
As for the UniqueObject recipe, thanks! Amazing that I never found it
searching for a cure. As I see it basically does the same ...
I never managed to reproduce this bug on my development environment. It
only
happens in my production environment.
Do you suppose adding a session.add and removing the merge will solve
this
issue?
Thanks,
Ofir
It's difficult to say without knowing more about your system. For
example, does your production system get multiple concurrent API
requests, or are they serialised? If 2 requests can come in at
approximately the same time and are handled by 2 different threads (or
processes), then it is easy to imagine that the first handler will
check the database, find that an entry doesn't exist, and create it.
But before it flushes the change to the database (or even after it
flushes, but before it commits, depending on your transaction
isolation), the second handler will check for the same object, find it
missing, and so create it.
To track down problems like this, you could ensure that your
development environment has the same thread/process behaviour as the
production environment, then try submitting multiple
Re: [sqlalchemy] Occasional IntegrityError when identifying model not by its ID
Suppose we are looking at a race condition, do you also think this should
be handled by catching the IntegrityError?
If so, what should I do? only flush and do the operation again?
On Tuesday, August 27, 2013 5:42:23 PM UTC+3, Michael Bayer wrote:
the word occasional is very meaningful. It usually suggests race
conditions.Then with the word tornado, the baysean filters are
strongly leaning towards race condition at that point :).
if an error is occurring only under volume then you have to revisit where
race conditions can occur.
On Aug 27, 2013, at 10:32 AM, herzaso her...@gmail.com javascript:
wrote:
I'm running a Tornado server without redundancy (only one process,
requests can arrive at the same time but will be handled one at a time)
I do agree that for large volumes, catching the IntegrityError would be
better, but currently I am handling a single request at a time and I want
to fix this problem before I move on ...
On Tuesday, August 27, 2013 5:24:07 PM UTC+3, Simon King wrote:
On Tue, Aug 27, 2013 at 2:31 PM, herzaso her...@gmail.com wrote:
On Tuesday, August 27, 2013 3:55:50 PM UTC+3, Simon King wrote:
On Tue, Aug 27, 2013 at 1:40 PM, herzaso her...@gmail.com wrote:
I have a model with an ID column set as the primary key, though i'd
like
to
be able to identify records by 3 other columns.
For this situation, I've added a classmethod that will fetch the
record
if
found or a new record if not.
The problem i'm having is that every once in a while, I get
IntegrityError
trying to flush a change
class Foo(Base):
__table_args__ = (sa.UniqueConstraint('bar', 'baz', 'qux'),)
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' %
__tablename__),
nullable=False, primary_key=True)
bar = sa.Column(sa.BigInteger)
baz = sa.Column(sa.BigInteger)
qux = sa.Column(sa.BigInteger)
a1 = sa.Column(sa.BigInteger)
a2 = sa.Column(sa.BigInteger)
@classmethod
def get(cls, bar=None, baz=None, qux=None, **kwargs):
item = session.query(cls).\
filter(cls.bar== bar).\
filter(cls.baz == baz).\
filter(cls.qux == qux).\
first()
if item:
for k, v in kwargs.iteritems():
if getattr(item, k) != v:
setattr(item, k, v)
else:
item = cls(bar=bar, baz=baz, qux=qux, **kwargs)
return item
This is the code I use to add/update records:
foo = Foo.get(**item)
session.merge(foo)
I'm struggling with this problem for some time now, and would
appreciate
any
help ...
I'm not sure of the exact problem, but there are a couple of things
that you could investigate.
Firstly, session.merge returns a copy of the object, rather than
adding the object that you supplied into the session. See
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#merging for
details.
Secondly, your get method sometimes returns objects that are already
part of the session (if they were in the database), and sometimes
objects that are not in the session. It would probably be more
consistent to always return objects that are part of the session, by
putting session.add(item) in your else clause. This would get rid
of the need for session.merge(). (If you want to be able to use the
get with non-global sessions, pass the session as a parameter.)
Finally, if your session isn't auto-flushing, it would be possible for
you to call get twice with the same parameters and get 2 different
objects back.
You may want to look at the UniqueObject recipe in the wiki:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject
Hi Simon,
Thanks for the fast reply.
I tried adding session.add(item) and session.flush() in the else clause
in
the past but that didn't solve my problem.
I didn't however remove the merge, do you think that might be the
problem?
Regarding the flush, this code is part of an API server where a
scoped_session is committed after each change. I haven't changed the
autoflush parameter, and as I understand the default value is True
making a
flush before each commit or query.
As for the UniqueObject recipe, thanks! Amazing that I never found it
searching for a cure. As I see it basically does the same ...
I never managed to reproduce this bug on my development environment. It
only
happens in my production environment.
Do you suppose adding a session.add and removing the merge will solve
this
issue?
Thanks,
Ofir
It's difficult to say without knowing more about your system. For
example, does your production system get multiple concurrent API
requests, or are they serialised? If 2 requests can come in at
approximately
Re: [sqlalchemy] Occasional IntegrityError when identifying model not by its ID
Thanks Michael,
I will try that and let you know if it solved my issue.
BTW: Is there a lock mechanism for such conditions?
Thanks,
Ofir
On Tuesday, August 27, 2013 6:40:03 PM UTC+3, Michael Bayer wrote:
I'm not a fan of catching integrity errors, i prefer to try to make sure
they aren't going to happen, or if they are, they aren't a normal
occurrence and the system is such that the particular operation can just
fail (of course it depends on what it is). A problem with catching the
integrity error due to concurrent, conflicting operations is that depending
on backend and isolation level, you can't be totally sure when the error is
going to get raised (e.g. serializable isolation vs. non). Also on a
backend like Postgresql, the database can't recover the transaction after
an integrity error unless you used a savepoint.
But here you're doing the concurrent transactions need row identity X,
so maybe it is appropriate here. Here is a rough idea of a transactional
pattern for that, noting this isn't tested:
try:
my_object = Session.query(MyClass).filter().one()
except NoResultFound:
try:
with Session.begin_nested(): # run inside a SAVEPOINT
my_object = MyClass(...)
Session.add(my_object)
Session.flush()
except IntegrityError:
my_object = Session.query(MyClass).filter().one()
On Aug 27, 2013, at 11:13 AM, herzaso her...@gmail.com javascript:
wrote:
Suppose we are looking at a race condition, do you also think this should
be handled by catching the IntegrityError?
If so, what should I do? only flush and do the operation again?
On Tuesday, August 27, 2013 5:42:23 PM UTC+3, Michael Bayer wrote:
the word occasional is very meaningful. It usually suggests race
conditions.Then with the word tornado, the baysean filters are
strongly leaning towards race condition at that point :).
if an error is occurring only under volume then you have to revisit where
race conditions can occur.
On Aug 27, 2013, at 10:32 AM, herzaso her...@gmail.com wrote:
I'm running a Tornado server without redundancy (only one process,
requests can arrive at the same time but will be handled one at a time)
I do agree that for large volumes, catching the IntegrityError would be
better, but currently I am handling a single request at a time and I want
to fix this problem before I move on ...
On Tuesday, August 27, 2013 5:24:07 PM UTC+3, Simon King wrote:
On Tue, Aug 27, 2013 at 2:31 PM, herzaso her...@gmail.com wrote:
On Tuesday, August 27, 2013 3:55:50 PM UTC+3, Simon King wrote:
On Tue, Aug 27, 2013 at 1:40 PM, herzaso her...@gmail.com wrote:
I have a model with an ID column set as the primary key, though i'd
like
to
be able to identify records by 3 other columns.
For this situation, I've added a classmethod that will fetch the
record
if
found or a new record if not.
The problem i'm having is that every once in a while, I get
IntegrityError
trying to flush a change
class Foo(Base):
__table_args__ = (sa.UniqueConstraint('bar', 'baz', 'qux'),)
id = sa.Column(Identifier, sa.Sequence('%s_id_seq' %
__tablename__),
nullable=False, primary_key=True)
bar = sa.Column(sa.BigInteger)
baz = sa.Column(sa.BigInteger)
qux = sa.Column(sa.BigInteger)
a1 = sa.Column(sa.BigInteger)
a2 = sa.Column(sa.BigInteger)
@classmethod
def get(cls, bar=None, baz=None, qux=None, **kwargs):
item = session.query(cls).\
filter(cls.bar== bar).\
filter(cls.baz == baz).\
filter(cls.qux == qux).\
first()
if item:
for k, v in kwargs.iteritems():
if getattr(item, k) != v:
setattr(item, k, v)
else:
item = cls(bar=bar, baz=baz, qux=qux, **kwargs)
return item
This is the code I use to add/update records:
foo = Foo.get(**item)
session.merge(foo)
I'm struggling with this problem for some time now, and would
appreciate
any
help ...
I'm not sure of the exact problem, but there are a couple of things
that you could investigate.
Firstly, session.merge returns a copy of the object, rather than
adding the object that you supplied into the session. See
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#merging for
details.
Secondly, your get method sometimes returns objects that are
already
part of the session (if they were in the database), and sometimes
objects that are not in the session. It would probably be more
consistent to always return objects that are part of the session, by
putting session.add(item) in your else clause. This would get rid
of the need for session.merge(). (If you want to be able to use
