SQLite Export to CSV, JSON, Excel and SQL Dumps

The sqlite3 command-line shell does the exporting. .mode picks the output format, .headers on adds the column names, and .output or .once sends the results to a file instead of the screen. From your own shell, flags do the same in one line.

sqlite3 -header -csv shop.db "SELECT * FROM users;" > users.csv

Every command below gives the same result on SQLite 3.43, 3.50 and 3.54 unless a section says otherwise. Relative file names are resolved from the folder you started sqlite3 in.

Export to CSV

Inside the shell, set the mode and headers, then point the output at a file. .output keeps writing to the file until you run .output on its own; .once captures only the next query and then goes back to the screen.

Example: export three columns of the users table to a CSV with a header.

.headers on
.mode csv
.output users.csv
SELECT id, name, email FROM users;
.output

The file starts with id,name,email, then rows such as 1,"Orrin Schiller",[email protected]. If the folder does not exist, sqlite3 stops with Error: cannot open "/nope/users.csv".

Export a query to CSV

Any SELECT can go to a file, so filtering, joining and grouping happen before the export. .once is the easiest way to save one result without changing where later output goes.

Example: count audit events per action and save the summary.

.headers on
.mode csv
.once action_counts.csv
SELECT action, count(*) AS events, max(created_at) AS last_seen
FROM audit_log
GROUP BY action
ORDER BY events DESC;

The header row uses the names from the SELECT list, which is why count(*) gets an alias here.

Export all tables to CSV

The shell has no command for every table at once, but sqlite_schema lists them, so a short loop in your own shell writes one CSV per table. The NOT LIKE 'sqlite_%' filter skips SQLite's internal tables such as sqlite_sequence.

for t in $(sqlite3 shop.db "SELECT name FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"); do
  sqlite3 -header -csv shop.db "SELECT * FROM \"$t\";" > "$t.csv"
done

On the demo database this writes app_settings.csv, audit_log.csv, feature_rollout.csv, migrations.csv and users.csv, each with a header row.

Export to JSON

.mode json writes each result as one JSON array of objects, and the -json flag does the same from your own shell.

sqlite3 -json shop.db "SELECT key, value FROM app_settings WHERE key LIKE 'app.%'" > app_settings.json

Two things catch people out. A query that returns no rows writes nothing at all, not [], and two SELECT statements in a row write two separate arrays, which is not valid JSON as a single file. Building the JSON in SQL with json_group_array and json_object avoids both, because it always returns exactly one value.

Example: export audit events whose details column already holds JSON text.

SELECT json_group_array(
  json_object('id', id, 'action', action, 'details', json(details))
)
FROM (SELECT * FROM audit_log WHERE details IS NOT NULL ORDER BY id LIMIT 2);

Run it with .mode list and .once events.json to save the result. The json() call matters: without it, .mode json and json_object both write the details as an escaped string ("{\"field\":\"title\"...}") instead of a nested object.

Export to Excel

SQLite cannot write .xlsx files, so the usual route is CSV. Add --bom to .once or .output so Excel reads the file as UTF-8 instead of guessing the encoding, which garbles accented names.

.headers on
.mode csv
.once --bom users_excel.csv
SELECT id, name, email FROM users;

.excel goes one step further: it writes the next result to a temporary CSV and opens it in your default spreadsheet app. It also takes --bom. Run .headers on first so the sheet gets column names.

.headers on
.excel
SELECT id, name, email FROM users;

For a real .xlsx file with one sheet per table, use pandas (see the Python section).

Dump to SQL with .dump

.dump writes the database as SQL text: PRAGMA foreign_keys=OFF, then every CREATE statement and one INSERT per row inside a single transaction. Load it with input redirection to rebuild the database.

sqlite3 shop.db .dump > shop.sql
sqlite3 restored.db < shop.sql

.dump users dumps one table, and the argument is a LIKE pattern, so .dump audit% matches every table that starts with audit. The pattern is matched against object names, so .dump audit_log leaves out the table's indexes and triggers. Name them too when you need them:

.output audit_log.sql
.dump audit_log idx_audit_log%
.output

Loading a dump into a database that already has the table fails with Parse error near line 3: table users already exists. Start from a new file, or drop the table first.

SQLite 3.54 writes REAL values in a dump without the trailing .0 (100 rather than 100.0 in 3.50). The column's REAL type converts them back on load, so the restored data is the same.

Schema only and data only

.schema prints every CREATE statement without data, and .schema users prints one table. Add --nosys when you plan to load the file, because the plain output includes CREATE TABLE sqlite_sequence(name,seq);, which fails on load with object name reserved for internal use: sqlite_sequence.

.output schema.sql
.schema --nosys
.output

.dump --data-only writes only the INSERT statements, ready to load into a database that already has the schema.

sqlite3 shop.db ".dump --data-only" > data.sql
sqlite3 new.db < schema.sql
sqlite3 new.db < data.sql

The data file also restores the AUTOINCREMENT counters in sqlite_sequence, and the versions differ here. A 3.50 file appends the counter rows, so the new database ends up with each one twice. That is untidy but harmless: the next insert still got id 26 after 25 rows. A 3.54 file clears them first, but loading it prints Parse error near line 262: object name reserved for internal use: sqlite_sequence and exits with status 1, even though every row and counter loads.

Copy the database with .backup or VACUUM INTO

For a full copy you can open straight away, copy the file itself rather than its SQL. .backup uses SQLite's online backup API, so it is safe while other connections are writing, and it overwrites the target if it exists. VACUUM INTO is plain SQL, so it works from any client, and it writes a compacted copy without free pages.

.backup shop-backup.db

VACUUM INTO 'shop-copy.db';

VACUUM INTO refuses to overwrite: if the file exists it stops with output file already exists. Both also work on an in-memory database, which is the simplest way to save one to disk.

From Python

Python's built-in sqlite3 module covers CSV, JSON, dumps and backups without extra packages. The CSV header comes from cursor.description.

import csv
import json
import sqlite3

con = sqlite3.connect("shop.db")
cur = con.execute("SELECT id, name, email FROM users ORDER BY id")

with open("users.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow([col[0] for col in cur.description])
    writer.writerows(cur)

con.row_factory = sqlite3.Row
rows = [dict(r) for r in con.execute("SELECT * FROM app_settings")]
with open("app_settings.json", "w", encoding="utf-8") as f:
    json.dump(rows, f, indent=2)

with open("shop.sql", "w", encoding="utf-8") as f:
    for line in con.iterdump():
        f.write(line + "\n")

backup = sqlite3.connect("shop-backup.db")
con.backup(backup)
backup.close()

iterdump() gives the same kind of SQL as .dump, and con.backup() is the same API as the shell's .backup.

Example: write three tables to one Excel workbook, one sheet each. This needs pip install pandas openpyxl.

import sqlite3
import pandas as pd

con = sqlite3.connect("shop.db")
with pd.ExcelWriter("shop.xlsx") as writer:
    for table in ["users", "audit_log", "app_settings"]:
        df = pd.read_sql_query(f'SELECT * FROM "{table}"', con)
        df.to_excel(writer, sheet_name=table, index=False)
con.close()