Exporting shapefile into PostGIS via Python script from QGIS

Question

I want to export a shapefile into PostGIS via a Python script from QGIS. I’m using QGIS 3.10, PostgreSQL 10 and PostGIS 2.5 on Windows 10. I tried go use qgis:importintopostgis:

import processing
processing.run("qgis:importintopostgis",{
                'INPUT': 'unioncrs',
                'DATABASE': "HistoPG",
                'SCHEMA': "public",
                'TABLENAME': "testunion",
                'PRIMARY_KEY': "Nummer"
                })

That produces an error message:

“.... There were errors executing the algorithm.”

Interestingly it works with the DB Manager, which seems to be a GUI of qgis:importintopostgis.

The differences which I can see are the Source and the Target SRIDs. I don’t know how to enter these parameters in qgis:importintopostgis. I’m also not sure if I wouldn’t need to enter username and pasword for the database, but again I don’t see where to enter these parameters in qgis:importintopostgis.

I also tried `gdal:importvectorintopostgisdatabaseavailableconnections:

processing.run("gdal:importvectorintopostgisdatabaseavailableconnections",{
                'DATABASE': "HistoPG",
                'INPUT': 'C:/QGIS/Vers1/Results/unioncrs.shp',
                'GTYPE': 0,
                'SCHEMA': "public",
                'TABLE': "testunion"
                })

That runs without error message but also doesn’t import a table into PostGIS.

Is there a method which works with Python from QGIS?

I run:

PARAMETERS = {'DATABASE':'HistoPG','INPUT':'C:\\QGIS\\Vers1\\Results\\unioncrs.shp', 
'SHAPE_ENCODING':'utf-8','GTYPE':0,'A_SRS':QgsCoordinateReferenceSystem('EPSG:31467'),'T_SRS':None,'S_SRS':None,
    'SCHEMA':'public','TABLE':'testunion','PK':'Nummer','PRIMARY_KEY':'','GEOCOLUMN':'geom','DIM':0,'SIMPLIFY':'',
   'SEGMENTIZE':'','SPAT':None,'CLIP':False,'WHERE':'','GT':'','OVERWRITE':True,'APPEND':False,
    'ADDFIELDS':False,'LAUNDER':False,'INDEX':False,'SKIPFAILURES':False,'PROMOTETOMULTI':True,
    'PRECISION':True,'OPTIONS':''}

processing.run("gdal:importvectorintopostgisdatabaseavailableconnections", PARAMETERS)

I then get the following error message:

Traceback (most recent call last): File "C:\PROGRA~2\QGIS3~1.10\apps\Python37\lib\code.py", line 90, in runcode exec(code, self.locals) File "", line 1, in File "", line 98, in File "C:/PROGRA~2/QGIS3~1.10/apps/qgis-ltr/./python/plugins\processing\tools\general.py", line 106, in run return Processing.runAlgorithm(algOrName, parameters, onFinish, feedback, context) File "C:/PROGRA~2/QGIS3~1.10/apps/qgis-ltr/./python/plugins\processing\core\Processing.py", line 181, in runAlgorithm raise QgsProcessingException(msg) _core.QgsProcessingException: There were errors executing the algorithm.

It creates a table in my PostGIS database. But the table is empty and has only the fields ‚nummer‘ and ‚geom‘.

Do I have to define the fields somewhere in the parameters or do I have to append to a table which has the fields already?

I found the History, that’s good to know. But it doesn’t log when I run DB Manager. And I didn’t find any other plugin to import a vector layer into PostGIS.

Which one did you refer to?

I found the function now in the Processing toolbox. I ran it successfully and used the text from the history in Python. That produced after a while an error message „...There were errors executing the algorithm.“ But it had exported the table into PostgreSQL. The number of features is correct and the table looks alright so far. So I’m not quite sure what went wrong actually.

Answer 1

For the record. An example of a Python function using OGR2OGR outside QGIS of a OSGEO QGIS dist. I used in a disk walk finding 900 shape files in recursive directories and uploaded to PostGIS with no error. To simplify the code I removed some GUID table naming to avoid duplicate table names.

def upload_to_db(shpfile):
    """
    Uploades shape files to PostgreSQL with org2ogr
    :param shpfile: Shapefile to upload to database
    :return: None
    """

    dbname = 'mydb'
    schema = 'myschema'
    host = 'xx.xx.xxx.xx'
    user = 'myuser'
    password = 'mypw'
    port = '5432'

    tablename = f'{os.path.basename(shpfile)}'

    # https://gdal.org/drivers/vector/pg.html
    command = f"""SET PGCLIENTENCODING=LATIN1 && ogr2ogr -f "PostgreSQL" PG:"host={host} dbname={dbname} user={user} password={password} port={port}" "{shpfile}" -nln {schema}.{tablename} -lco geometry_name=geom -lco precision=NO -nlt promote_to_multi -a_srs epsg:25832"""

    #print(command)
    os.system(command)

Answer 2

You were already on the right path:

To export a Shapefile from QGIS 3 to PostGIS through pyqgis you could use following code:

import processing
PARAMETERS = {'DATABASE':'nameofdatabase','INPUT':'C:\\Users\\yourpath\\shapename.shp|layername=shapename',
    'SHAPE_ENCODING':'utf-8','GTYPE':5,'A_SRS':QgsCoordinateReferenceSystem('EPSG:4326'),'T_SRS':None,'S_SRS':None,
    'SCHEMA':'schemaname','TABLE':'filenameinpostgis','PK':'id','PRIMARY_KEY':'','GEOCOLUMN':'geom','DIM':0,'SIMPLIFY':'',
    'SEGMENTIZE':'','SPAT':None,'CLIP':False,'WHERE':'','GT':'','OVERWRITE':True,'APPEND':False,
    'ADDFIELDS':False,'LAUNDER':False,'INDEX':False,'SKIPFAILURES':False,'PROMOTETOMULTI':True,
    'PRECISION':True,'OPTIONS':''}

processing.run("gdal:importvectorintopostgisdatabaseavailableconnections", PARAMETERS)

Since there is also a Plugin to import vector layer into PostGIS, you can execute that and then see the code from History Protocol.

The plugin is the GDAL plugin from Processing Toolbox "Export to PostgreSQL (available connections)"

Plugin looks like following, and it would probably easier to use this first and after checking the History of executed processes, implement your code.

Here is a link to documentation from QGIS 2 QGIS 2.6 docs

Here is a link to documentation for QGIS 3 QGIS 3.10 docs

Your Code with:

 'GTYPE': 0

means no geometry type btw. 5 would be polygon.

More info are in the links to QGIS 2 or QGIS 3 documentation about parameters.