sqlite-utils is a Python library (and command-line tool for quickly creating and manipulating SQLite database files.
This tutorial will show you how to use the Python library to manipulate data.
Installation
To install the library, run:
pip install sqlite-utils
You can run this in a Jupyter notebook cell by executing:
%pip install sqlite-utils
Or use pip install -U sqlite-utils to ensure you have upgraded to the most recent version.
%pip install -U sqlite_utils
Requirement already satisfied: sqlite_utils in /usr/local/Cellar/jupyterlab/3.0.16_1/libexec/lib/python3.9/site-packages (3.14)
Requirement already satisfied: click-default-group in /usr/local/lib/python3.9/site-packages (from sqlite_utils) (1.2.2)
Requirement already satisfied: sqlite-fts4 in /usr/local/lib/python3.9/site-packages (from sqlite_utils) (1.0.1)
Requirement already satisfied: click in /Users/simon/Library/Python/3.9/lib/python/site-packages (from sqlite_utils) (7.1.2)
Requirement already satisfied: tabulate in /usr/local/lib/python3.9/site-packages (from sqlite_utils) (0.8.7)
Requirement already satisfied: python-dateutil in /usr/local/Cellar/jupyterlab/3.0.16_1/libexec/lib/python3.9/site-package (from sqlite-utils) (2.8.1)
Requirement already satisfied: six>=1.5 in /usr/local/Cellar/jupyterlab/3.0.16_1/libexec/lib/python3.9/site-package (from python-dateutil->sqlite-utils) (1.16.0)
WARNING: You are using pip version 21.1.1; however, version 21.2.2 is available.
You should consider upgrading via the '/usr/local/Cellar/jupyterlab/3.0.16_1/libexec/bin/python3.9 -m pip install --upgrade pip' command.
Note: you may need to restart the kernel to use updated packages.
import sqlite_utils
You can use the library with a database file on disk by running:
db = sqlite_utils.Database("path/to/my/database.db")
In this tutorial we will use an in-memory database. This is a quick way to try out new things, though you should note that when you close the notebook the data store in the in-memory database will be lost.
db = sqlite_utils.Database(memory=True)db
<Database <sqlite3.Connection object at 0x139a16300>>
Creating a table
We are going to create a new table in our database called creatures by passing in a Python list of dictionaries.
db[name_of_table] will access a database table object with that name.
Inserting data into that table will create it if it does not already exist.
for row in db.query("select name, species from creatures"):print(f'{row["name"]} is a {row["species"]}')
Cleo is a dog
Lila is a chicken
Bants is a chicken
SQL parameters
You can run a parameterized query using ? as placeholders and passing a list of variables. The variables you pass will be correctly quoted, protecting your code from SQL injection vulnerabilities.
list(db.query("select * from creatures where age > ?", [1.0]))
[{'name': 'Cleo', 'species': 'dog', 'age': 6.0}]
As an alternative to question marks we can use :name parameters and feed in the values using a dictionary:
list(db.query("select * from creatures where species = :species", {"species": "chicken"}))
When we created this table we did not specify a primary key. SQLite automatically creates a primary key called rowid if no other primary key is defined.
We can run select rowid, * from creatures to see this hidden primary key:
We can use a join SQL query to combine data from these two tables:
list(db.query(""" select creatures.id, creatures.name, creatures.age, species.id as species_id, species.species from creatures join species on creatures.species_id = species.id"""))