Welcome to a tutorial on how to export data from the database to an Excel spreadsheet in Python. So you want to generate an Excel report or create a list from the database? Well, it is actually a simple “read from database and write to Excel file”. Read on for the example!
TABLE OF CONTENTS
EXPORT TO EXCEL
All right, let us now get into the example of exporting from the database into an Excel file.
The “usual stuff”:
- Create a virtual environment
- Activate it –
- Install required libraries –
pip install openpyxl
STEP 1) DUMMY DATABASE
1A) USER TABLE & DATA
-- (A) USERS TABLE CREATE TABLE users ( uid INTEGER, name TEXT NOT NULL, email TEXT NOT NULL, tel TEXT NOT NULL, PRIMARY KEY("uid" AUTOINCREMENT) ); -- (B) DUMMY DATA INSERT INTO "users" VALUES (1,'Jo Doe','firstname.lastname@example.org','465785'), (2,'Joa Doe','email@example.com','123456'), (3,'Job Doe','firstname.lastname@example.org','234567'), (4,'Joe Doe','email@example.com','345678'), (5,'Jog Doe','firstname.lastname@example.org','578456'), (6,'Joh Doe','email@example.com','378945'), (7,'Joi Doe','firstname.lastname@example.org','456789'), (8,'Jon Doe','email@example.com','987654'), (9,'Jor Doe','firstname.lastname@example.org','754642'), (10,'Joy Doe','email@example.com','124578');
For this example, we will be working with this simple dummy
uidUser’s ID. Primary key and auto-increment.
nameThe user’s name.
1B) CREATE DATABASE
# (A) LOAD PACKAGES import sqlite3, os from sqlite3 import Error # (B) DATABASE + SQL FILE DBFILE = "users.db" SQLFILE = "s1_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!")
To keep things easy, without having to install any databases – We are using SQLite here. This script should be self-explanatory, we are just creating the
users.db database file, and importing
S1A_users.sql to create the table.
STEP 2) EXPORT TO EXCEL
# (A) INIT # (A1) LOAD MODULES import sqlite3, os, openpyxl from sqlite3 import Error # (A2) SETTINGS DBFILE = "users.db" # (B) OPEN DATABASE & CREATE EXCEL conn = sqlite3.connect(DBFILE) cursor = conn.cursor() book = openpyxl.Workbook() sheet = book.active # (C) EXPORT DATA TO EXCEL cursor.execute("SELECT * FROM `users`") results = cursor.fetchall() i = 0 for row in results: i += 1 j = 1 for col in row: cell = sheet.cell(row = i, column = j) cell.value = col j += 1 # (D) SAVE EXCEL FILE & CLOSE DB book.save("demo.xlsx") conn.close()
Lastly, this should be self-explanatory too… We are extracting records from the database and writing them into an Excel file. But just a quick note, Python is unable to write Excel files natively. So, we are using
openpyxl to do the job here. Will leave a link to their documentation below if you want to learn more.
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.
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.
HOW ABOUT MYSQL, MSSQL, POSTGRESQL, ETC…
Well, this tutorial will never end if we went through each and every database on Earth. So I will leave this as “homework” – Study the basics of the database on your own, install your own preferred database management tool, change the SQL for the
users table, and update
s3_export.py to connect to the database of your choice instead.
Just a quick note here that we can define Excel formulas as well, for example,
cell.value = "=SUM(A1:A10)".
LINKS & REFERENCES
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!