Farshid/Ashouri

sqlite

61 posts tagged “sqlite”.

  1. 2023

  2. Compile and run a new SQLite version with the existing sqlite3 Python library on macOS

    I've been trying to figure this out for years. Previous notes include Using LD PRELOAD to run any version of SQLite with Python (Linux only), and Building a specific version of SQLite with pysqlite on…

    sqlite python macos compilation environment-variables
    1 min read

  3. Compiling the SQLite spellfix.c module on macOS

    I wanted to browse a backup copy of my Plex database, which is a SQLite file. I tried this: The module is an optional module for SQLite.

    sqlite compilation macos datasette extensions
    1 min read

  4. List all columns in a SQLite database

    Here's a devious trick for listing ALL columns in a SQLite database, using a SQL query that generates another SQL query.

    TIL sqlite
    4 min read

  5. Null case comparisons in SQLite

    I wanted to say "output this transformed value if it's not null, otherwise nothing". The recipe I figured out was: Full query example: This uses datasette-rure for the function.

    TIL sqlite
    1 min read

  6. Copy tables between SQLite databases

    I figured out a pattern for doing this today using the CLI tool - given two SQLite databases in the current folder, called and : I'm using that in this GitHub Actions workflow.

    sqlite database cli bash data-transfer
    1 min read

  7. Returning related rows in a single SQL query using JSON

    When building database-backed applications you'll often find yourself wanting to return a row from the database along with its related rows.

    TIL sqlite
    1 min read

  8. Counting SQLite virtual machine operations

    When SQLite executes a query, it does so by executing a sequence of virtual machine operations. There are mechanisms for cancelling a query after a specific number of these operations, to protect…

    sqlite database performance cli python
    1 min read

  9. Fixing broken text encodings with sqlite-transform and ftfy

    I was working with a database table that included values that were clearly in the wrong character encoding - values like this: Rue Léopold I I used my sqlite-transform tool with the ftfy Python…

    sqlite encoding data-cleaning python cli
    1 min read

  10. Trying out cr-sqlite on macOS

    cr-sqlite is fascinating. It's a loadable SQLite extension by Matt Wonlaw that "allows merging different SQLite databases together that have taken independent writes".

    TIL sqlite
    3 min read

  11. Using LD_PRELOAD to run any version of SQLite with Python

    I've been wanting to figure this out for ages. This thread on Hacker News plus this Stackoverflow post gave me some initial clues.

    TIL sqlite
    3 min read

  12. Deno KV

    Initial article: April 28th 2023 - see below for an update I got intrigued by Deno KV, which describes itself as "a global database for global apps".

    deno deno-kv sqlite database key-value-store
    4 min read

  13. Loading SQLite extensions in Python on macOS

    I finally found a workaround for this error when attempting to load a SQLite extension in Python on macOS: The fix is to install Python using Homebrew, and then use that version of Python.

    TIL sqlite
    1 min read

  14. Consecutive groups in SQL using window functions

    I have a database table with all of my Swarm checkins since 2011, created using my swarm-to-sqlite tool.

    sql window-functions database data-analysis sqlite
    3 min read

  15. Finding the largest SQLite files on a Mac

    This runs using Spotlight so it's really fast: mdfind "kMDItemDisplayName == .sqlite" -0 xargs -0 stat "-f%z %N" sort -nr head -n 20 I have a lot of files in my Dropbox so I excluded those like this…

    macos sqlite cli filesystem productivity
    1 min read

  16. Using pysqlite3 on macOS

    While trying to use pysqlite3 on macOS I got the following error: Thanks to this tip on Stack Overflow I found that installing a fresh SQLite with and then running the following first fixed the error…

    TIL sqlite
    1 min read

  17. Tracking SQLite table history using a JSON audit log

    Today I decided to try a slightly different approach, using JSON to store changes to a table in an audit log.

    TIL sqlite
    3 min read

  18. Ordered group_concat() in SQLite

    I was trying to use to glue together some column values into a stiched together Markdown document. My first attempt looked like this: This attempt didn't work, because the order of the elements…

    TIL sqlite
    1 min read

  19. Building a specific version of SQLite with pysqlite on macOS/Linux

    I wanted the ability to test my Python software against specific version of SQLite on macOS. I found a way to do that using pysqlite3.

    sqlite python macos linux compilation
    1 min read

  20. Comparing two training datasets using sqlite-utils

    WizardLM is "an Instruction-following LLM Using Evol-Instruct". It's a fine-tuned model on top of Meta's LLaMA.

    llm ai data-analysis sqlite sqlite-utils
    3 min read

  21. Browse files (including SQLite databases) on your iPhone with ifuse

    I spotted an intriguing note in the release notes for osxphotos 0.51.7: Added ability to read Photos.sqlite from iPhone This lead me to osxphotos issue 745 which lead me to How to access iPhone files…

    iphone macos sqlite cli debugging
    1 min read