3 ms·
For a project a few years ago I chose to use apsw: https://github.com/rogerbinns/apsw https://github.com/rogerbinns/apsw It is a different wrapper for sqlite
by chubot 9y ago
For a project a few years ago I chose to use apsw:
https://github.com/rogerbinns/apsw https://github.com/rogerbinns/apsw
It is a different wrapper for sqlite than the one in the Python standard library. As far as I remember, the stdlib one had some brain-dead choices with regard to concurrency and transactions.
There may have been some other reasons I chose it too, but I don't recall at the minute. The issue is that Python has this spec called "DB-ABI" which is supposed to make all databases look alike. But I don't want that -- I just want to use sqlite!
So 'apsw' is a thin layer on top of sqlite. I'm able to read the sqlite documentation in C, and be reasonably confident in what it's doing. But as far as I remember the Python stdlib version had a bunch of magic in the wrappers that was annoying. I might be mistaken but I think it parsed its own SQL? I think it had some sort of silly cache of parsed queries, rather than letting you simple store/control that in the application.
I think it also inserted SQL statements for transactions between your own calls!!! I just want the raw SQL to go to sqlite as I intended.
I definitely ran into issues where non-obvious flags to the API increased insert performance by 10x, and it had nothing to do with sqlite itself! So I abandoned it for a thinner wrapper.
- tyingq 9y agoThanks for this pointer. It appears to have python bindings for the sqlite VFS layer and virtual tables too. That would be fun to experiment with.
- coleifer 9y agoYou might also like this library, which allows you to create virtual tables using a very simple API (2 functions). https://github.com/coleifer/sqlite-vtfunc https://github.com/coleifer/sqlite-vtfunc
- Xophmeister 9y agoI have also transitioned to APSW because of the transaction point you mentioned. I was trying — and pulling my hair out for some time — to get the stdlib SQLite3 transactions to work how they’re documented, but no combination of magic words seemed to so the trick. A bit of Googling and I come across APSW, which explicitly mentions that the stdlib is broken in this regard because, as you say, it’s paying lip service to the DB-API. (However, that somewhat implies to me that the DB-API must be broken for all transactional RDBMSs. Why is SQLite different?) Anyway, it’s a bit of extra work to install, because you have to compile it from source and there’s no, e.g., pip package. That’s not so difficult in the grand scheme of things. One thing I miss from the stdlib, however, is the adaptor and convertor interface for transparent type conversion between Python and SQLite; I ended up rolling my own for APSW.