[sqlalchemy] Re: rollback not working
Thanks a lot for bearing with me. explicit execution - uses a Connection, but will autocommit conn = engine.connect() conn.execute(insert into table (a, b, c) values (1, 2, 3)) I still want a clarification about the above case. When we use explicit execution like the above, will each conn.execute() statement be in a transaction or not? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: rollback not working
On Jan 24, 2011, at 5:46 AM, bool wrote: Thanks a lot for bearing with me. explicit execution - uses a Connection, but will autocommit conn = engine.connect() conn.execute(insert into table (a, b, c) values (1, 2, 3)) I still want a clarification about the above case. When we use explicit execution like the above, will each conn.execute() statement be in a transaction or not? Everything is in a transaction. The above transaction autocommits upon each INSERT/UPDATE/DELETE statement. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: rollback not working
I am not an expert in these concepts, so just trying to make sure I understand what you said. 1. If I use connection.execute(), then then every sql statements is not put in its own transactions. 2. But If I use connection-less execution like table.execute or engine.execute() then every statement is put in its own transaction. Can you confirm if the above understanding is correct. Also what is the DBAPI that you generally refer to ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: rollback not working
On Jan 21, 2011, at 9:16 AM, bool wrote: I am not an expert in these concepts, so just trying to make sure I understand what you said. 1. If I use connection.execute(), then then every sql statements is not put in its own transactions. 2. But If I use connection-less execution like table.execute or engine.execute() then every statement is put in its own transaction. so the relevant info we're talking about is here: http://www.sqlalchemy.org/docs/core/connections.html connectionless execution - execute statement in a new transaction, if its an INSERT/UPDATE/DELETE, autocommit: engine.execute(select * from table) implicit, connectionless execution - same behavior, executes in a new transaction, autocommits: table.insert().execute(a=5, b=4) explicit execution - uses a Connection, but will autocommit conn = engine.connect() conn.execute(insert into table (a, b, c) values (1, 2, 3)) ...unless you start a transaction: trans = conn.begin() conn.execute(insert into table (a, b, c) values (1, 2, 3)) conn.execute(insert into table (a, b, c) values (4, 5, 6)) trans.commit() ORM: using the Session, you're in a transaction for all operations, bounded by rollback()/commit(): sess = Session() sess.execute(insert into table (a, b, c) values (1, 2, 3)) sess.execute(insert into table (a, b, c) values (4, 5, 6)) sess.commit() ... unless you're using autocommit : sess = Session(autocommit=True) Can you confirm if the above understanding is correct. Also what is the DBAPI that you generally refer to ? DBAPI is what you're talking to your database with: http://www.python.org/dev/peps/pep-0249/ -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: rollback not working
On Jan 20, 2011, at 2:04 AM, bool wrote: If I dont use autocommit:True option, it seems the driver will be in a chained transaction mode and results in every single statement (including selects) being run in a new transaction. This is not desirable either. Is there a way out ? The DBAPI doesn't do that, since DBAPI calls for a single connection to be open in a transaction immediately, the state of which is only ended by calling rollback or commit on that connection. if you're using SQLalchemy using connectionless execution, i.e. table.insert().execute(), then yes each execute() is in its own transaction, as it should be since no transactional boundary has been declared. This style of usage is not appropriate for a large volume of operations. Usually you create a transaction for a series of operations, which is automatic when using the ORM with a Session object, so that there's a reasonable boundary of transactions. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: rollback not working
If I dont use autocommit:True option, it seems the driver will be in a chained transaction mode and results in every single statement (including selects) being run in a new transaction. This is not desirable either. Is there a way out ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: rollback not working
I have put a self contained script that can probably reproduce this
for you. I think that the argument 'connect_args': {'autocommit' :
True } is causing the transactions to not work properly. Is this
expected and if so can you explain the reason. Thanks in advance.
from sqlalchemy import *
from sqlalchemy.sql import *
def main():
Main body of the script.
meta= MetaData()
kwargs = {
'echo' : True,
'module_name' : 'pyodbc',
'connect_args': {'autocommit' : True },
}
engine = create_engine(mssql://login:password@/database?
driver=FreeTDSdsn=DBDEV, **kwargs)
connection = engine.connect()
meta.bind = engine
table = Table(bbb, meta, Column('id', Integer,
primary_key=True), Column('name', String),
Column('tt_start', Date, primary_key=True),
Column('tt_end', Date, primary_key=True),
Column('vt_start', Date, primary_key=True),
Column('vt_end', Date, primary_key=True))
table.create()
for row in connection.execute(select([table])):
print row
trans = connection.begin()
try:
ins = table.insert().values(id=122, name='k',
tt_start='20100101', tt_end='20100101', vt_start='20100101',
vt_end='20100101')
connection.execute(ins)
ins = table.insert().values(id=121, name='k')
connection.execute(ins)
trans.commit()
except:
trans.rollback()
for row in connection.execute(select([table])):
print row
raise
if __name__ == __main__:
main()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: rollback not working
that is absolutely the reason transactions would not be working for you.
autocommit on the DBAPI essentially makes the commit() and rollback() methods
of the DBAPI connection a no-op.
On Jan 18, 2011, at 5:12 AM, bool wrote:
I have put a self contained script that can probably reproduce this
for you. I think that the argument 'connect_args': {'autocommit' :
True } is causing the transactions to not work properly. Is this
expected and if so can you explain the reason. Thanks in advance.
from sqlalchemy import *
from sqlalchemy.sql import *
def main():
Main body of the script.
meta= MetaData()
kwargs = {
'echo' : True,
'module_name' : 'pyodbc',
'connect_args': {'autocommit' : True },
}
engine = create_engine(mssql://login:password@/database?
driver=FreeTDSdsn=DBDEV, **kwargs)
connection = engine.connect()
meta.bind = engine
table = Table(bbb, meta, Column('id', Integer,
primary_key=True), Column('name', String),
Column('tt_start', Date, primary_key=True),
Column('tt_end', Date, primary_key=True),
Column('vt_start', Date, primary_key=True),
Column('vt_end', Date, primary_key=True))
table.create()
for row in connection.execute(select([table])):
print row
trans = connection.begin()
try:
ins = table.insert().values(id=122, name='k',
tt_start='20100101', tt_end='20100101', vt_start='20100101',
vt_end='20100101')
connection.execute(ins)
ins = table.insert().values(id=121, name='k')
connection.execute(ins)
trans.commit()
except:
trans.rollback()
for row in connection.execute(select([table])):
print row
raise
if __name__ == __main__:
main()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: rollback not working
FYI I am using sqlserver 2008 and sqlalchemy 0.5 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Rollback not working like I expect it to (sort of)
ie: I expect nesting behaviour like in MatthieuF's response here for SQLServer: http://stackoverflow.com/questions/527855/nested-transactions-in-sql-... OK, that's a behavior specific to stored procedures in SQL server, not one I was aware of. To my knowledge, a nested transaction is not really a normal thing in SQL, usually if you want such a thing you use SAVEPOINT - which SQLA's ORM session provides via begin_nested(). The usual behavior of transactions with DBAPI is that there is only one transaction at a time - if you were to call BEGIN repeatedly, the subsequent calls have no effect. Incidentally, the BEGIN is implicit with DBAPI. SQLAlchemy doesn't issue any kind of BEGIN, only connection.rollback() or connection.commit(). Thanks for clarifying this. Among other things, I thought the BEGINs where being issued at a higher level. I've been digging further and I do see that according to the Sqlite documentation Transactions created using BEGIN...COMMIT do not nest. For nested transactions, use the SAVEPOINT and RELEASE commands. In short - what I observed has nothing fundamentally to do with SQLAlchemy, as I expected it might. But it does have me wondering about fiddling with SQLAlchemy and the sqlite dialect to try and automatically manage nested transactions using generated SAVEPOINTs and RELEASE. yeah you need to use begin_nested(). We don't allow any implicit nesting at the ORM level since it just confuses people. What I've ended up doing here is severely limiting my use of .commit(). I now use .flush() instead in most cases. I originally had nested commits in several locations where the inner commits were in place solely to get access to things like the autoincrementing id in a table on the next line of code. With my thinking that commits *could* be nested I did not think it was an issue. Unit tests told me otherwise, though. Worth noting is that me doing these inner commits (when I knew full well i wasn't complete with a transaction I may roll back) is rooted in the fact that you have this direct demonstration in the ORM tutorial. You demonstrate that id == None until you do a commit. I was following this, although in hindsight I feel a bit silly since I now recognize the problem and that know that flush() is much more appropriate. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Rollback not working like I expect it to (sort of)
Thanks... I don't think my question stemmed from a lack of reading the
extensive sqlalchemy documentation, but more of a limitation on my
understanding of how sqlite works (or doesn't) with nested
transactions. I know that you need the SingletonThreadPool or nested
sessions completely block on the inner session.
My issue was that with the nested transaction (sess3 inside sess2) I
thought that it should not have left 'jack' persisted after sess2 was
rolled back.
ie: I expect nesting behaviour like in MatthieuF's response here for
SQLServer:
http://stackoverflow.com/questions/527855/nested-transactions-in-sql-server
After your (clearly frustrated...sorry?!?) response, I've been digging
further and I do see that according to the Sqlite documentation
Transactions created using BEGIN...COMMIT do not nest. For nested
transactions, use the SAVEPOINT and RELEASE commands. This explains
why it did not work... it seems that the sess3 COMMIT also committed
sess2.
Relevant sqlite doc page here:
http://www.sqlite.org/lang_transaction.html
In short - what I observed has nothing fundamentally to do with
SQLAlchemy, as I expected it might. But it does have me wondering
about fiddling with SQLAlchemy and the sqlite dialect to try and
automatically manage nested transactions using generated SAVEPOINTs
and RELEASE.
so above, issue #1, SQLite doesn't support foreign key constraints by default.
They are accepted syntactically, but do nothing.
My main issue was that jack remained after the rollback, not the fk
constraint, although your comment certainly clarifies that (without
enabling the new feature) I certainly can't have expected sqlite to
complain that jack was not committed at the time of address storage.
For future reference, the feature Mike mentioned that lets you change
the foreign key constraint handling from the current default of no
constraints is here:
http://www.sqlite.org/foreignkeys.html
On Oct 18, 12:57 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Oct 18, 2010, at 12:41 PM, Russell Warren wrote:
#sess2 will add a new user (jack), but we'll roll it back in the end
sess2 = Session()
jack = User('jack')
jack.addresses = [Address(email_address='j...@example.com'),
Address(email_address='j...@example.org')]
sess2.add(jack)
sess2.flush() #no commit
so above, issue #1, SQLite doesn't support foreign key constraints by
default. They are accepted syntactically, but do nothing. Consult the
sqlite documentation (SQLite itself, not SQLAlchemy) for information on a
fairly recent feature that enables them to enforce the constraints.
#sess3 will create an address linked to jack (before he gets rolled
back)
sess3 = Session()
addr3 = Address(h...@hmm.com, jack_id) #but this id will be rolled
back
# adding to jack in another session succeeds even though jack is not
commited...
sess3.add(addr3)
sess3.commit()
#Now rollback sess2, which should get rid of jack... but doesn't??
sess2.rollback()
print Session().query(User).all()
issue #2, I've tried to emphasize this as much as possible in the
documentation, the SQLite dialect uses *one connection* for the thread, by
default. This default is being changed in 0.7 (so in 0.7, the common issue
will become database is locked issues, whereby I'll probably have to tell
people to switch back to SingletonThreadPool ).
The one connection per thread default is discussed at the following
locations:
http://www.sqlalchemy.org/docs/dialects/sqlite.html?highlight=sqlite#...http://www.sqlalchemy.org/docs/core/pooling.html?highlight=singleton#...http://www.sqlalchemy.org/docs/core/pooling.html#sqlalchemy.pool.Sing...
and exactly what you are doing is mentioned at:
http://www.sqlalchemy.org/trac/wiki/FAQ#Iamusingmultipleconnectionswi...
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Rollback not working like I expect it to (sort of)
On Oct 18, 2010, at 3:10 PM, Russell Warren wrote:
Thanks... I don't think my question stemmed from a lack of reading the
extensive sqlalchemy documentation, but more of a limitation on my
understanding of how sqlite works (or doesn't) with nested
transactions. I know that you need the SingletonThreadPool or nested
sessions completely block on the inner session.
My issue was that with the nested transaction (sess3 inside sess2) I
thought that it should not have left 'jack' persisted after sess2 was
rolled back.
ie: I expect nesting behaviour like in MatthieuF's response here for
SQLServer:
http://stackoverflow.com/questions/527855/nested-transactions-in-sql-server
OK, that's a behavior specific to stored procedures in SQL server, not one I
was aware of. To my knowledge, a nested transaction is not really a normal
thing in SQL, usually if you want such a thing you use SAVEPOINT - which SQLA's
ORM session provides via begin_nested().
The usual behavior of transactions with DBAPI is that there is only one
transaction at a time - if you were to call BEGIN repeatedly, the subsequent
calls have no effect. Incidentally, the BEGIN is implicit with DBAPI.
SQLAlchemy doesn't issue any kind of BEGIN, only connection.rollback() or
connection.commit().So given that, and the thread-local connection
behavior, the conversation with sqlite looks like:
connection = sqlite3.connect(':memory:')
execute SQL with the connection
connection.commit()
execute SQL with the connection
connection.commit()
etc.
After your (clearly frustrated...sorry?!?) response,
yikes, not intended at all, guess I don't have a clear view of how my emails
sound
I've been digging
further and I do see that according to the Sqlite documentation
Transactions created using BEGIN...COMMIT do not nest. For nested
transactions, use the SAVEPOINT and RELEASE commands.
Well that's news to me that SQLIte supports SAVEPOINT. I just tried enabling
SQLite for our SAVEPOINT tests and got an error at the point at which it
emitted SAVEPOINT, so this must be something extremely new in SQLite, not
supported by the stock version that comes with Python 2.7.
Relevant sqlite doc page here:
http://www.sqlite.org/lang_transaction.html
In short - what I observed has nothing fundamentally to do with
SQLAlchemy, as I expected it might. But it does have me wondering
about fiddling with SQLAlchemy and the sqlite dialect to try and
automatically manage nested transactions using generated SAVEPOINTs
and RELEASE.
yeah you need to use begin_nested(). We don't allow any implicit nesting at
the ORM level since it just confuses people.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
