6/24/2023 0 Comments Sqlite command lineAuthor Phil Eaton compiled a collection of benchmarks of other similar tools, and his benchmarking script demonstrates how to use each one of them. It's a LOT slower than using sqlite3 directly though.ĭsq is a tool that does this kind of thing (and a lot more). My own sqlite-utils memory command can load data from JSON, CSV or TSV into an in-memory database and run a query against it. There are a whole bunch of other tools that can be used for this kind of thing! Or use -cmd '.mode markdown' to get a Markdown table: passenger_countĪ full list of output modes can be seen like this: % sqlite3 -cmd '.help mode'Īscii Columns/rows delimited by 0x1F and 0x1Eīox Tables using unicode box-drawing characters Passenger_count COUNT(*) AVG(total_amount) dump command inside the sqlite3 prompt to export your SQLite database as an SQL file, you can restore the state of the database by. The output looks like this: "",128020,32.2371511482553Īdd -cmd '.mode column' to output in columns instead: $ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' -cmd '.mode column' \ I figured this out while commenting on this issue. You can get taxi.csv by downloading the compressed file from here and running: 7z e -aos taxi.csv.7z import -csv like this (thanks, Mark Lawrence): sqlite3 :memory: -cmd '.import -csv taxi.csv taxi' \ Then it uses two -cmd options to turn on CSV mode and import the taxi.csv file into a table called taxi. This uses the special :memory: filename to open an in-memory database. 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count' I figured out how to run a SQL query directly against a CSV file using the sqlite3 command-line utility: sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' \ The sqlite-utils command-line tool can be used to manipulate SQLite databases in a number of different ways. provides a viewer for exploring the database data and an editor for executing SQL commands. Simon Willison’s TILs One-liner for running queries against CSV files with SQLite To work with SQLite database manually from the command-line. The SQLite library includes a simple command-line utility named sqlite3 (or sqlite3.exe on windows) that allows the user to manually enter and execute SQL. This document provides a brief introduction on how to use the sqlite3program. ![]() One-liner for running queries against CSV files with SQLite | Simon Willison’s TILs The SQLite project provides a simple command-line program named sqlite3(or sqlite3.exeon Windows) that allows the user to manually enter and execute SQL statements against an SQLite database or against a ZIP archive.
0 Comments
Leave a Reply. |