Sqlite

Import a CSV file into SQLite sqlite til
sqlite3 CLI Using the sqlite3 CLI allows you to import CSV files into a new or existing database file. The following multi-line command will import the CSV file, disruptions.csv into the disruptions table. All columns will be of type TEXT if created by the import command. sqlite3 data.db <<EOS .mode csv .import disruptions.csv disruptions EOS The command infers the input file type by the output mode (.mode csv), otherwise use --csv in the import command. Read more...
Export a SQLite query results to CSV til sqlite
Exporting the results of a query can be useful to import it into other tools for data preparation or visualisation particularly as a CSV file. sqlite3 CLI Using the SQLite3 CLI allows you to set the query result mode to CSV and output that result to a file. A number of different output formats, including custom separators can be set as well. sqlite> .open links.db sqlite> .mode csv sqlite> .headers on sqlite> . Read more...
Using sqlite-diffable as a Python module til sqlite
sqlite-diffable is a tool, built by Simon Willison, to load and dump SQLite databases to JSON files. It’s intended to be used through the CLI, however since May I’ve been using this tool as a callable Python module to build my main site after making changes to the code1. This has allowed me to combine the build commands into one Python file. The changes aren’t available in the upstream code2 so it’ll have to be pulled from my fork if you’re looking to use this. Read more...
Using text embeddings to recommend similar articles til aws cloud sqlite
To show related content on this blog I use Hugo’s in-built functionality, which works surprisingly well with no setup. I did, however, want to test out creating text embeddings from my posts and rank them by similarity. Before you continue reading, the usual disclaimer: Do not take the information here as good or best practice. The purpose of this entry is to post my learnings in somewhat real-time. I will use Amazon Titan Embeddings G1 - Text available through Amazon Bedrock and SQLite to store the results. Read more...
Insert JSON objects into SQLite til sqlite
There have been a number of occasions where I needed to insert JSON objects into an SQLite database, the sqlite-utils Python library and CLI tool handled the task every time. I will be showcasing some of the JSON-inserting capabilities via CLI below. Inserting a single JSON object Here the object is stored in data.json, sqlite-utils takes in the insert command, the name of the database, the name of the table, the filename, and lastly, I also specify the column to use as the primary key. Read more...