Export Database Query Results to Excel with Python pyexcel

Database management often requires exporting data. Python makes this easy. The pyexcel library simplifies Excel exports.

This guide shows you how to export query results to Excel. You will learn practical examples. We cover SQLite, MySQL, and PostgreSQL.

Why Export Database Data to Excel?

Excel is widely used for data analysis. Many professionals prefer it. Exporting database data enables better reporting.

Python bridges databases and Excel. Automated exports save time. pyexcel handles the Excel formatting for you.

You can schedule regular data exports. This keeps reports current. It also reduces manual work.

Installing Required Packages

First, install pyexcel and database drivers. Use pip for installation. Choose the packages you need.

pip install pyexcel pyexcel-xlsx
# For SQLite (built-in)
pip install mysql-connector-python  # For MySQL
pip install psycopg2-binary         # For PostgreSQL

If you encounter installation issues, see our guide on Install pyexcel in Python with pip and Virtualenv.

Basic pyexcel Export Workflow

The export process has three main steps. Connect to your database. Execute your query. Save results with pyexcel.

Here is the basic pattern. We will expand on each step.

import database_driver
import pyexcel

# 1. Connect to database
connection = database_driver.connect(connection_string)

# 2. Execute query
cursor = connection.cursor()
cursor.execute("SELECT * FROM table_name")
results = cursor.fetchall()

# 3. Export to Excel
pyexcel.save_as(records=results, dest_file_name="output.xlsx")

Exporting from SQLite Database

SQLite is a lightweight database. It is perfect for testing. Let's export data from an SQLite database.

import sqlite3
import pyexcel

# Connect to SQLite database
conn = sqlite3.connect('sample.db')
cursor = conn.cursor()

# Create sample data
cursor.execute('''CREATE TABLE IF NOT EXISTS employees
                  (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary REAL)''')
cursor.execute("INSERT INTO employees VALUES (1, 'John Doe', 'Engineering', 75000)")
cursor.execute("INSERT INTO employees VALUES (2, 'Jane Smith', 'Marketing', 65000)")
conn.commit()

# Execute query and export
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()

# Get column names
column_names = [description[0] for description in cursor.description]

# Combine column names with data
export_data = [column_names] + rows

# Save to Excel
pyexcel.save_as(array=export_data, dest_file_name='employees.xlsx')

conn.close()
print("Data exported successfully to employees.xlsx")

Data exported successfully to employees.xlsx

Exporting from MySQL Database

MySQL is a popular relational database. Exporting follows a similar pattern. You need the MySQL connector.

import mysql.connector
import pyexcel

# Database connection details
config = {
    'user': 'username',
    'password': 'password',
    'host': 'localhost',
    'database': 'company_db'
}

try:
    # Connect to MySQL
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()
    
    # Execute query
    cursor.execute("SELECT product_name, category, price, stock FROM products")
    results = cursor.fetchall()
    
    # Get column names
    columns = [desc[0] for desc in cursor.description]
    data_with_headers = [columns] + results
    
    # Export to Excel
    pyexcel.save_as(array=data_with_headers, dest_file_name='products.xlsx')
    
    print("MySQL data exported to products.xlsx")
    
except mysql.connector.Error as err:
    print(f"Database error: {err}")
finally:
    if conn.is_connected():
        cursor.close()
        conn.close()

Exporting from PostgreSQL Database

PostgreSQL is a powerful open-source database. The process is similar to MySQL. Use psycopg2 for connectivity.

import psycopg2
import pyexcel

# Connection parameters
conn_params = {
    'host': 'localhost',
    'database': 'sales_db',
    'user': 'postgres',
    'password': 'your_password'
}

try:
    # Connect to PostgreSQL
    conn = psycopg2.connect(**conn_params)
    cursor = conn.cursor()
    
    # Complex query example
    query = """
    SELECT c.customer_name, o.order_date, p.product_name, oi.quantity
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.order_date >= '2024-01-01'
    ORDER BY o.order_date DESC
    """
    
    cursor.execute(query)
    results = cursor.fetchall()
    
    # Include headers
    columns = [desc[0] for desc in cursor.description]
    export_data = [columns] + results
    
    # Save with custom sheet name
    pyexcel.save_as(array=export_data, dest_file_name='sales_report.xlsx')
    
    print("PostgreSQL sales data exported successfully")
    
