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!

 

 

TABLE OF CONTENTS

 

 

 

EXAMPLE CODE DOWNLOAD

Click here to download

The example code is released under the MIT license, so feel free to build on top of it or use it in your own project.

 

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

 

 

TUTORIAL VIDEO

 

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', '[email protected]'),
(2, 'Joe Doe', '[email protected]'),
(3, 'John Doe', '[email protected]'),
(4, 'Julie Doe', '[email protected]'),
(5, 'Johan Doe', '[email protected]'),
(6, 'Joanne Doe', '[email protected]'),
(7, 'Juliet Doe', '[email protected]'),
(8, 'June Doe', '[email protected]'),
(9, 'Juan Doe', '[email protected]'),
(10, 'Jamir Doe', '[email protected]'),
(11, 'Jaden Doe', '[email protected]'),
(12, 'James Doe', '[email protected]'),
(13, 'Janus Doe', '[email protected]'),
(14, 'Jason Doe', '[email protected]'),
(15, 'Jay Doe', '[email protected]'),
(16, 'Jeff Doe', '[email protected]'),
(17, 'Jenn Doe', '[email protected]'),
(18, 'Joah Doe', '[email protected]'),
(19, 'Joyce Doe', '[email protected]'),
(20, 'Joy Doe', '[email protected]'),
(21, 'Juke Doe', '[email protected]'),
(22, 'Johnnie Doe', '[email protected]'),
(23, 'Jim Doe', '[email protected]'),
(24, 'Jess Doe', '[email protected]'),
(25, 'Jabril Doe', '[email protected]');

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

import sqlite3
conn = sqlite3.connect("users.db")
with open("S1A_users.sql") 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) EXPORT ALL USERS TO CSV
with open("users.csv", mode="w", newline="") as f:
  # (B1) CSV FILE WRITER
  writer = csv.writer(f)

  # (B2) CONNECT TO DATABASE
  conn = sqlite3.connect("users.db")
  cursor = conn.cursor()

  # (B3) 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”.

 

 

 

INFOGRAPHIC CHEATSHEET

 

LINKS & REFERENCES

• Python CSV
• Python SQLite