Some gotcha tips from using SQLite with Python that I've encountered. You may already know some/all of these:

* SQLite doesn't have a "Truncate" function - simply delete the file if possible for larger datasets. * Explicitly committing is good because the default python sqlite3 library does it randomly and implicitly. I found that doing it only when the database is dettaching or closing speeds things up a lot. * SQLite 3 only considers up to 64bits an INTEGER. So if you want to insert a 128bit string you have to use Python string substitution (i.e. "Hello %s") rather than the SQLite variable substitution "insert into tab values (?)" * To be reliably INSERTed Byte data should be first converted to sqlite3.Binary(my_data) explicitly * By default Foreign Keys are not enforced. Enable them at connection time if you care about referential integrity! * It's typically opaque as to where the install of SQLite is that the library is using and it's very hard and not-documented as to how to update the SQLite version that Python is using.

If you want an even thinner wrapper around SQLite there's APSW ( https://rogerbinns.github.io/apsw/index.html ) - I've never used it myself but it's useful to know about. There's a page with differences - https://rogerbinns.github.io/apsw/pysqlite.html#pysqlitediffs


On 2019-08-05 22:43, David Raymond wrote:
"What's the advantage of this over letting the connection object do
that for you? As the context manager exits, it will automatically
either commit or roll back. If you want to guarantee closing _as
well_, then you can do that, but you can at least use what already
exists."

After review I guess I should have phrased it more as a "here's what I've found for 
reference" rather than a "here's what _you_ should do"


Part of it is large use of the Command Line Interface for SQLite, and similar 
command line tools for other db's, which all work in autocommit mode by 
default, so that's how my brain is now wired to think about executing things.

The context manager transaction feature I can see using, and might actually 
start switching to it as it's explicit enough. Though oddly, __enter__ doesn't 
seem to actually begin a transaction, not even a deferred one. It's only 
__exit__ that either commits or rolls back.
(Eh, it'd "probably" be simple enough to subclass Connection so that __enter__ 
and __exit__ work properly no matter the isolation_level. Famous last words)

The implicit stuff I hated because it never seemed straightforward enough. Especially since there used to be 
implicit commits as well as implicit begins ("Changed in version 3.6: sqlite3 used to implicitly commit 
an open transaction before DDL statements. This is no longer the case.") Maybe because I was new to both 
Python and SQLite at the time, but there was a lot of "stop doing hidden stuff I didn't tell you 
do" getting muttered, along with others like "why do I need to commit when I never did a 
begin?" The documentation on it is all of 1 sentence, so there was a lot of trial an error going on.
"The Python sqlite3 module by default issues a BEGIN statement implicitly before a 
Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE)."


"(Also, I'd definitely use conn.commit() rather than
cur.execute("commit"), in case there's extra functionality in the
commit method.)"

True. I know for example that if you try to rollback when not in a transaction that 
cur.execute("rollback;") will raise an exception whereas conn.rollback() will 
quietly suppress it for you. So there might be similarly useful stuff in .commit()
sqlite3 is (almost) all C though, so there'd be noticeably more digging and 
decyphering required to check. (For me anyway)


--
https://mail.python.org/mailman/listinfo/python-list

Reply via email to