apsw: Another Python SQLite Wrapper | Hacker News Reader

apsw: Another Python SQLite Wrapper

github.com

89 pointstosh33 comments

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.

Why do you say it's bad?

Everything to do with transactions, including nested transactions.

It's not even that good for switching to different engines.

My normal mode of operation is via postgres(I like the functions better than sqlite) But for a recent little program thought it would be nice to have it a little more self contained and allow for a sqlite db if wanted. Ignoring the the sql differences(Remember, I use postgres for it's functions, so there were many) sqlite uses a different parameter style(I ended up writing an automatic converter to transform my psycopg3 style "%(name)s" to sqlite3's ":name") and I had got in the (perhaps bad) habit of keeping an open connection and using a context manager on the cursor where sqlite does not like that and really wants the context manager on the connection. The final hoop to jump through was getting the row output as a dict. Not super relevant as it is not default on psycopg either, but it takes a bit more effort on sqlite3.

So yes it is possible to make the database swapable, But even with the dbapi, it is not trivial.

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)

what quirks did you bump into with sqlite3?

I don't recall exactly, other than having a hard time making sense of the transaction management.

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.

Thank you for the kind words. I was especially delighted when noticing that cysqlite also supports table valued functions in Python. I'm hoping they become a standard thing for Python developers using SQLite, because it makes bridging SQLite and Python even more productive.

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"

For most uses, SQL databases are easy to use and general enough to allow practically unlimited growth, "stringy queries" usually need less "wrangling" than accessing bare files correctly, and designing and implementing file formats is a luxury (usually done because optimizing for file size and compression is traditional).

Yea those go in the pros.

On the cons, you have sql injections, which can kill your company's reputation.

It only became less common by shifting the issue to the supply chain, now you need orms and yet another wrangling library. So I'm not convinced the security issue is "fixed", let alone the code complexity introduced by these things just to effectively do one of the most fundamental operations in computing, writing() to disk

You don't need an orm or some wrangling library to avoid sql injections. You just have to use parameterized queries. That's a bit cumbersome when using the bare C SQLite API, but in Python it's very easy.

I originally started out doing exactly what you said - dumping Python dictionaries into a file and reading/writing that. It worked well. Where it really hurt was I wanted to add undo functionality, and that means needing to store the before and after view of the data. And I wanted it more sophisticated than that - be able to view what the data looked like at any point in time.

That forced me to use a database, because now I could use triggers to automatically save off old values, and views to use them nicely.

There were many Python SQLite wrappers at the time, but I didn't like them. So I wrote another one :)

https://www.rogerbinns.com/blog/20-years-of-apsw.html

(I am the APSW maintainer)

One of the reasons is high resilience, practically state of the art as far as writing to a single storage device allows. You're less likely to end up with corrupted files with sqlite. This is much more difficult to get right when manipulating files yourself than you might think.

For your consideration (I'll add that I used to think similarly as you, and I still wouldn't use sqlite for everything, but it's shockingly effective):

https://www.sqlite.org/transactional.html

https://www.sqlite.org/fasterthanfs.html

https://www.sqlite.org/atomiccommit.html

https://www.sqlite.org/aff_short.html

https://www.sqlite.org/testing.html

https://danluu.com/file-consistency/

I remember working on https://github.com/fzakaria/sqlelf and I was curious about some performance;

Immediately the maintainer responded with a very thorough set of benchmarks and improvements!

The nicest part was that it turns out we lived in the same small-ish town and I didn't even know it.

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 ?

If you ask via the contacts such as Github discussions or email then I can help find a perfect solution for your needs - https://rogerbinns.github.io/apsw/about.html#mailing-lists-c...

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.

You can use apsw.ext.query_info to get details about a query without running it - see https://rogerbinns.github.io/apsw/ext.html#apsw.ext.query_in... which includes an example.

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.

(I am the APSW maintainer)

Thanks for the detailed explanation, I will try with apsw.ext.query_info. In my case I was building column-keyed dicts from results but as the result was an empty set, there was nothing to key on. Appreciate the pointer to the execution tracer too.

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.

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

Seems to even be in Debian as https://packages.debian.org/trixie/python3-apsw Nice!

The Linux and BSD flavours have APSW - see https://rogerbinns.github.io/apsw/install.html#linux-bsd-pro...

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

(I am the APSW maintainer)

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

The latest commit was 1 hour ago

Abandonware!

ahh. It’s been around a while, but maybe the date convention is for articles not repos.

new to many people, just not you.

It has been going since 2004. There is going to be a new release today. What year do you suggest :)

(I am the APSW author)

thanks for your work, it's awesome!

Reply on news.ycombinator.com