Export a SQLite query results to CSV

Posted: | Tags: til sqlite

Exporting the results of a query can be useful to import it into other tools for data preparation or visualisation particularly as a CSV file.

sqlite3 CLI

Using the SQLite3 CLI allows you to set the query result mode to CSV and output that result to a file. A number of different output formats, including custom separators can be set as well.

sqlite> .open links.db
sqlite> .mode csv
sqlite> .headers on
sqlite> .output file.csv
sqlite> select * from links;
sqlite> .quit

DB Browser for SQLite

Export to CSV option from the file save icon.

Export to CSV option from the file save icon.

In th “Execute SQL” tab, clicking the page and floppy icon, will allow you to save the query results as CSV. From the export window you can then specify if you’d like to include column names and what field separator, quote character, and new line characters you’d like to use.

Python

Saving the output for a SQLite query using Python can be done with the built-in CSV library. After crafting and executing the query, open a CSV file and establish a writer. You can then choose whether or not you want the headers by writing them using the cursor’s description. Keeping with the Python Database API Specification it each column name is returned in a typle of 7 elements, six of which are None. See the full code sample below for guidance.

import sqlite3
import csv

connection = sqlite3.connect("./links.db")
cursor = connection.cursor()

cursor.execute("SELECT * FROM links;")
data = cursor.fetchall()

with open("table.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow([header[0] for header in cursor.description])
    writer.writerows(data)

Related ramblings