Python/SQLite best practices
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
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
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.
Cameron Simpson <cs at cskk.id.au>