How to Export Data from Database to Excel in Python (Step by Step Guide)

5 min readJun 13, 2023

Excel is a popular spreadsheet program that is used by many businesses and individuals to manage and analyze data, and create reports. There are several situations when we may need to export data from a database to Excel:

Reporting: Excel is a popular tool for creating reports as it provides several formatting and visualization options that make it easy to present data in a meaningful way. By exporting data from a database to Excel, we can easily generate reports based on the data stored in the database.
Data analysis: Excel is an excellent tool for analyzing data, and many businesses use it for this purpose. By exporting data from a database to Excel, we can leverage Excel’s powerful data analysis features to gain insights from the data and make informed decisions.
Data sharing: If we need to share data with someone who does not have access to the database, exporting the data from the database to Excel can be a convenient solution. We can share the Excel file via email or a file-sharing service, and the recipient can view and analyze the data without needing access to the database.
Data backup: Exporting data from a database to Excel can also serve as a backup mechanism. In case of a database failure or corruption, having a copy of the data in Excel can help us restore the database quickly and easily.

In this article, we will explore how to programmatically export data from a database to Excel with Python.

Prerequisites

Before getting started, we’ll need to have the following software installed in our environment:

Python 3.x
A database system (e.g., MySQL, PostgreSQL, Microsoft SQL Server, Microsoft Access, or SQLite)
The appropriate Python library for connecting to our database system (you will find the library and the pip command for installation in the next part)
The Spire.XLS for Python library for working with Excel files (you will find the pip command for installation in the next part)

How to Export Data from Database to Excel with Python

Step 1. Connect to the Database

To export data from a database to an Excel file, we first need to establish a connection to the database.

Depending on the type of database used, we can use different libraries to connect to the database, such as pyodbc library for Microsoft Access and SQL Server databases (pip command: pip install pyodbc), psycopg2 library for PostgreSQL database (pip command: pip install psycopg2), mysql-connector-python library for MySQL database (pip command: pip install mysql-connector-python), or sqlite3 library for SQLite database (sqlite3 is a built-in module in Python, which means you don't need to install it separately and can start using it right away).

Let’s take Microsoft Access database as an example. If we want to connect to a Microsoft Access database using pyodbc, we’d do the following:

1. Install pyodbc

pip install pyodbc

2. Import pyodbc module
We need to import the pyodbc module so we can use this module’s methods to communicate with the Microsoft Access database.

import pyodbc

3. Establish a Connection Using the connect() Method

Once pyodbc is installed, we can use the connect() method provided by the pyodbc module to create a new connection object to the database. We will need to pass in the appropriate parameters to the the connect() method, such as the driver name, the path to the database file, and any required authentication credentials.

Here’s an example of how to create a connection to an Access database:

# Set the path to your Access database file
db_file_path = r'C:/Users/Administrator/Documents/Employees.accdb'

# Establish a connection to the database
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + db_file_path + ';')

Step 2. Execute a Database Query to Retrieve Required Data from the Database

Now, we have connected to our Access database, we can execute a database query to retrieve data from the database. We do this by creating a cursor object and using it to execute database commands.

For example, let’s say we want to retrieve all the records from a table named “employees”. We could do this with the following code:

cursor = conn.cursor()
db_query = 'SELECT * FROM Employees'
cursor.execute(db_query)

Step 3. Write the Data into an Excel File Using Spire.XLS for Python Library

Now, we have retrieved the data, we can use Spire.XLS for Python library to write it into an Excel file.

Spire.XLS for Python is a multifunctional and easy-to-use library for creating, reading, editing, and converting Excel files within Python applications. With this library, we can easily work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, we are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.

1. Install Spire.XLS for Python

pip install Spire.Xls

2. Import Spire.XLS module

from spire.xls import *
from spire.xls.common import *

3. Create an Excel File and Write the Data into It using Spire.XLS for Python

#Create a workbook
workbook = Workbook()
workbook.Version = ExcelVersion.Version2016
#Get the first worksheet of the file
worksheet = workbook.Worksheets[0]

#Get the headers of the retrieved data
headers = [column[0] for column in cursor.description]

#Write the headers to specific cells of the worksheet
for col_num, header in enumerate(headers, 1):
    worksheet.Range[1, col_num].Text = header
    #Set font style for headers
    worksheet.Range[1, col_num].Style.Font.IsBold = True

#Write the remaining data to specific cells of the worksheet
for row_num, row in enumerate(cursor, 2):
    for col_num, cell_value in enumerate(row, 1):
        if type(cell_value) == int:
            worksheet.Range[row_num, col_num].NumberValue = cell_value
        else:
            worksheet.Range[row_num, col_num].Text = str(cell_value)        

#Autofit column widths
worksheet.AllocatedRange.AutoFitColumns()

#Save the Excel file to a specific location
workbook.SaveToFile('DatabaseToExcel.xlsx', ExcelVersion.Version2016)

The following is the entire code for exporting data from a Microsoft Access database to an Excel file:

import pyodbc
from spire.xls import *
from spire.xls.common import *

#Create a workbook
workbook = Workbook()
workbook.Version = ExcelVersion.Version2016
#Get the first worksheet of the file
worksheet = workbook.Worksheets[0]

#Set the path to your Access database file
db_file_path = r'C:/Users/Administrator/Documents/Employees.accdb'

#Establish a connection to the database
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + db_file_path + ';')

#Execute a query to retrieve data from a database table named 'Employees'
cursor = conn.cursor()
db_query = 'SELECT * FROM Employees'
cursor.execute(db_query)

#Get the headers of the data
headers = [column[0] for column in cursor.description]

#Write the headers to specific cells of the worksheet
for col_num, header in enumerate(headers, 1):
    worksheet.Range[1, col_num].Text = header
    #Set font style for headers
    worksheet.Range[1, col_num].Style.Font.IsBold = True

#Write the remaining data to specific cells of the worksheet
for row_num, row in enumerate(cursor, 2):
    for col_num, cell_value in enumerate(row, 1):
        if type(cell_value) == int:
            worksheet.Range[row_num, col_num].NumberValue = cell_value
        else:
            worksheet.Range[row_num, col_num].Text = str(cell_value)

#Autofit column widths
worksheet.AllocatedRange.AutoFitColumns()

#Save the Excel file to a specific location
workbook.SaveToFile('DatabaseToExcel.xlsx', ExcelVersion.Version2016)
workbook.Dispose()
conn.close()

Conclusion

In this article, we have discussed how to export data from a database to an Excel file with Python. We hope it is useful to you.