![]() And thus we could the view the current intermediate state and decide for rollback (or commit nevertheless) > list(conn.execute('SELECT * FROM test')) ![]() OperationalError: near "FNORD": syntax error > conn.executescript("BEGIN UPDATE TEST SET i = 1 FNORD COMMIT""") And this interaction shows step by step whats going on: > list(conn.execute('SELECT * FROM test')) So with executescript() we better use a explicit BEGIN (just as your inital schema creation script did for the "raw" mode sqlite command line tool). This also means that without a "BEGIN" inside the script executescript() works without a transaction, and thus no rollback option upon error or otherwise. Therefor it just commits a potentially pending auto-commit transactions at start, before "going raw". context manager) but forwards the script rather raw. Sqlite3 module's non-standard conn_or_cursor.executescript() doesn't take part in the (default) auto-commit mode (and so doesn't work normally with the with conn. context manager doing auto-commit OR rollback - except for protected read-modify-write transactions, which are explained at the end of this answer. execute()'s work as expected with the comfortable default auto-commit mode and the with conn. avoid executescript entirely you can call execute as many times as you want, subject to the one-statement-per- execute limitation.use exactly one executescript within the with block and nothing else, or.This can easily mess up the transactional with connection block, so your choice is to either This renders isolation_level irrelevant within the block, because thankfully it only has an effect while autocommit mode is enabled, and autocommit mode is always suppressed within transaction blocks.Īnother quirk is executescript, which always issues a COMMIT before running your script. 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 |