Online SQLite3 Compiler

SQLite is a database engine that keeps a whole database in one file, and Python's built-in sqlite3 module runs SQL against it. There is no server to install or start. Apps use it to store their data, and people use it to learn SQL and to keep small datasets. This page is an online SQLite compiler: the code runs in your browser, so you can try it without installing anything. Run the example first, then paste any snippet below into a new cell to try it.

What the example does

sqlite3.connect() opens the database file example.db and creates it if it doesn't exist. The code first deletes any old copy with os.remove(), so each run starts from an empty database. cursor.execute() runs one SQL statement, here CREATE TABLE IF NOT EXISTS. A column declared INTEGER PRIMARY KEY numbers itself, so the three users get the ids 1, 2 and 3. executemany() runs the INSERT once for each tuple in users, filling the two ? placeholders from it. conn.commit() saves the rows. The code then closes the connection, reopens the file and reads the rows back with fetchall(), which returns a list of tuples.

Insert rows with placeholders

Pass values with ? or named placeholders such as :title, never by pasting them into the SQL. An f-string would fail on the apostrophe in O'Connor with OperationalError: near "Connor": syntax error, and would let a user's text change the query. ":memory:" opens a database that is never written to a file:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE books (title TEXT, author TEXT, year INTEGER)")

books = [("Dune", "Frank Herbert", 1965), ("Emma", "Jane Austen", 1815)]
with conn:   # commits at the end of the block, or rolls back on an error
    conn.executemany("INSERT INTO books VALUES (?, ?, ?)", books)
    conn.execute("INSERT INTO books VALUES (?, ?, ?)",
                 ("Wise Blood", "Flannery O'Connor", 1952))
    conn.execute("INSERT INTO books VALUES (:title, :author, :year)",
                 {"title": "Beloved", "author": "Toni Morrison", "year": 1987})

for row in conn.execute("SELECT * FROM books WHERE year > ? ORDER BY year", (1900,)):
    print(row)

Group, filter and sort

GROUP BY returns one row per region, and HAVING filters those groups the way WHERE filters rows. Setting row_factory to sqlite3.Row lets you read each column by name:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute("CREATE TABLE sales (region TEXT, product TEXT, units INTEGER, price REAL)")
conn.executemany("INSERT INTO sales VALUES (?, ?, ?, ?)", [
    ("North", "Pen", 12, 1.2), ("South", "Pen", 7, 1.2), ("North", "Notebook", 3, 3.5),
    ("East", "Notebook", 9, 3.5), ("South", "Notebook", 14, 3.5), ("East", "Pen", 2, 1.2),
])

query = """
    SELECT region, SUM(units) AS units, ROUND(SUM(units * price), 2) AS revenue
    FROM sales
    GROUP BY region
    HAVING revenue > 25
    ORDER BY revenue DESC
"""
for row in conn.execute(query):
    print(row["region"], row["units"], row["revenue"])

North is left out: its revenue is 24.9, below the limit of 25.

Join two tables

This adds an orders table to the example's example.db and joins it to users. A LEFT JOIN keeps users who have no orders, and COALESCE() turns their missing total into 0:

import sqlite3

conn = sqlite3.connect("example.db")   # the file the example created
conn.execute("DROP TABLE IF EXISTS orders")
conn.execute("""CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
)""")
conn.executemany("INSERT INTO orders (user_id, total) VALUES (?, ?)",
                 [(1, 25.0), (1, 12.5), (3, 40.0)])
conn.commit()

query = """
    SELECT users.name, COUNT(orders.id) AS orders, COALESCE(SUM(orders.total), 0) AS spent
    FROM users LEFT JOIN orders ON orders.user_id = users.id
    GROUP BY users.id
    ORDER BY spent DESC
"""
for row in conn.execute(query):
    print(row)
conn.close()

Load query results into pandas

pd.read_sql_query() runs a query and returns a pandas DataFrame, and to_sql() writes a DataFrame back as a table. if_exists="replace" overwrites an existing table:

import sqlite3
import pandas as pd

conn = sqlite3.connect("example.db")
df = pd.read_sql_query("SELECT name, age FROM users WHERE age > ?", conn, params=(26,))
print(df)

df["age_next_year"] = df["age"] + 1
df.to_sql("users_next_year", conn, if_exists="replace", index=False)
print(conn.execute("SELECT * FROM users_next_year").fetchall())
conn.close()

Getting the file

Files your code saves, like example.db, appear in the sidebar's Data tab. Running code and inspecting the result is free. Downloading the files requires a Premium plan.

Good to know

• Closing a connection without conn.commit() throws away the changes since the last commit. with conn: commits for you, but leaves the connection open.
• SQLite does not check column types by default: an INTEGER column stores 'thirty' as text without an error. Add STRICT after the closing bracket of CREATE TABLE to get an IntegrityError instead.
• sqlite3.connect() creates an empty file for a name it can't find, so a typo in the file name shows up later as no such table.