Print Composer using input from several Spatialite-tables with PyQgis

Question

How can I create PDFs out of QGIS using the Print Composer Templates that include a lot of text (from a text-template) and within the text several information comes out of my spatiallite-database. A map is also part of it, but I am only having problems with the spatialite-text-inputs.

Workflow:

• User Selects a Polygon (This is the only layer that is loaded)
• Users presses a „Print this for me“ Plugin Button (Using the Polygon-Name I get out all the information I need from the tables in my Spatialite DB)
• User gets a wonderful PDF

The table that includes the polygon is different from the tables (yes, there are several) that have all the other information, this is due to data-entry reasons.

I already have a solution using QWebView(), but my solution is not very good for composing the map itself and if a user wants to change my templates, it is a bit complicated. Here my solution:

import sys
from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtWebKit import *
import sqlite3

# create web view
webview = QWebView()

connection = sqlite3.connect('D:\allthedata.sqlite')
cursor = connection.cursor()
cursor.execute('SELECT name FROM information WHERE polygon_nr = 2015')
for row in cursor.fetchall():
    name = row[0]
# I use 15 inputs, but to simplify this, I am only using „name“ here

# get template and push into webview
template_txt_temp = open("c:/template1.txt") #I have several templates
template_txt = template_txt_temp.read()
html = template_txt.replace('$name$', name)

# the template ist just some html, but can have several pages;  html →
# <p>Hello, this is just a dummy text, I want to show you, where I
# want so insert the $name$ from the database</p>

print html
print type(html)

webview.setHtml(html)

printer = QPrinter()
printer.setPageSize(QPrinter.A4)
printer.setOutputFormat(QPrinter.PdfFormat)
printer.setOutputFileName(r'C:\report.pdf')
webview.print_(printer)

And out of it I get a nice PDF. So the essential question: How do I get the "name" information out of my database and into the Print Composer? (And is my Question even understandable?) Maybe this picture helps:

Answer 1

I would suggest you perform a join of the relevant non spatial tables with your spatial layer. Do this by opening the properties for your spatial table. You then open up the Join tab and add the relevant joins.

If your tables do not share a common key you could create a supporting table which you then use for each of your map plates. This table is then linked to relevant objects in the other tables. You could name it tbl_mapcoverage and your attributes could be something like this:

• id - uniquely identifies your plate)
• geom - a polygon which sets the focus area for your map. It does not have to be visible.
• ref_breadandbutterprices - reference to table with non-spatial information
• ref_conveniencestore - reference to table with geom containing referencing one or more shops.

I am assuming tha tyou are aware of how the QGIS atlas functionality]2 works and that you are using it to complete this task.

If you want to include a one to many relation you could pull this data in by using a python script and generate a html-table with your data using a function.