Add Pandas DataFrame as table to pdf file by using data from MySQL or SQLite database by using Python ReportLab

Interactive Google Colab Practice Notebook

Practice extracting Pandas DataFrames from dictionaries, CSVs, Excel sheets, SQLite databases, and MySQL connections to render formatted Platypus tables directly inside Google Colab.

View on GitHub

Adding pandas dataframe created from SLQite or MySQL database table to PDF file by Python ReportLab

Our main script table_pandas.py reads records as a list (my_data) from a data provider module and builds a ReportLab Platypus table.

Inside table_pandas.py, we import my_data:

from reportlab.lib.units import inch
from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate
from reportlab.platypus.tables import Table, TableStyle, colors
from table_pandas_data import my_data  # import the data list

my_doc = SimpleDocTemplate('output/dataframe_table.pdf', pagesize=letter)

c_width = [1.5 * inch, 1.0 * inch, 1.2 * inch, 1.2 * inch]
t = Table(my_data, colWidths=c_width, repeatRows=1)
t.setStyle(TableStyle([
    ('FONTSIZE', (0, 0), (-1, -1), 12),
    ('BACKGROUND', (0, 0), (-1, 0), colors.lightgreen),
    ('VALIGN', (0, 0), (-1, 0), 'TOP'),
    ('GRID', (0, 0), (-1, -1), 0.5, colors.grey)
]))
elements = [t]
my_doc.build(elements)

Basics of adding Table to PDF file

Creating a DataFrame Source 🔝

Inside table_pandas_data.py, we construct a Pandas DataFrame from a Python dictionary and convert it into a list using df.values.tolist():

import pandas as pd

my_dict = {
    'NAME': ['Ravi', 'Raju', 'Alex', 'Ron', 'King', 'Jack'],
    'ID': [1, 2, 3, 4, 5, 6],
    'MATH': [80, 40, 70, 70, 60, 30],
    'ENGLISH': [80, 70, 40, 50, 60, 30]
}
df = pd.DataFrame(data=my_dict)
my_data = df.values.tolist()
my_data.insert(0, list(df.columns))  # include column headers

Using an SQLite Database Table as a Data Source 🔝

Pandas can query SQLite database tables directly using SQLAlchemy engines. We wrap database operations inside try-except blocks to handle SQL connectivity errors gracefully:

Download sample SQLite database with student table

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError

db_file = 'my_db.db'
file1 = 'sqlite:///' + db_file
my_conn = create_engine(file1)

try:
    df = pd.read_sql("SELECT * FROM student", my_conn)
    my_data = df.values.tolist()
    my_data.insert(0, list(df.columns))
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    print(error)

tolist() is used to create List from DataFrame

Using a MySQL Database 🔝

To pull records from a MySQL database table, simply swap out the SQLAlchemy connection string format:

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://<userid>:<password>@localhost/<database_name>")

Full student table with SQL Dump for MySQL database

Source Code Files 🔝

table_pandas.py

from pathlib import Path
from reportlab.lib.units import inch
from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate
from reportlab.platypus.tables import Table, TableStyle, colors
from table_pandas_data import my_data

output_dir = Path('output')
output_dir.mkdir(exist_ok=True)
my_path = output_dir / 'dataframe_table.pdf'

my_doc = SimpleDocTemplate(str(my_path), pagesize=letter)
c_width = [1.5 * inch, 1.0 * inch, 1.2 * inch, 1.2 * inch]

t = Table(my_data, colWidths=c_width, repeatRows=1)
t.setStyle(TableStyle([
    ('FONTSIZE', (0, 0), (-1, -1), 12),
    ('BACKGROUND', (0, 0), (-1, 0), colors.lightgreen),
    ('VALIGN', (0, 0), (-1, 0), 'TOP'),
    ('GRID', (0, 0), (-1, -1), 0.5, colors.grey)
]))

elements = [t]
my_doc.build(elements)
print(f"PDF DataFrame table generated successfully: {my_path}")

table_pandas_data.py

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError

db_file = 'my_db.db'
file1 = 'sqlite:///' + db_file
my_conn = create_engine(file1)

try:
    df = pd.read_sql("SELECT * FROM student", my_conn)
    my_data = df.values.tolist()
    my_data.insert(0, list(df.columns))
except SQLAlchemyError as e:
    # Fallback dictionary source if database file is absent
    my_dict = {
        'NAME': ['Ravi', 'Raju', 'Alex', 'Ron', 'King', 'Jack'],
        'ID': [1, 2, 3, 4, 5, 6],
        'MATH': [80, 40, 70, 70, 60, 30],
        'ENGLISH': [80, 70, 40, 50, 60, 30]
    }
    df = pd.DataFrame(data=my_dict)
    my_data = df.values.tolist()
    my_data.insert(0, list(df.columns))

Practical Uses of DataFrame PDF Tables

Business Intelligence Reports: Query transactional databases, process dataframes using Pandas aggregations, and export formatted summary tables into PDF deliverables.
Automated Student Transcripts & Mark Sheets: Pull student exam scores from SQLite/MySQL and render multi-row report cards with alternating table styles.
Dynamic Data Conversion: Seamlessly transform dictionary arrays, CSV exports, and database query cursors into ReportLab-compatible Platypus list structures.

Common Errors & Troubleshooting

Missing Column Header Row: Calling `df.values.tolist()` only extracts row values. Remember to insert column headers as the first row using `my_data.insert(0, list(df.columns))`.
Column Width Exceeding Page Margins: If total column widths exceed the printable page width, table cells overflow or get clipped. Adjust `colWidths` or scale font sizes accordingly.
SQLAlchemy Connection Failures: Ensure database connection strings and driver specifications (e.g. `mysql+mysqldb://`) are properly configured before executing `pd.read_sql()`.

Frequently Asked Questions (FAQ)

Q: Can I style alternating rows in a Pandas DataFrame PDF table?
A: Yes, you can apply background color styles dynamically using list comprehensions or `TableStyle` commands such as `('BACKGROUND', (0, i), (-1, i), colors.beige)` inside a loop.

Q: How do I handle large database tables that span multiple pages?
A: Set `repeatRows=1` in the `Table` constructor so that header rows automatically repeat at the top of each new page.

PDF file Table from Pandas DataFrame