Python Export Database To CSV (Simple Example)

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

 

 

 

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”.

 

 

 

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.

 

 

LINKS & REFERENCES

• Python CSV
• Python SQLite

 

INFOGRAPHIC CHEAT SHEET