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

 

 

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

S1A_users.sql
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

S1B_create.py
# (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

S2_export.py
# (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

Python Export Database To CSV (click to enlarge)

 

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!

Leave a Comment

Your email address will not be published. Required fields are marked *