Python/SQLite best practices

[Jonathan Moules]

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)
>