except psycopg2.Error as e:
    print(f"PostgreSQL error: {e}")
finally:
    if conn:
        cursor.close()
        conn.close()

Advanced pyexcel Features

pyexcel offers more than basic exports. You can customize the output. Let's explore some advanced features.

Multiple Sheets Export

Create Excel files with multiple sheets. This organizes related data. Use save_book_as method.

import pyexcel

# Data for different departments
engineering_data = [['Name', 'Project'], ['Alice', 'Website'], ['Bob', 'Mobile App']]
marketing_data = [['Name', 'Campaign'], ['Carol', 'Social Media'], ['David', 'Email']]

# Create a dictionary with sheet names and data
sheets_data = {
    'Engineering': engineering_data,
    'Marketing': marketing_data
}

# Export to multi-sheet Excel file
pyexcel.save_book_as(bookdict=sheets_data, dest_file_name='department_reports.xlsx')

Export Specific Columns Only

You don't always need all columns. Filter data before exporting. This creates cleaner reports.

import sqlite3
import pyexcel

conn = sqlite3.connect('company.db')
cursor = conn.cursor()

# Get all data but export only specific columns
cursor.execute("SELECT * FROM employees")
all_data = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]

# Choose which columns to export (name and department only)
desired_columns = ['name', 'department']
column_indices = [columns.index(col) for col in desired_columns]

filtered_data = [desired_columns]  # Headers
for row in all_data:
    filtered_row = [row[i] for i in column_indices]
    filtered_data.append(filtered_row)

pyexcel.save_as(array=filtered_data, dest_file_name='filtered_employees.xlsx')
conn.close()

For more advanced data filtering techniques, check our guide on Filter Search Spreadsheet Rows Python pyexcel.

Data Cleaning Before Export

Database data often needs cleaning. Handle missing values. Format dates and numbers properly.

import sqlite3
import pyexcel
from datetime import datetime

conn = sqlite3.connect('data.db')
cursor = conn.cursor()

cursor.execute("SELECT name, hire_date, salary FROM staff")
results = cursor.fetchall()

cleaned_data = [['Name', 'Hire Date', 'Salary']]  # Headers

for row in results:
    name, hire_date, salary = row
    
    # Clean data
    if name is None:
        name = 'Unknown'
    
    # Format date
    if isinstance(hire_date, str):
        try:
            hire_date = datetime.strptime(hire_date, '%Y-%m-%d').strftime('%m/%d/%Y')
        except ValueError:
            hire_date = 'Invalid Date'
    
    # Handle missing salary
    if salary is None:
        salary = 0
    
    cleaned_data.append([name, hire_date, salary])

pyexcel.save_as(array=cleaned_data, dest_file_name='cleaned_staff.xlsx')
conn.close()

Learn more about data preparation in our tutorial on Clean Normalize Spreadsheet Data Python pyexcel.

Error Handling and Best Practices

Always include error handling. Database operations can fail. Proper error handling makes scripts reliable.

import sqlite3
import pyexcel
import sys

def export_database_to_excel(db_path, query, output_file):
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        cursor.execute(query)
        results = cursor.fetchall()
        
        if not results:
            print("No data found for the query")
            return False
            
        columns = [desc[0] for desc in cursor.description]
        export_data = [columns] + results
        
        pyexcel.save_as(array=export_data, dest_file_name=output_file)
        
        print(f"Successfully exported {len(results)} rows to {output_file}")
        return True
        
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return False
    except Exception as e:
        print(f"Unexpected error: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()

# Usage example
export_database_to_excel(
    db_path='company.db',
    query='SELECT * FROM products WHERE active = 1',
    output_file='active_products.xlsx'
)

Conclusion

Exporting database data to Excel is valuable. Python and pyexcel make it straightforward. You can automate routine reporting tasks.

We covered SQLite, MySQL, and PostgreSQL. Each follows similar patterns. The key is proper database connectivity.

Remember to handle errors gracefully. Clean your data before exporting. Use appropriate file naming conventions.

pyexcel offers even more capabilities. Explore formatting options. Create complex multi-sheet reports.

Start with the examples provided. Adapt them to your specific needs. Automate your data export workflows today.