Repository files navigation

Python CLI utility and library for manipulating SQLite databases.

• Pipe JSON (orCSV or TSV) directly into a new SQLite database file, automatically creating a table with the appropriate schema
• Run in-memory SQL queries, including joins, directly against data in CSV, TSV or JSON files and view the results
• Configure SQLite full-text search against your database tables and run search queries against them, ordered by relevance
• Runtransformations against your tables to make schema changes that SQLiteALTER TABLE does not directly support, such as changing the type of a column
• Extract columns into separate tables to better normalize your existing data
• Install plugins to add custom SQL functions and additional features

Read more on my blog, in this series of posts onNew features in sqlite-utils and otherentries tagged sqliteutils.

pip install sqlite-utils

Or if you useHomebrew for macOS:

brew install sqlite-utils

Now you can do things with the CLI utility like this:

$ sqlite-utils memory dogs.csv "select * from t"[{"id": 1, "age": 4, "name": "Cleo"}, {"id": 2, "age": 2, "name": "Pancakes"}]$ sqlite-utils insert dogs.db dogs dogs.csv --csv[####################################]  100%$ sqlite-utils tables dogs.db --counts[{"table": "dogs", "count": 2}]$ sqlite-utils dogs.db "select id, name from dogs"[{"id": 1, "name": "Cleo"}, {"id": 2, "name": "Pancakes"}]$ sqlite-utils dogs.db "select * from dogs" --csvid,age,name1,4,Cleo2,2,Pancakes$ sqlite-utils dogs.db "select * from dogs" --table  id    age  name----  -----  --------   1      4  Cleo   2      2  Pancakes

You can import JSON data into a new database table like this:

$ curl https://api.github.com/repos/simonw/sqlite-utils/releases \    | sqlite-utils insert releases.db releases - --pk id

Or for data in a CSV file:

$ sqlite-utils insert dogs.db dogs dogs.csv --csv

sqlite-utils memory lets you import CSV or JSON data into an in-memory database and run SQL queries against it in a single command:

$ cat dogs.csv | sqlite-utils memory - "select name, age from stdin"

See thefull CLI documentation for comprehensive coverage of many more commands.

You can alsoimport sqlite_utils and use it as a Python library like this:

importsqlite_utilsdb=sqlite_utils.Database("demo_database.db")# This line creates a "dogs" table if one does not already exist:db["dogs"].insert_all([    {"id":1,"age":4,"name":"Cleo"},    {"id":2,"age":2,"name":"Pancakes"}],pk="id")

Check out thefull library documentation for everything else you can do with the Python library.

• Datasette: A tool for exploring and publishing data
• csvs-to-sqlite: Convert CSV files into a SQLite database
• db-to-sqlite: CLI tool for exporting a MySQL or PostgreSQL database as a SQLite file
• dogsheep: A family of tools for personal analytics, built on top ofsqlite-utils