Add table to pdf file with style by using data different sources in Python ReportLab

All records of Student table in PDF file

Adding tabular data to PDF file by adding table with option and styles using Python report lab

Adding table to PDF file

Importing libraries and setting the path to create pdf file.

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 my_table_data import my_data # import the data
my_path='G:\\My drive\\testing\\pypdf2\\my_pdf.pdf'#path,file name

data

The name of the variable is maintained as the sources can be changed. Note the variable name my_data here.

my_data= [['ID', 'Name', 'Class', 'Mark', 'Gender'],
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male')]

Creating SimpleDocTemplate object.

my_doc=SimpleDocTemplate(my_path,pagesize=letter)

rowHights , colWidths, repeatRows

c_width=[0.4*inch,1.5*inch,1*inch,1*inch,1*inch]
t=Table(my_data,rowHeights=20,repeatRows=1,colWidths=c_width)

When we are not sure about the number of columns, then we can fix a common width for all the columns of our table.

c_width=[1*inch]

setStyle()

BACKGROUND , FONTSIZE are set here.

(0,0) : ( column=0,row=0) Top left cell
(-1,0) : ( column=-1,row=0) Top row, column=-1 is the right most column.
(-1,-1): (column=-1,row=-1) Bottom row right most ( column ) cell.

marking of rows and columns in table of PDF file

marking of rows and columns in table of PDF file

t.setStyle(TableStyle([('BACKGROUND',(0,0),(-1,0),colors.lightgreen),
('FONTSIZE',(0,0),(-1,-1),14)]))

Full code is here

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 my_table_data import my_data # import the data
my_path='G:\\My drive\\testing\\pypdf2\\my_pdf.pdf' # change path, file name 

my_data= [['ID', 'Name', 'Class', 'Mark', 'Gender'],
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male')]

my_doc=SimpleDocTemplate(my_path,pagesize=letter)
c_width=[0.4*inch,1.5*inch,1*inch,1*inch,1*inch]
t=Table(my_data,rowHeights=20,repeatRows=1,colWidths=c_width)
t.setStyle(TableStyle([('BACKGROUND',(0,0),(-1,0),colors.lightgreen),
('FONTSIZE',(0,0),(-1,-1),10)]))
elements=[]
elements.append(t)
my_doc.build(elements)

Using data file

In above code we created one list using some data ( column names and rows of data ) . This variable my_data we can get from different file here.

Importing the data file

Here by changing the data file we can collect data from different sources. In all our sources, after fetching the data finally we will create the list of data using the same variable name my_data.

from my_table_data import my_data

We will remove the list storing the data as we are getting the same variable ( my_data ) from another file my_table_data.py

my_table_data.py

my_data=[['ID', 'Name', 'Class', 'Mark', 'Gender'],
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male'),
-------
-------
(35, 'Rows Noump', 'Six', 88, 'female')]

You can download the data at end of this page.

Data Source

Adding tabular data to PDF file from different data sources like SQLite, MySQL student table

We will use SQLite database student table.
Script can easily changed to get data from MySQL database table.
Download sample SQLite database with student table
We can also use MySQL database.
Full student table with SQL Dump for MySQL database.

Database connection and getting data

We used try except error handling to show error message.

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
db_file='G:\\My drive\\testing\\my_db.db'  # change your path 
my_data=[] 
try:
    file1='sqlite:///'+ db_file 
    #my_conn = create_engine(file1)
    # For MySQL use the below line and remove the above lines for SQLite file
    my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")    
    r_set=my_conn.execute("SELECT * FROM student ")
    my_data.append(list(r_set.keys())) # add the column names 
    for row in r_set.fetchall():
        my_data.append(row) # adding one row 
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    print(error)

Using MySQL database

In above code we can create the connection object my_conn by using this connection string.

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

table-1-basic.py

my_table_database.py

my_table_data.py

my_data=[['ID', 'Name', 'Class', 'Mark', 'Gender'],
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male'),
(4, 'Krish Star', 'Four', 60, 'female'),
(5, 'John Mike', 'Four', 60, 'female'),
(6, 'Alex John', 'Four', 55, 'male'),
(7, 'My John Rob', 'Five', 78, 'male'),
(8, 'Asruid', 'Five', 85, 'male'),
(9, 'Tes Qry', 'Six', 78, 'male'),
(10, 'Big John', 'Four', 55, 'female'),
(11, 'Ronald', 'Six', 89, 'female'),
(12, 'Recky', 'Six', 94, 'female'),
(13, 'Kty', 'Seven', 88, 'female'),
(14, 'Bigy', 'Seven', 88, 'female'),
(15, 'Tade Row', 'Four', 88, 'male'),
(16, 'Gimmy', 'Four', 88, 'male'),
(17, 'Tumyu', 'Six', 54, 'male'),
(18, 'Honny', 'Five', 75, 'male'),
(19, 'Tinny', 'Nine', 18, 'male'),
(20, 'Jackly', 'Nine', 65, 'female'),
(21, 'Babby John', 'Four', 69, 'female'),
(22, 'Reggid', 'Seven', 55, 'female'),
(23, 'Herod', 'Eight', 79, 'male'),
(24, 'Tiddy Now', 'Seven', 78, 'male'),
(25, 'Giff Tow', 'Seven', 88, 'male'),
(26, 'Crelea', 'Seven', 79, 'male'),
(27, 'Big Nose', 'Three', 81, 'female'),
(28, 'Rojj Base', 'Seven', 86, 'female'),
(29, 'Tess Played', 'Seven', 55, 'male'),
(30, 'Reppy Red', 'Six', 79, 'female'),
(31, 'Marry Toeey', 'Four', 88, 'male'),
(32, 'Binn Rott', 'Seven', 90, 'female'),
(33, 'Kenn Rein', 'Six', 96, 'female'),
(34, 'Gain Toe', 'Seven', 69, 'male'),
(35, 'Rows Noump', 'Six', 88, 'female')]

Adding Title , footer along with data table

rom reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.rl_config import defaultPageSize
from reportlab.lib.units import inch
from reportlab.platypus.tables import Table, TableStyle, colors

PAGE_HEIGHT = defaultPageSize[1]
PAGE_WIDTH = defaultPageSize[0]
styles = getSampleStyleSheet()
Title = "This is my Main Title"
pageinfo = "At footer sample by www.plus2net.com"
my_path = "F:\\testing\\pdf_files\\my_pdf.pdf"
my_data = [
    ["ID", "Name", "Class", "Mark", "Gender"],
    (1, "John Deo", "Four", 75, "female"),
    (2, "Max Ruin", "Three", 85, "male"),
    (3, "Arnold", "Three", 55, "male"),
]


def myFirstPage(canvas, doc):
    canvas.saveState()
    canvas.setFont("Times-Bold", 16)
    canvas.drawCentredString(PAGE_WIDTH / 2.0, PAGE_HEIGHT - 50, Title)
    canvas.setFont("Times-Roman", 9)
    canvas.drawString(inch, 0.75 * inch, "First Page / %s" % pageinfo)
    canvas.restoreState()


def myLaterPages(canvas, doc):
    canvas.saveState()
    canvas.setFont("Times-Roman", 9)
    canvas.drawString(inch, 0.75 * inch, "Page %d %s" % (doc.page, pageinfo))
    canvas.restoreState()


def go():
    doc = SimpleDocTemplate(my_path)
    style = styles["Normal"]
    some_text = "This is a string  . " * 10
    p = Paragraph(some_text, style)

    c_width = [0.4 * inch, 1.5 * inch, 1 * inch, 1 * inch, 1 * inch]
    t = Table(my_data, rowHeights=20, repeatRows=1, colWidths=c_width)
    t.setStyle(
        TableStyle(
            [
                ("BACKGROUND", (0, 0), (-1, 0), colors.lightgreen),
                ("FONTSIZE", (0, 0), (-1, -1), 10),
            ]
        )
    )
    elements = []
    elements.append(t)
    elements.append(p)

    doc.build(elements, onFirstPage=myFirstPage, onLaterPages=myLaterPages)


go()

Python PDF Generate PDF Invoice using Sales data PDF Mark sheet by using data from SQLite database

Subscribe to our YouTube Channel here

	04-08-2023
AttributeError: 'Engine' object has no attribute 'execute'

	12-08-2023
This is due to new SQLAlchemy driver. You need to add one more line to connect and use query as text. Detail modified script to connect and execute is available here https://www.plus2net.com/python/mysql-sqlalchemy.php One video is also kept here with all details. https://youtu.be/90-PW53e1jM

PDF file from database table