In a sentence, I'd say apsw is "the SQLite lover's SQLite library". The python stdlib one tries to provide a generic interface ("DBAPI") which is great if you want to be able to swap to a different database engine later, but it's bad for actually getting the most out of SQLite.

If you are used to stdlib but want sane transactional semantics or the ability to easily statically link your own SQLite implementation (eg to have encryption), I’ve written some bindings as well:

https://github.com/coleifer/cysqlite

This supersedes my earlier pysqlite3 project. Perf is better than stdlib, but not as good as apsw. Roger is doing amazing work and I urge anyone wanting to fully utilize SQLite to try apsw. It does everything, exposes all API surfaces, and has a fantastic track record.

For anyone interested in an ergonomic API over this-- I ported sqlite-utils to apsw a while back: https://github.com/AnswerDotAI/apswutils

I've been using it a lot over the last year or two and it's worked great.

This library is great, and recently saved a project of mine. Figuring out what was going on with some of python's sqlite3 quirks was too much of a hassle, meanwhile this more or less just worked the way I expected it to. Because of this experience I started defaulting to it over sqlite3 for other projects as well.

(This documentation page explains the library's intent: https://rogerbinns.github.io/apsw/pysqlite.html)

Thank you HackerNews. I learned something new and will be using APSW for my next Sqlite project.

I was experimenting with APSW and I noticed that when a query doesn't return any row, it becomes difficult to get column names from the cursor, is there any recommended way to do this ?

It’s a great wrapper, but we should add a year. Not exactly new.