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.
somat 11 hours ago [-]
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.
overflowy 16 hours ago [-]
Why do you say it's bad?
Retr0id 15 hours ago [-]
Everything to do with transactions, including nested transactions.
I've been using it a lot over the last year or two and it's worked great.
arc-in-space 17 hours ago [-]
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.
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.
dex0shubham 14 hours ago [-]
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 ?
mont_tag 18 hours ago [-]
Thank you HackerNews. I learned something new and will be using APSW for my next Sqlite project.
TZubiri 6 hours ago [-]
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"
HelloNurse 3 hours ago [-]
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).
TZubiri 54 minutes ago [-]
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
scosman 18 hours ago [-]
It’s a great wrapper, but we should add a year. Not exactly new.
Retr0id 17 hours ago [-]
The latest commit was 1 hour ago
arcanemachiner 17 hours ago [-]
Abandonware!
scosman 16 hours ago [-]
ahh. It’s been around a while, but maybe the date convention is for articles not repos.
gjvc 15 hours ago [-]
new to many people, just not you.
tomyinxkd 5 hours ago [-]
[flagged]
Rendered at 11:32:34 GMT+0000 (UTC) with Wasmer Edge.
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.
I've been using it a lot over the last year or two and it's worked great.
(This documentation page explains the library's intent: https://rogerbinns.github.io/apsw/pysqlite.html)
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.
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.
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"
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