Possible to automatically export atlases from QGIS containing PostgreSQL views/tables?

Question

I've managed to export several atlases automatically from QGIS with a Python and a .bat file, see Export several configured atlases with a Python script

I'm using QGIS 3.10.2 on a Windows 10 PC and I'm connecting to PostgreSQL 9.5.2.

The QGIS project (and layouts/atlases) only contains shapefiles and WMS.

When I try to do the same thing with layouts containing PostgreSQL views and tables, no JPEG files are created. May it be due to PostgreSQL connecting issues?

The username and password for the PostgreSQL/PostGIS connection is stored in the QGIS project (using Authentication manager when creating a new PostGIS connection). (In addition username and password are stored in pgpass.conf.)

I've tried the code from @Matthias Kuhn (@Matthias Kuhn ) (host, tablename, username etc. are not the real ones used in my actual code)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# linja over bruker vi for å kunne bruke æøå uten advarsler.

import os
from qgis.core import QgsApplication, QgsProject, QgsLayoutExporter, QgsDataSourceUri, QgsCredentials

#from PyQt5.QtSql import QSqlDatabase

# Initialize QGIS Application
QgsApplication.setPrefixPath("C:\\OSGeo4W64\\apps\\qgis", True)

# # Start a QGIS application without GUI
qgs = QgsApplication([], False)
qgs.initQgis()

uri = QgsDataSourceURI()
# assign this information before you query the QgsCredentials data store
uri.setConnection("HOST", "PORT", "DB_NAME", None, None)
connInfo = uri.connectionInfo()

(success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)

if success:
    uri.setPassword(passwd)
    uri.setUsername(user)
    uri.setDataSource("SCHEMA", "TABLE_NAME", "the_geom")
    QgsVectorLayer(uri.uri(), "<name of view>", "postgres")

    project_path = 'C:\\adhoc\\lst/lstp.qgs'
    output_folder = 'C:\\adhoc\\lst\\n5g\\'
    layout_name = 'n5 NGO48 sone G'

    # Open existing project
    project = QgsProject.instance()
    project.read(project_path)

    print('Project ' + project.fileName() + ' loaded successfully')

    # Open prepared layout that as atlas enabled and set
    layout = project.layoutManager().layoutByName(layout_name)

    # Export atlas
    exporter = QgsLayoutExporter(layout)
    settings = QgsLayoutExporter.ImageExportSettings()
    exporter.exportToImage(layout.atlas(),output_folder, 'jpg', settings)

    # Close the QGIS application
    qgs.exitQgis()

I also had a shot at @J. Montecolo (@J. Montecolo) including connInf, but connection fails.

...
from qgis.core import QgsApplication, QgsProject, QgsLayoutExporter, QgsDataSourceUri, QgsCredentials

from PyQt5.QtSql import QSqlDatabase

# Initialize QGIS Application
QgsApplication.setPrefixPath("C:\\OSGeo4W64\\apps\\qgis", True)

# # Start a QGIS application without GUI
qgs = QgsApplication([], False)
qgs.initQgis()

host = "HOST"
port = 5432
database = "DB_NAME"

db = QSqlDatabase.addDatabase("QPSQL")
db.setHostName(host)
db.setPort(port)
db.setDatabaseName(database)

uri = QgsDataSourceUri()
# assign this information before you query the QgsCredentials data store
uri.setConnection("HOST", "PORT", "DB_NAME", None, None)
connInfo = uri.connectionInfo()

(success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
if success:
    db.setUserName(username)
    db.setPassword(password)

if db.open():
    print("Connection successful !")
else:
    print("Connection failed.")
...

Is it possible to automatically export layouts containing PostgreSQL views/tables? If yes, how may I adjust the Python code?

It is definytely possible... But from your question, it is not clear what you want to do with that PostGIS layer. In first code example your QgsVectorLayer is not asigned to variable, thus not used later on... Please describe what you want to achieve with that layer... — DavidP, Feb 24 '20 at 13:55
So I tested the code you link to (for my answer there). And I am using PostgreSQL layers. The export works without issue, QGIS stores the connection details in the project. When adding the PostgreSQL connection in the first place did you "save" the username and password? — HeikkiVesanto, Feb 24 '20 at 14:06
@DavidP: I have created an atlas layouts involving 2 views and 1 table added from a postgreSQL-connection. When I manually export the atlas, the jpg-images are created. When I do it from a .bat-file calling the .py-file, no jpg-images are created. — 9ls1, Feb 24 '20 at 16:29
@HeikkiVesanto: This brings hope. I have only tried Authentication manager (for not having the username and password revealed as text in the project-file). However, I'll reconnect and save username and password. — 9ls1, Feb 24 '20 at 16:34

score 0 · Accepted Answer · answered Feb 24 '20 at 18:09

@HeikkiVesanto were right. The same (corrected) code as in the link works for postgrSQL-layers. I disconnected the postgreSQL host, schema and database. Then I reconnected to postgreSQL. This time I saved the username and password (not using Authentication . Then I added the views and table from postgreSQL and created my atlas layouts once again and saved it in a new project-file *.qgz. I could then reuse my .py and .bat file mentioned in the link in the original question and repeated here (including the \ at the end of output_folder to save the jpg-files in the subfolder n5e):

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# linja over bruker vi for å kunne bruke æøå uten advarsler.

import os
from qgis.core import  QgsApplication, QgsProject, QgsLayoutExporter

def export_atlas(qgs_project_path, layout_name, outputs_folder):

    # Open existing project
    project = QgsProject.instance()
    project.read(qgs_project_path)

    print('Project in ' + project.fileName() + ' loaded successfully')

    # Open prepared layout that as atlas enabled and set
    layout = project.layoutManager().layoutByName(layout_name)

    # Export atlas
    exporter = QgsLayoutExporter(layout)
    settings = QgsLayoutExporter.ImageExportSettings()
    exporter.exportToImage(layout.atlas(),outputs_folder, 'jpg', settings)

def main():
    # Start a QGIS application without GUI
    qgs = QgsApplication([], False)
    qgs.initQgis()

    project_path = 'C:\\adhoc\\lst\\lstp.qgz'
    output_folder = 'C:\\adhoc\\lst\\n5e\\'
    layout_name = 'n5 ngo48 sone e'
    print('Starter atlas-eksport')
    export_atlas(project_path, layout_name, output_folder)

    # Close the QGIS application
    qgs.exitQgis()

if __name__ == "__main__":
    main()

Possible to automatically export atlases from QGIS containing PostgreSQL views/tables?

1 Answers1