Therefore, the right thing to do is to always explicitly mark the beginning of your transactional with connection blocks using BEGIN. It only has an effect when you _exit_ the scope, choosing either COMMIT or ROLLBACK depending on whether the scope is exiting normally or with an exception. In fact it doesn't do anything at all in _enter_. # do other things, but do NOT use 'executescript'Ĭontrary to my intuition, with connection does not call BEGIN upon entering the scope. The short answer is that if you want a proper transaction, you should stick to this idiom: with connection: Here's what I think is happening based on my reading of Python's sqlite3 bindings as well as official Sqlite3 docs. Therefore, if you let Python exit the with-statement when an exception occurs, the transaction will be rolled back. Transaction is rolled back otherwise, the transaction is committed: Python 2.7, python-sqlite3 2.6.0, sqlite3 3.7.13, Debian.Ĭonnection objects can be used as context managers that automaticallyĬommit or rollback transactions. In addition, changing sql.isolation_level appears to make no difference to the behaviour.)Ĭan someone explain to me what's happening here? I need to understand this if I can't trust the transactions in the database, I can't make my application work. (I should also add that if I put the begin and commit inside the inner call to executescript then it behaves correctly in all cases, but unfortunately I can't use that approach in my application. However, if I replace the calls to c.execute() to c.executescript(), then it works (i remains at 99)! I get this: sqlite3.OperationalError: cannot rollback - no transaction is active Now I'm calling BEGIN and COMMIT explicitly: import sqlite3 This behaves in precisely the same way - i gets changed from 99 to 1. Here is another test program, which explicitly calls commit() and rollback(). I'm expecting it to remain at 99, because that first update should be rolled back. However, when I run it, I get the expected SQL error. This causes the SQL script to fail on the second line, after the update has been executed.Īccording to the docs, the with sql statement is supposed to set up an implicit transaction around the contents, which is only committed if the block succeeds. You may notice the deliberate mistake in it. Here is the schema for my test database (to be fed into the sqlite3 command line tool). I'm really confused by this I've used sqlite a lot in other languages, because it's great, but I simply cannot work out what's wrong here. I'm trying to port some code to Python that uses sqlite databases, and I'm trying to get transactions to work, and I'm getting really confused.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |