I made a pyQGIS script to backup the PostGIS tables of my team. I needed to save each table style in the Gpkg's layer_styles.

Here's an extract of how I've done but I would prefer to implement the pyQGIS one.

# Init QGIS
qgs = QgsApplication([], False)
qgs.setPrefixPath("C:/ProgGIS/QGIS314/apps/qgis", True)

# PostGIS datasource
uri = QgsDataSourceUri()
uri.setConnection(myServeur, "5432", myBase, myUser, myPass)
uri.setDataSource('public', 'layer_styles', None)
# Importing the table layer_styles
couche_styles = QgsVectorLayer(uri.uri(False), 'layer_styles', "postgres")
couche_styles.setSubsetString(' "f_table_catalog" = \'myBase\' AND "f_table_schema" = \'mySchema\' AND "f_table_name" IN (\'table1\',\'table2\',\'table3\',\'table4\')')
# Exporting the table layer_styles to the gpkg

#exporting the other layers

# Geopakagization of the gpkg's layer_styles (Empying id, f_table_catalog and f_table_schema)
connection_gpkg = sqlite3.connect(my_geopackage)
cursor_gpkg = connection_gpkg.cursor()
cursor_gpkg.execute("UPDATE layer_styles SET id = '', f_table_catalog = '', f_table_schema = ''")

# Quiting QGIS
  • What is the issue?
    – JGH
    Jul 24 '20 at 11:55
  • The job is done but I would prefer using QGis lib
    – aurel_nc
    Jul 24 '20 at 12:03

You may choose using Qt approach e.g using recipe from Connecting QGIS, SpatiaLite and Python? (change import to from qgis.PyQt.QtSql import *)

You may also want to use the new "Connections API" mentioned in Access SpatiaLite connections with PyQGIS QgsProviderMetadata (QGIS 3.10.7 LTR) combined with it executeSql method

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.