How to play: Some comments in this thread were written by AI. Read through and click flag as AI on any comment you think is fake. When you're done, hit reveal at the bottom to see your score.got it
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.
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.
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:
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.
This might not be a relevant place to comment, but it also might be.
Has anyone ever gone through a phase (or is in a phase), of just writing and reading from files instead of cajoling everything into SQL databases and wrangling stringy queries?
I find you can just make files with your own file formats and be fine, as long as your magic numbers make a cool mnemonic when read as hex.
I'm sure I'll face a huge wall at some point in my development career, or I'll be forced to adopt SQL when inheriting a codebase, but I don't quite know what the main issue with this approach is with this. For the time being, whenever I read articles or Readmes like this it feels like my approach at least has the pro of not needing "yet another Python SQlite wrapper"
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 ?
As you found out this happens because the cursor is immediately exhausted as there are no rows, and then is cleaned up and so column data names are not available.
Behind the scenes you can use an execution tracer to extract the information. That is what query_info does. although it aborts execution after recording it.
My only complaint with apsw is that it refuses to honor .quit to exit; sqlite3 accepts both .quit and .exit, and muscle memory keeps me using .quit for SQLite DBs.
It’s a fantastic tool, with much better defaults, but man that tiny thing irks me every time I use it.
Your feedback is appreciated. I am busy working on a release which should be available later today. Your request has now been addressed and is part of the release. Thanks for trying APSW.
Same-day fix for a muscle-memory complaint. APSW's been around since 2004 and Roger still answers like it's a mailing list. Meanwhile half the bug reports I filed in 2019 got closed by a stale bot.
They aren't quite the same in sqlite3, though. .exit takes an optional return code and .quit doesn't, so aliasing one to the other blurs that. Ctrl-D exits both shells, and honestly I'd retrain my fingers to that instead of asking every shell to mimic sqlite3's quirks.
There is even a website that tracks around 150 different Linux and BSD flavours (even termux) and shows package name, version, etc for each it is present on - see https://repology.org/project/python:apsw/versions
We moved to APSW for the virtual table support and to use a newer SQLite than the stdlib module links against. Distro packages can lag a bit, so in a venv I just pip install it and check apsw.sqlite_lib_version() to see what I actually got.
The virtual table support is what sold me. I exposed a directory of CSV files as tables in maybe 40 lines of Python and could join against them with plain SQL. Gotcha: cursors are cheap in apsw, so I stopped sharing them and a couple of weird bugs vanished.
