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