Save QGIS project to PostgreSQL/PostGIS database using PyQGIS

Question

I'm currently working on some code to automate the creation of a QGIS project from tables in a PostgreSQL DB. For info I'm working in QGIS 3.4.

I'm almost there, but I'm stuck on one thing

How do I save the project back into postgreSQL?

The code I've put together so far:

# CONNEXION 
# This works fine

    uri = QgsDataSourceUri()
    DB = "MCD_1"
    user = "user1"
    MP = "XXXX"
    host = "XXX.XXX.XX.XXX"
    port = "XXXX"
    schema = "XXXX"

    uri.setConnection(host, port, DB, user, MP)

# OUTPUT FOLDER
# Here I define the ouput options - saving to local works ok but I'm not sure how to modify the 'PG_OUTPUT' to save into the DB

    LOC_OUTPUT = 'C:/Users/user1/Docs/SIG/test_output/loc_ouput.qgz'
    PG_OUTPUT = 'uri.setConnection(host, port, DB, user, MP)/TEST.qgz'  

# LOAD LAYERS
# works ok

    uri.setDataSource(schema, "DB_table_A", "geom")
    layer = QgsVectorLayer(uri.uri(False), "SIG_table_A", "postgres")
    QgsProject.instance().addMapLayer(layer)

    uri.setDataSource(schema, "DB_view_A", "geom","", cb_PrimaryKey)
    layer = QgsVectorLayer(uri.uri(False), "SIG_view_A", "postgres")
    QgsProject.instance().addMapLayer(layer)

# CREATE AND PLACE LAYERS IN GROUPS
# works ok

    root = QgsProject.instance().layerTreeRoot()

    Group_1="Tables"
    group = root.addGroup(Group_1)

    table_1 = QgsProject.instance().mapLayersByName("SIG_table_A")[0]
    root = QgsProject.instance().layerTreeRoot()
    layer = root.findLayer(table_1.id())
    clone = layer.clone()
    group.insertChildNode(0, clone)
    root.removeChildNode(layer)


    Group_2="Views"
    group = root.addGroup(Group_2)

    view_1 = QgsProject.instance().mapLayersByName("SIG_view_A")[0]
    root = QgsProject.instance().layerTreeRoot()
    layer = root.findLayer(view_1.id())
    clone = layer.clone()
    group.insertChildNode(0, clone)
    root.removeChildNode(layer)

## SAVING PROJECT
# Here I just modify the variable in project.write() between PG_OUTPUT and  LOC_OUTPUT - to save locally or in the DB.

    project = QgsProject.instance()
    project.write(PG_OUTPUT)

sources:

https://docs.qgis.org/3.4/pdf/en/QGIS-3.4-PyQGISDeveloperCookbook-en.pdf

Creating Proj and layers:

QGIS Python script for creating project file

https://gis.stackexchange.com/search?q=grouping+layers+pyqgis

Grouping layers using PyQGIS makes them not visible on canvas?

Adding layer to group in layers panel using PyQGIS?

QGIS 3.2 have a new option for save projects to postgres http://www.bostongis.com/blog/index.php?/archives/271-New-in-QGIS-3.2-Save-Project-to-PostgreSQL.html — Fran Raga, Apr 26 '19 at 10:50
Yes, that's basically what I'm trying to do - but through the python console :) — lhowarth, Apr 26 '19 at 11:44
you need change settings value then.I can post an answer but I have not tried it if you want — Fran Raga, Apr 26 '19 at 12:27
try using settings = QgsSettings() baseKey = "/PostgreSQL/connections/" + connName; settings.setValue( baseKey + "/projectsInDatabase", True ) ,based on core code https://github.com/qgis/QGIS/blob/master/src/providers/postgres/qgspgnewconnection.cpp#L75 — Fran Raga, Apr 26 '19 at 12:35
Make sure you have at least PostGreSQL 9.4. It won't work with older versions. https://issues.qgis.org/issues/21148 — Michel Stuyts, May 03 '19 at 11:18
cheers for the replies - I still haven't worked it out yet - the core code is a bit beyond me (I'm still learning python) - otherwise I'm running postgresql 11 (so it should be ok) — lhowarth, May 03 '19 at 13:57

Germán Carrillo · Answer 1 · 2021-12-19T01:36:18.563

You're very close. The only issue I see with your code is the PG_OUTPUT variable, which should actually be a URI like this:

uri = 'postgresql://user:pass@localhost:5432?dbname=my_db&schema=my_schema&project=my_project'

If you have the URI in that way, you can just call:

QgsProject.instance().write(uri)

And now your project will be stored in your DB.

Save QGIS project to PostgreSQL/PostGIS database using PyQGIS

1 Answers1

Linked