Create Mark sheet pdf file by using data from SQLite / MySQL database table in Python ReportLab

Generate PDF Mark sheet of students using data from SQLite or MySQL table using Python Reportlab

We will create this sample mark sheet by using data from SQLite ( or MySQL ) database student table.

We used two files for this script, the main.py file creates the canvas and uses the template file temp_marksheet.py to generate the PDF file with data.
Copy the source code of these files at the end of this page.

Data Source

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.

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'  
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://root:pw@localhost/my_tutorial")    
    r_set=my_conn.execute("SELECT *  FROM student WHERE id =6")
    data=r_set.fetchone()
    
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    print(error)

main.py

from reportlab.pdfgen import canvas
my_path='G:\\My drive\\testing\\pypdf2\\my_pdf.pdf' 
from reportlab.lib.units import inch
from reportlab.lib.pagesizes import letter, A4
from temp_marksheet import my_temp # import the template
c = canvas.Canvas(my_path,pagesize=letter)
c=my_temp(c) # run the template
c.setFillColorRGB(1,0,0)
c.setFont("Helvetica", 70)
c.drawRightString(6.4*inch,7.5*inch,'MARKSHEET')
c.setFillColorRGB(0,0,0)
c.setFont("Helvetica", 24)
c.drawRightString(2.5*inch,6.8*inch,'ID:')
c.drawRightString(2.5*inch,6*inch,'Name:')
c.drawRightString(2.5*inch,5*inch,'Class:')
c.drawRightString(2.5*inch,4*inch,'Gender:')
c.drawRightString(2.5*inch,3*inch,'Mark:')
c.drawRightString(3*inch,2*inch,'Grade:')
c.drawRightString(6*inch,-0.4*inch,'Signature')
# Conect to SQLite and collect the student details.
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
db_file='G:\\My drive\\testing\\my_db.db'  
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://root:pw@localhost/my_tutorial")    
    r_set=my_conn.execute("SELECT *  FROM student WHERE id =6")
    data=r_set.fetchone()
    
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    print(error)
## end of SQLite connection. 
c.setFillColorRGB(0,0,1)
c.setFont("Helvetica", 20)
c.drawString(3*inch,6.8*inch,str(data[0]))
c.drawString(3*inch,6*inch,data[1])
c.drawString(3*inch,5*inch,data[2])
c.drawString(3*inch,4*inch,data[4])
c.drawString(3*inch,3*inch,str(data[3]))
if(data[3]>=80):
    c.drawString(4*inch,2*inch,'A')
elif(data[3]>=60):
    c.drawString(4*inch,2*inch,'B')
else:
    c.drawString(4*inch,2*inch,'C')
c.showPage()
c.save()

temp_marksheet.py

from reportlab.lib.units import inch
def my_temp(c):
    c.translate(inch,inch)
# define a large font
    c.setFont("Helvetica", 14)
# choose some colors
    c.setStrokeColorRGB(0.1,0.8,0.1)
    c.setFillColorRGB(0,0,1) # font colour
    c.drawImage('D:\\top2.jpg',-0.8*inch,9.3*inch) #change path
    c.drawString(0, 9*inch, "1234, ABCD Road")
    c.drawString(0, 8.7*inch, "Mycity, ZIP : 12345  ")
    c.setFillColorRGB(0,0.5,1) # font colour
    c.drawString(2*inch, 8.7*inch, " www.plus2net.com ")
    c.setFillColorRGB(0,0,0) # font colour
    c.line(0,8.6*inch,6.8*inch,8.6*inch)
    from  datetime import date
    dt = date.today().strftime('%d-%b-%Y') #current date as string
    c.drawString(5.6*inch,9.3*inch,dt) # print the date
    c.setFont("Helvetica", 8)
    c.drawString(3*inch,9.6*inch,'End Semester Examination')
    c.line(0,-0.7*inch,6.8*inch,-0.7*inch)
    c.setFillColorRGB(1,0,0) # font colour
    c.drawString(0, -0.9*inch, u"\u00A9"+" plus2net.com")
    c.rotate(45)
    c.setFillColorCMYK(0,0,0,0.08) # font colour
    c.setFont("Helvetica", 100)
    c.drawString(2*inch, 1*inch, "SAMPLE") # watermarking
    c.rotate(-45)
    return c

Python PDF
PDFs with Student Information from user input using Tkinter and SQLite Generate PDF Invoice using Sales data Table from different data sources to PDF file Table from Pandas DataFrame to PDF file

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Mark sheet in PDF using data from database

Data Source

Database connection and getting data

Subhendu Mohapatra