Farshid/Ashouri

sqlite

61 posts tagged “sqlite”.

  1. 2024

  2. Lag window function in SQLite

    Here's how to use a lag window function to calculate new cases per day when the table just has total cases over time on different dates.

    TIL sqlite
    1 min read

  3. One-liner for running queries against CSV files with SQLite

    I figured out how to run a SQL query directly against a CSV file using the command-line utility: sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' \ 'SELECT passenger count, COUNT( )…

    TIL sqlite
    1 min read

  4. Saving an in-memory SQLite database to a file in Python

    I was messing around in Python with an in-memory SQLite database, when I decided I actually wanted to save my experimental database to a file so I could explore it using Datasette.

    TIL sqlite
    1 min read

  5. How to read Hacker News threads with most recent comments first

    Hacker News displays comments in a tree. This can be frustrating if you want to keep track of a particular conversation, as you constantly have to seek through the tree to find the latest comment.

    hacker-news api json datasette sqlite
    3 min read

  6. Comparing database rows before and after with SQLite JSON functions

    Here's a trick I've been using to compare the rows in a table before and after I perform an operation against it. It works well for a few hundred (and maybe a few thousand) rows.

    sqlite sql json database data-analysis
    1 min read

  7. The simplest recursive CTE

    I found this really simple recursive CTE useful for ensuring I understood how to write recursive CTEs. This query returns five rows from a single column - from 0 to 4.

    TIL sqlite
    1 min read

  8. SQLite VACUUM: database or disk is full

    I was trying to run against a large SQLite database file ( 7GB) using and I got this error: sqlite3.OperationalError: database or disk is full The volume that the database lived in was 20GB in size…

    TIL sqlite
    1 min read

  9. Enabling WAL mode for SQLite database files

    I was getting occasional messages from a Datasette instance that was running against a bunch of different SQLite files that were updated by cron scripts (my personal Dogsheep).

    sqlite database wal performance cli
    1 min read

  10. Sort by number of JSON intersections

    This post on Reddit asked how to run a query that takes a list of items (in this case ingredients) as the input and returns all rows with at least one of those items in a JSON list, ordered by the…

    TIL sqlite
    1 min read

  11. SQLite aggregate filter clauses

    SQLite supports aggregate filter clauses (as of 3.30.0, released 2019-10-04), as described in this SQL Pivot in all databases tutorial.

    TIL sqlite
    1 min read

  12. SQLite can use more than one index for a query

    I was trying to figure out if SQLite has the ability to use more than one index as part of executing a single query, or if it only ever picks a single index that it thinks will give the best…

    TIL sqlite
    1 min read

  13. SQLite timestamps with floating point seconds

    Today I learned about this: Which outputs: Note the seconds component which reads - that's what you get from the format string.

    TIL sqlite
    1 min read

  14. Querying for items stored in UTC that were created on a Thursday in PST

    This came up as a question on Hacker News. How can you query a SQLite database for items that were created on a Thursday in PST, when the data is stored in UTC?

    TIL sqlite
    1 min read

  15. Export a Mastodon timeline to SQLite

    I've been playing around with the Mastodon timelines API. It's pretty fun! I'm running my own instance which means I don't feel limited by politeness in terms of rate limits - if I'm just using my own…

    mastodon api sqlite sqlite-utils cli
    1 min read

  16. Figuring out if a text value in SQLite is a valid integer or float

    Given a table with a column in SQLite I want to figure out if every value in that table is actually the text representation of an integer or floating point value, so I can decide if it's a good idea…

    sqlite sql data-types validation database
    1 min read

  17. The SQLite now argument is stable within the same query

    I stumbled across an interesting little detail of SQLite today, running the following query: That pattern is described here and in this TIL, it returns the current Unix timestamp in milliseconds.

    TIL sqlite
    1 min read
  18. 2023

  19. Compile a new sqlite3 binary on Ubuntu

    I wanted to try the backup command that was released in SQLite3 3.27.0 on 2019-02-07. Ubuntu 18.04.4 LTS has SQLite 3.22.0 from 2018-01-22.

    sqlite ubuntu linux compilation cli
    1 min read

  20. Splitting on commas in SQLite

    I had an input string in format and I needed to split it into three separate values in SQLite. I managed to do it using a confusing combination of the and functions.

    TIL sqlite
    1 min read

  21. Atuin for zsh shell history in SQLite

    Atuin (via Rhet Turnbull) "replaces your existing shell history with a SQLite database". Obviously I had to try this out!

    zsh shell history sqlite atuin
    1 min read

  22. Track timestamped changes to a SQLite table using triggers

    This is more of a "today I figured out" than a TIL. I have an idea to implement the Denormalized Query Engine design pattern using SQLite triggers.

    TIL sqlite
    1 min read