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

We can connect to different data sources to collect data and display in tabular format inside a PDF document.
In part 4 we learnt about basics of adding tabular data to PDF file with various options and styles.
While using the table the data source is taken from a file where we have a list with 35 rows of records.
We can change the source of the data by keeping our main code to create the table same.
Here the variable my_data is used and the value for this variable is taken from different data sources.
We create one connection string to connect to SQLite Database and execute the query to collect a set of records of the student table.
This connection string can be changed to MySQL database.
First we will execute the query to get a record set and then we call all column heads by using the keys() method.
We will create one list by using fetchall() and then we will append each row of data to our main variable my_data.
This variable my_data is used to create our table in pdf document.
Download the source code from here.
www.plus2net.c...
Part 1 Creating PDF file and adding text, line , images , water mark etc
• Creating PDF file and ...
Part 2 Generate PDF Mark sheet of students using data from SQLite or MySQL table
• Generate PDF Mark shee...
Part 3 PDF Invoice generation in Python
• PDF Invoice generation...
Part 4 Adding table to PDF file
• Adding tabular data to...
Part 5 Adding database table data to PDF file
• Adding tabular data to...
Part 6 Adding Pandas DataFrame data to PDF file
• Adding pandas datafram...
Part 7 Adding shapes like circle, rectangles
• PDF pages in Python to...
Part 8 Adding bar charts
• Vertical and horizonta...
Part 9 Adding line charts
• Adding line chart to P...
Part 10 Adding Pie Charts
• Pie chart in Python PD...
Part 11 Adding Paragraph
• Adding Paragraph text ...
Part 12 Creating PDF reports
• Creating PDF report by...
#MySQLdataInPDF #SQLiteDataPDF #PDFdataSources #DataSourceIncreatingPDFTable #python #reportLab #plus2net #fetchall

[@nayibecamacho7996]

Nice video but I would like to add the row number in the report (not the identity number) to see how many records my list report in reportlab has. Thanks a lot

[@plus2net1]

Number of records we have to collect from backend database as use the same as string to display at footer or any where else. Here the list we are creating also can be used to get the number of rows . Once the data is available same can be positioned as string , check how pageinfo is added as a string.
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()
www.plus2net.com/python/pdf-table.php

[@ali-mw8nf]

This video is really much informative. Sir will we also learn in other parts of it, how to add sqlite data through tkinter application as you have done in resturant project, to create invoice or customer bill.

[@plus2net1]

Ok, we will use that restaurant management script and bill generation part we will integrate to generate the PDF invoice.

[@anandvishwa305]

Sir how to add a static image (logo) and text in top of the first page of pdf as you demonstrated in this video.

[@plus2net1]

Check this how image is added to pdf
www.plus2net.com/python/pdf-record.php
Continue with this and add data

[@martinjeaks]

Could any on please tell me how to add a report Title and sub title in the first page of the report. Thankyou.

[@plus2net1]

Title and sub title , you can use standard font style and add the text.
www.plus2net.com/python/pdf.php
if you want to repeat the titles then keep as templated and call it from different pages. Here is one example.
www.plus2net.com/python/pdf-id-card1.php
For tabular data you can check this , you can add headers which will repeat at every page top.
th-cam.com/video/n7Ive256tPE/w-d-xo.html
or check here
www.plus2net.com/python/pdf-table.php

[@martinjeaks]

@@plus2net1 I crated a report exactly as you mentioned.. I watched the videos. I could not figure it out as to how can I add a heading and a sub heading to the report. There is no canvas used. My code for the template is pdf = pdf=SimpleDocTemplate(fileName, pagesize = landscape(A3)).

[@plus2net1]

@@martinjeaks Towards the end of the page , the code is added. This will have one title, data and footer . Just copy the code and try. The data part my_data can be replaced from any other data sources.
www.plus2net.com/python/pdf-table.php
Can be improved further ... we will add that.

[@martinjeaks]

@@plus2net1 Got it sir. Thankyou very much

[@malikkamil6596]

I am printing two list how i can add it to table

[@malikkamil6596]

def order_pdf(request, oid):
doc = SimpleDocTemplate("hello_platypus.pdf",
pagesize=letter,
rightMargin=72,
leftMargin=72,
topMargin=72,
bottomMargin=18)
#create bytestrem buffer
buf =io.BytesIO()
# create a canvas
c = canvas.Canvas(buf, pagesize=letter, bottomup=0, )
# create a text object
textob = c.beginText()
textob.setTextOrigin(350, 100)
textob.setFont("Helvetica", 14)
textoc = c.beginText()
textoc.setTextOrigin(120, 100)
textoc.setFont("Helvetica", 14)
#add some lines of text
# lines = [
# "this is line 1",
# "this is line 2",
# "this is line 3",
# ]
order = Orders.objects.filter(pk=oid)
if order:
order_instance = Orders.objects.get(pk=oid)
print(order_instance)
lines = []
vines = []
# for order in orders:
vines.append("Serial Number:")
lines.append(order_instance.serial)
vines.append("Customer Name:")
lines.append(order_instance.customer_name)
vines.append("Order Date:")
lines.append(order_instance.order_date)
vines.append("From Location:")
lines.append(order_instance.from_location)
vines.append("To Location:")
lines.append(order_instance.to_location)
vines.append("Destination:")
lines.append(order_instance.destination)
vines.append("Load Type:")
lines.append(order_instance.load_type)
vines.append("Customer Address:")
lines.append(order_instance.customer_address)
vines.append("Vehicle:")
lines.append(order_instance.vehicle)
vines.append("Driver:")
lines.append(order_instance.driver)
for line in lines:
textob.textLine(f'{line}')
for vine in vines:
textoc.textLine(f'{vine}')
c.setFont("Helvetica", 30)
c.setFillColorRGB(0, 0, 1)
c.drawString(50, 30, "Shahina")
c.setFont("Helvetica", 30)
c.drawString(200,60, "Bill of Landing")
c.setFillColorRGB(0, 0, 0)
c.line(0, -0.7 * inch, 6.8 * inch, -0.7 * inch)
c.drawText(textob)
c.drawText(textoc)
c.showPage()
c.save()
buf.seek(0)
#return
return FileResponse(buf, as_attachment=True, filename='order.pdf')

[@plus2net1]

@@malikkamil6596 Can't read must, what I understand is you have two lists, then just join them by using List append or by using pandas . Once you have a single list then use the same.

[@malikkamil6596]

@@plus2net1 when i print report data from list print in colum form how can i print it to row

[@plus2net1]

Best way is using Pandas merger the list . Details are here.
www.plus2net.com/python/pandas-dataframe-merge.php
Or if you can manage the source then while getting the lsit use query to get one output by joining tables.

[@malikkamil6596]

@@plus2net1 thanks a lot very helpful