Mark Sheet in PDF Using Data from Database

Interactive Google Colab Practice Notebook

Practice generating PDF student mark sheets using multi-source data (CSV, Excel, JSON, XML, SQL dump, SQLite, and MySQL) with custom templates directly inside Google Colab.

View on GitHub Open In Colab
Mark sheet from database to PDF page

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. Source code for both files is available below.

Data Sources 🔝

We will use the SQLite database student table. The script can easily be modified to retrieve data from a MySQL database table or external files.

Download sample SQLite database with student table

We can also use a MySQL database:

Full student table with SQL Dump

You can also download sample student datasets in multiple formats directly from the plus2net pandas student tutorial page:

!wget https://www.plus2net.com/python/download/student.csv
!wget https://www.plus2net.com/python/download/student.xlsx
!wget https://www.plus2net.com/python/download/student.json
!wget https://www.plus2net.com/python/download/student.xml
!wget https://www.plus2net.com/python/download/student.sql

Database Connection and Multi-Source Data Ingestion 🔝

We use try-except error handling to catch and display database errors cleanly:

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError

db_file = 'my_db.db'  
try:
    file1 = 'sqlite:///' + db_file 
    my_conn = create_engine(file1)
    # For MySQL use:
    # 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)

Ingesting Student Data from Alternative Sources

import csv
with open('student.csv', mode='r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    students = list(reader)
# Pick target student record by ID
st = next(s for s in students if s['id'] == '6')
data = (int(st['id']), st['name'], st['class'], int(st['mark']), st['gender'])
import openpyxl
wb = openpyxl.load_workbook('student.xlsx')
sheet = wb.active
rows = list(sheet.iter_rows(values_only=True))
header, records = rows[0], rows[1:]
data = next(r for r in records if r[0] == 6)
import json
with open('student.json', 'r', encoding='utf-8') as f:
    json_list = json.load(f)
st = next(s for s in json_list if int(s['id']) == 6)
data = (st['id'], st['name'], st['class'], int(st['mark']), st['gender'])
import xml.etree.ElementTree as ET
tree = ET.parse('student.xml')
root = tree.getroot()
st = next(elem for elem in root.findall('student') if elem.find('id').text == '6')
data = (int(st.find('id').text), st.find('name').text, st.find('class').text, int(st.find('mark').text), st.find('gender').text)

Source Code Files 🔝

main.py

from pathlib import Path
from reportlab.pdfgen import canvas
from reportlab.lib.units import inch
from reportlab.lib.pagesizes import letter
from temp_marksheet import my_temp # import template
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError

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

c = canvas.Canvas(str(my_path), pagesize=letter)
c = my_temp(c) # run 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')

# Connect to SQLite/MySQL and fetch student details
db_file = 'my_db.db'  
try:
    file1 = 'sqlite:///' + db_file 
    my_conn = create_engine(file1)
    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)

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()
print(f"Mark sheet PDF generated: {my_path}")

temp_marksheet.py

from reportlab.lib.units import inch
from datetime import date

def my_temp(c):
    c.translate(inch, inch)
    c.setFont("Helvetica", 14)
    c.setStrokeColorRGB(0.1, 0.8, 0.1)
    c.setFillColorRGB(0, 0, 1) # font color
    
    # Header Info
    c.drawString(0, 9 * inch, "1234, ABCD Road")
    c.drawString(0, 8.7 * inch, "Mycity, ZIP : 12345")
    c.setFillColorRGB(0, 0.5, 1)
    c.drawString(2 * inch, 8.7 * inch, " www.plus2net.com ")
    c.setFillColorRGB(0, 0, 0)
    c.line(0, 8.6 * inch, 6.8 * inch, 8.6 * inch)
    
    dt = date.today().strftime('%d-%b-%Y')
    c.drawString(5.6 * inch, 9.3 * inch, dt)
    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)
    c.drawString(0, -0.9 * inch, "\u00A9 plus2net.com")
    
    # Watermark
    c.rotate(45)
    c.setFillColorCMYK(0, 0, 0, 0.08)
    c.setFont("Helvetica", 100)
    c.drawString(2 * inch, 1 * inch, "SAMPLE")
    c.rotate(-45)
    
    return c

Practical Uses of Dynamic Mark Sheet Generation

Automated mark sheet generation with customized ReportLab templates is essential across educational institutions:

  • Automated Academic Evaluation: Instantly compute and output student letter grades (A >= 80, B >= 60, C < 60) alongside student performance metrics.
  • Multi-Format Data Integration: Query records dynamically from local SQLite DBs, enterprise MySQL servers, or flat files (CSV, Excel, JSON, XML).
  • Brandable & Secure Documents: Apply watermarks, institutional headers, signature placeholders, and automated date stamps.

Common Errors & Troubleshooting

  • Canvas Coordinate Matrix Offset: Calling c.translate(inch, inch) shifts the canvas origin (0, 0) by 1 inch right and upwards. Ensure subsequent coordinate calls account for this origin shift.
  • Font / Color Persistence across Template Functions: Canvas settings (like font size, fill color, stroke color) persist until explicitly overridden. Reset fill colors after drawing text headers or background watermarks.
  • Missing Record Handling: Always check if r_set.fetchone() or list search iterators return None before attempting tuple indexing to avoid TypeError: 'NoneType' object is not subscriptable.

Frequently Asked Questions (FAQ)

Q: How do I change the grading criteria logic?
A: Modify the conditional logic checking data[3] (the mark value index) inside main.py before drawing the grade string on canvas.

Q: Can I batch generate mark sheets for all students in a database?
A: Yes! Loop over r_set.fetchall(), invoke c = my_temp(c) for each student record, write the text details, and call c.showPage() inside the loop before saving the canvas.

Further Reading & Related Tutorials


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



Subscribe to our YouTube Channel here

plus2net.com






Python Video Tutorials
Python SQLite Video Tutorials
Python MySQL Video Tutorials
Python Tkinter Video Tutorials
✖
We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles Contact us
© 2000-2026 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer