On Wednesday, 27 September, 2017 00:35, bensonbear <benson.b...@gmail.com> wrote:
>I mentioned I was using python and its sqlite3 module, but did >not specify I was using it in simplest possible default mode, just >opening the connection with no arguments. >I didn't need to know more before -- with a simple single-threaded >program I thought (and still think) it should be fine to >just open a connection, do stuff, commit and close. >This appears to be true, but reading more about the sqlite3 >module, what it does in the default mode is a little tricky with >respect to when commits are done. >I didn't even know that outside transactions, SQLite by >default itself commits automatically after each statement, >which I would not have wanted. However, the python >module by default implicitly inserts "begin"s so that >statements are grouped into a transaction that one then has to >explicitly commit Thus, that the commit I did after them was >necessary >for that reason, and not the reason I assumed, which was that >SQLite itself needed the commit even outside transactions. >(I think I would prefer the module's option where it keeps SQLite >in autocommit mode and one then has to make one's own >transactions explicitly). Yeah, the default sqlite3 interface (pysqlite2) in python is weird. I switched to APSW a long time ago as it is less magical and better designed (and faster and more efficient as well). For simple use it is almost no more complicated than changing the name of the module and the options on the open call. You lose the data adapter stuff that is in the sqlite3 wrapper, but you gain a lot of other stuff since it is truly a "wrapper" for the Sqlite3 library and not a "standardized interface designed to be compatible with every RDBMS on the market" -- so you have access to just about all of the SQLite3 API functions via Python, not just the minimalized set that are supported by "everyone and their grandmother" (aka the DB-API). see the main page at https://github.com/rogerbinns/apsw and https://rogerbinns.github.io/apsw/pysqlite.html for an overview of the differences between the pysqlite2 wrapper and APSW. The best way to deal with transactions in the pysqlite2 wrapper is to set isolation=None on the open. This means that the magic is turned off in the wrapper and that you must explicitly begin or end transactions. If you do not, then the SQLite library (not the wrapper) runs in its default "autocommit" mode. This is especially important if you either format your SQL statements or use some that are not handled appropriately by the wrapper. Plus, your observed behaviour will exactly match the results obtained by issuing the same sequence of statements using the sqlite3 command line tool with no possible interference by magic occurring behind the scene. About the only (real) drawback of APSW is that it only works with standard CPython. It does not work with IronPython, PyPy, or many other Python interpreters. If this is important to you then you might want to stick with the pysqlite2 wrapper included as part of the Python standard library and ported to almost all other Python interpreters. Of course, if you use some of the APSW features that are inaccessible in other wrappers like pysqlite2 such as the execution hooks, authorizer hooks, logging and detailed statement hooks, you cannot backport to the DB-API which simply does not support all these interfaces. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
