sqlite-utils + litecli

sqlite-utils is a CLI tool for manipulating SQLite databases. I’ve used sqlite-utils and shot-scraper in the past to port my blog from PostHaven to this static site. Recently sqlite-utils added plugin support and Simon the creator of sqlite-utils wrote a few plugins to showcase the plugin system. One of them is a sqlite-utils-litecli plugin which launches an interactive shell with litecli. One obvious advantage is litecli has auto-completion and syntax highlighting which is a huge improvement over the default sqlite3 shell....

July 26, 2023 · 1 min

Python at Netflix

Zoran and I were guests on the Talk Python Podcast to discuss how Python is used at Netflix. The host of the podcast Michael Kennedy was well prepared with the background context and led the conversation in interesting ways. We got to cover a ton of different use cases at Netflix that use Python. I got to talk about some of my favorite OSS projects (bpython, pdb++, dbcli etc). We ran out of time before we could talk about pickley but we did mention it during the episode....

June 30, 2023 · 1 min

Finding the most frequent value in a SQLite column

I have a table with a list of trails that I’ve biked. I wanted to find out which trail I’ve biked the most. I used the following SQL query to find out: SELECT name, count(name) AS ct FROM trails GROUP BY name ORDER BY ct DESC LIMIT 5 This gave me the 5 trails I’ve biked the most. Translated to SQLAlchemy. from sqlalchemy import func def most_common_trails(db: Session): return ( db....

June 9, 2023 · 1 min

Updating a SQLite row with a value from another row

I had a table with a bunch of users and one of the users is a demo user and I wanted to copy the login token from my row to the demo user. This is how I managed to do that. UPDATE users SET token = (SELECT token FROM users u WHERE u.id = 1) WHERE id = 2; This will update the token column for the user with the id 2 with the value from the user with the id 1....

June 9, 2023 · 1 min

Vector Search

Recently I learned about a new kind of search called Vector Search or Semantic Search. This is a search technique that tries to find documents that match the meaning of the user’s search term instead of trying to match keywords like a Full Text Search (FTS). I wanted to try Semantic Search for my blog. I came across Alex Garcia’s post about a new SQLite extension for Vector Search called sqlite-vss....

June 1, 2023 · 6 min