Welcome to a tutorial on how to export a database to a CSV file. So you need to create a report from the database? Well, it’s a simple “select from the database, write to CSV file” process – Read on for the example!
TLDR – QUICK SLIDES
Fullscreen Mode – Click Here
TABLE OF CONTENTS
PYTHON EXPORT DATABASE TO CSV
All right, let us now get into the example of exporting an SQLite database to a CSV file.
QUICK SETUP
The “usual stuff”:
- Create a virtual environment
virtualenv venv
. - Activate it –
venv/Scripts/activate
(Windows)venv/bin/activate
(Linux/Mac)
PART 1) DUMMY DATABASE
1A) USERS TABLE
CREATE TABLE `users` (
`id` INTEGER NOT NULL,
`email` TEXT NOT NULL,
`name` TEXT NOT NULL,
PRIMARY KEY("id" AUTOINCREMENT)
);
INSERT INTO `users` (`id`, `email`, `name`) VALUES
(1, 'Jane Doe', 'jane@doe.com'),
(2, 'Joe Doe', 'joe@doe.com'),
(3, 'John Doe', 'john@doe.com'),
(4, 'Julie Doe', 'julie@doe.com'),
(5, 'Johan Doe', 'johan@doe.com'),
(6, 'Joanne Doe', 'joanne@doe.com'),
(7, 'Juliet Doe', 'juliet@doe.com'),
(8, 'June Doe', 'june@doe.com'),
(9, 'Juan Doe', 'juan@doe.com'),
(10, 'Jamir Doe', 'jamir@doe.com'),
(11, 'Jaden Doe', 'jaden@doe.com'),
(12, 'James Doe', 'james@doe.com'),
(13, 'Janus Doe', 'janus@doe.com'),
(14, 'Jason Doe', 'jason@doe.com'),
(15, 'Jay Doe', 'jay@doe.com'),
(16, 'Jeff Doe', 'jeff@doe.com'),
(17, 'Jenn Doe', 'jenn@doe.com'),
(18, 'Joah Doe', 'joah@doe.com'),
(19, 'Joyce Doe', 'joyce@doe.com'),
(20, 'Joy Doe', 'joy@doe.com'),
(21, 'Juke Doe', 'juke@doe.com'),
(22, 'Johnnie Doe', 'johnnie@doe.com'),
(23, 'Jim Doe', 'jim@doe.com'),
(24, 'Jess Doe', 'jess@doe.com'),
(25, 'Jabril Doe', 'jabril@doe.com');
For this example, we will work with this dummy users table. Pretty self-explanatory.
id
User ID. Primary key and auto-increment.name
User’s name.email
User’s email.
1B) CREATE DATABASE
# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error
# (B) DATABASE + SQL FILE
DBFILE = "users.db"
SQLFILE = "S1A_users.sql"
# (C) DELETE OLD DATABASE IF EXIST
if os.path.exists(DBFILE):
os.remove(DBFILE)
# (D) IMPORT SQL
conn = sqlite3.connect(DBFILE)
with open(SQLFILE) as f:
conn.executescript(f.read())
conn.commit()
conn.close()
print("Database created!")
This will read the above SQL and create the actual users.db
database file.
PART 2) EXPORT DATABASE TO CSV
# (A) LOAD MODULES
import sqlite3, csv
# (B) SETTINGS
DBFILE = "users.db"
CSVFILE = "users.csv"
# (C) EXPORT ALL USERS TO CSV
with open(CSVFILE, mode="w", newline="") as f:
# (C1) CSV FILE WRITER
writer = csv.writer(f)
# (C2) CONNECT TO DATABASE
conn = sqlite3.connect(DBFILE)
cursor = conn.cursor()
# (C3) WRITE ROWS TO CSV
cursor.execute("SELECT * FROM `users`")
for row in cursor:
print(row)
writer.writerow(row)
conn.close()
Long story short – “Fetch rows from the database, write to CSV file”.
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
SORRY FOR THE ADS...
But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.
Buy Me A Coffee Code Boxx eBooks
EXAMPLE CODE DOWNLOAD
Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
EXTRA BITS & LINKS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
INFOGRAPHIC CHEAT SHEET

THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!