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 [1] 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 Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://simonwillison.net/2024/Jul/23/sqlite-jiff/ [2]: /thoughts/

External Link stackoverflow.com [1] Another interesting option for slow count queries in sqlite. If you haven’t DELETEd any records, doing: SELECT MAX(ROWID) FROM "table" LIMIT 1; Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://stackoverflow.com/questions/8988915/sqlite-count-slow-on-big-tables [2]: /thoughts/

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 [1] 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 PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL; PRAGMA cache_size = 1000000000; PRAGMA foreign_keys = true; PRAGMA temp_store = memory; 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 count(*) 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...

sqlite_utils Python library - sqlite-utils sqlite-utils.datasette.io [1] 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. from sqlite_utils import Database db = Database("database.db") db["post"].enable_fts(["title", "message", "tags]) db["post"].search("water") This returns a generator object that you can iterate over the row objects with. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://sqlite-utils.datasette.io/en/stable/python-api.html#full-text-search [2]: /thoughts/

External Link stackoverflow.com [1] How to sort results from a sqlalchemy based orm. .order_by(model.Entry.amount.desc()) I needed this to enable paging on my thoughts api. @post_router.get("/posts/") async def get_posts( *, request: Request, session: Session = Depends(get_session), hx_request: Annotated[str | None, Header()] = None, accept: Annotated[str | None, Header()] = None, current_user: Annotated[User, Depends(try_get_current_active_user)], page_size: int = 10, page: int = 1, ) -> Posts: "get all posts" statement = ( select(Post) .where(Post.published) .order_by(Post.id.desc()) .limit(page_size) .offset((page - 1) * page_size) ) posts = session.exec(statement).all() posts = Posts(__root__=posts) if isinstance(current_user, RedirectResponse): is_logged_in = False else: is_logged_in = True if hx_request and page == 1 and len(posts.__root__) == 0: return HTMLResponse('<ul id="posts"><li>No posts</li></ul>') if hx_request and len(posts.__root__) == 0: return HTMLResponse("") if not hx_request and len(posts.__root__) == 0: return ["no posts"] if hx_request: return templates.TemplateResponse( "posts.html", { "request": request, "config":...

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 [1] 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. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://simonwillison.net/2023/Jul/24/sqlite-utils-plugins/ [2]: /thoughts/

Column INSERT/UPDATE Defaults — SQLAlchemy 1.4 Documentation docs.sqlalchemy.org [1] sqlalchemy server_defaults end up as defaults in the database when new values are inserted. t = Table( "test", metadata_obj, Column("abc", String(20), server_default="abc"), Column("created_at", DateTime, server_default=func.sysdate()), Column("index_value", Integer, server_default=text("0")), ) CREATE TABLE test ( abc varchar(20) default 'abc', created_at datetime default sysdate, index_value integer default 0 ) Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://docs.sqlalchemy.org/en/14/core/defaults.html#server-invoked-ddl-explicit-default-expressions [2]: /thoughts/

sqlite-utils command-line tool - sqlite-utils sqlite-utils.datasette.io [1] 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 curl https://thoughts.waylonwalker.com/posts/ | sqlite-utils memory - 'select title, message from stdin where stdin.tags like "%python%"' | jq Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://sqlite-utils.datasette.io/en/stable/cli.html#querying-data-directly-using-an-in-memory-database [2]: /thoughts/