Posts tagged: sql

sqlite-jiff I linked to the brand new Jiff datetime library yesterday. Alex Garcia has already used it for an experimental SQLite extension providing a timezone-aware jiff_duration() function - a useful new … Simon Willison’s Weblog · simonwillison.net Sqlite is getting rust extensions now, and datetimes make it totally worth if if they work well and and fast, two things that don’t always go together in datetime libraries

External Link stackoverflow.com Another interesting option for slow count queries in sqlite. If you haven’t DELETEd any records, doing:

Optimizing SQLite for servers SQLite is often misconceived as a "toy database", only good for mobile applications and embedded systems because it's default configuration is optimized for embedded use cases, so most... Sylvain Kerkour · kerkour.com Very interesting article by Sylvain, suggested by Simon Willison. Definitely some things that I want to come back and try later on. Here is the TLDR of the whole post This is interesting, and something I need to consider. I definitely have an application with slow count queries. I am not sure how to make it better as its not a full so a count table doesn’t work, nor does counting by index. I might need to have a table of cached results, and if a write matches the counter increase it, or update all counters on write. COUNT queries are slow SQLite doesn’t keep statistics about its indexes, unlike PostgreSQL, so COUNT queries are slow, even when using a WHERE clause on an indexed field: SQLite has to scan for all the matching records. One solution is to use a trigger on INSERT and DELETE that updates a running count in a separate table then query that separate table to find the latest count.

sqlite_utils Python library - sqlite-utils sqlite-utils.datasette.io sqlite-utils is primarily a cli tool for sqlite operations such as enabling full text search, and executing searches, but it also has a nice python api that is exposed and pretty straightforward to use. This returns a generator object that you can iterate over the row objects with.

External Link stackoverflow.com How to sort results from a sqlalchemy based orm. I needed this to enable paging on my thoughts api.

sqlite-utils now supports plugins sqlite-utils 3.34 is out with a major new feature: support for plugins. sqlite-utils is my combination Python library and command-line tool for manipulating SQLite databases. It recently celebrated... Simon Willison’s Weblog · simonwillison.net As the title states sqlite-utils now supports plugins. I dug in just a bit and Simon implemented this completely with entrypoints, no framework or library at all.

Column INSERT/UPDATE Defaults — SQLAlchemy 1.4 Documentation docs.sqlalchemy.org sqlalchemy server_defaults end up as defaults in the database when new values are inserted.

sqlite-utils command-line tool - sqlite-utils sqlite-utils.datasette.io I want to like jq, but I think Simon is selling me on sqlite, maybe its just me but this looks readable, hackable, editable, memorizable. Everytime I try jq, and its 5 minutes fussing with it just to get the most basic thing to work. I know enough sql out of the gate to make this work off the top of my head