Python/SQLite best practices
Cameron Simpson
cs at cskk.id.au
Mon Aug 5 20:55:22 EDT 2019
On 06Aug2019 00:01, Jonathan Moules <jonathan-lists at lightpear.com> wrote:
>Some gotcha tips from using SQLite with Python that I've encountered.
[...]
>* To be reliably INSERTed Byte data should be first converted to
>sqlite3.Binary(my_data) explicitly
Interesting. Is that Python 2 specific, or also in Python 3. Because the
latter would surprise me (not saying it isn't the case).
>* 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.
On a UNIX system the command "lsof -p pid-of-running-python-process"
should show the path of the sqlite library that is linked to the Python
executable, which should let you learn this.
>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
And for a thicker wrapper, I've been extremely happy using SQLAlchemy
for database access. It has an expression syntax where real Python
expressions (containing "column" objects) evaluate to safe SQL, letting
you write safe queries in nice Pythonic form, and it also has an ORM for
more sophisticated use. It provided context manager for transactions and
sessions for various work. Finally, it knows about a lot of backends, so
you could switch backends later (eg from SQLite to PostgreSQL) if that
becomes a thing.
Cheers,
Cameron Simpson <cs at cskk.id.au>
More information about the Python-list
mailing list