Python Export From Database To Excel (Simple Example)

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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes Export Excel Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create your own project folder, e.g. D:\xlsdb, unzip the code inside this folder.
  • Open the terminal (or command line), navigate to your project folder cd D:\xlsdb.
  • As usual, create a virtual environment if you don’t want to mess up your other projects.
    • virtualenv venv
    • Windows – venv\scripts\activate
    • Mac/Linux – venv/bin/activate
  • Get all the required packages – pip install openpyxl.
  • Create the dummy database by running python s2_create.py.
  • Launch python s3_export.py for the export example.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

EXPORT TO EXCEL

All right, let us now get into the example of exporting from the database into an Excel file.

 

PART 1) DUMMY USERS TABLE & DATA

s1_users.sql
-- (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','jo@doe.com','465785'),
(2,'Joa Doe','joa@doe.com','123456'),
(3,'Job Doe','job@doe.com','234567'),
(4,'Joe Doe','joe@doe.com','345678'),
(5,'Jog Doe','jog@doe.com','578456'),
(6,'Joh Doe','joh@doe.com','378945'),
(7,'Joi Doe','joi@doe.com','456789'),
(8,'Jon Doe','jon@doe.com','987654'),
(9,'Jor Doe','jor@doe.com','754642'),
(10,'Joy Doe','joy@doe.com','124578');

For this example, we will be working with this simple dummy users table.

  • uid User’s ID. Primary key and auto-increment.
  • name The user’s name.
  • email The user’s email address.
  • tel Telephone number.

 

 

PART 2) CREATE THE DATABASE

s2_create.py
# (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 s1_users.sql to create the table.

 

 

PART 3) EXPORT TO EXCEL

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

 

 

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

 

ADDING FORMULAS

Just a quick note here that we can define Excel formulas as well, for example, cell.value = "=SUM(A1:A10)".

 

LINKS & REFERENCES

 

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.