[GIS] Save QGIS project to PostgreSQL/PostGIS database using PyQGIS

postgispostgresqlpyqgispyqgis-3qgis-3

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?

Best Answer

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.

Related:

Related Question