Python/SQLite best practices

[Cameron Simpson]

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>