[GIS] Empty layer after import into PostGIS Database. Pyqgis

importpostgispyqgis

I'm facing troubles to import a layer into a PostGIS database with pyqgis in a script. Here is my code:

Nombre_BBDD='test'
host='localhost' 
puerto='5432'
usuario='postgres'
password='xxxxxxx'
Nombre_Tabla='table'
Llave_primaria='id'
Capa_a_procesar='/xxxxxxx/shapefile.SHP'
Tipo_geometria='POLYGON'

capaImportar = QgsVectorLayer(Capa_a_procesar, 'capaImportar', 'ogr')
uri = """dbname='"""+Nombre_BBDD+"""' host='"""+host+"""'    port="""+puerto+""" user='"""+usuario+"""' password='"""+password+"""' key="""+Llave_primaria+""" type="""+Tipo_geometria+""" table=\"public\".\""""+Nombre_Tabla+"""\" (geom) sql="""
crs = QgsCoordinateReferenceSystem(25830)
error = QgsVectorLayerImport.importLayer(capaImportar, uri, "postgres", crs, False, True)

I was trying to do it how is recommended in this link.

It seems everything is right, but when I try to load the layer this is empty and the python console throws this error:

Exception RuntimeError: 'wrapped C/C++ object of type PGVectorTable has been deleted' in bound method PGTableDataModel.del of db_manager.db_plugins.postgis.data_model.PGTableDataModel object at 0x7f3f24190d60 ignored

I'm working with qgis 2.18.10 in a Kubuntu 16.04

Best Answer

To work with PostGIS Databases in Python you need to use psycopg2 python module. This is automatically installed when you got Postgres+Postgis in your system.

As you work in a Linux system, you can create an individual user (instead 'postgres' user) and database for your test. As superuser: 

# su - postgres
$ psql
psql (8.4.17)
Digite «help» to get help.

postgres=#

Next, I created my new user with privileges of superuser for being able to add postgis extension in new database (in this case utah).

postgres=# CREATE USER your_user WITH PASSWORD 'your_password' SUPERUSER;
CREATE ROLE
postgres=# CREATE DATABASE utah OWNER zeito;
CREATE DATABASE

After created database utah, I used pgadmin3 to create a new connection and to add postgis extension to this database.

Afterward, I tried out next script (change asterisks for your password and your correspondents parameters) in Python Console of QGIS:

import psycopg2

try:

    conn = psycopg2.connect(dbname='utah', 
                            host='localhost',
                            port=5432, 
                            user='zeito',
                            password='********')

except:
    print "I am unable to connect to the database"

cur = conn.cursor()

layer = iface.activeLayer()
feats = [feat for feat in layer.getFeatures()]
ls = feats[0].geometry().asWkb().encode('hex')

# Send it to PostGIS
cur.execute('CREATE TABLE route(geom geometry, name text)')
cur.execute(
    'INSERT INTO route(geom, name)'
    'VALUES (ST_SetSRID(%(geom)s::geometry, %(srid)s), %(name)s)',
    {'geom': ls, 'srid': 32612, 'name': 'route'})

conn.commit()  # save data

print "Done"

to put 'route' layer into database 'utah' (active layer in layers panel of QGIS):

enter image description here

After running the code at Python Console of QGIS (observe at above image that it ended with word 'Done' and any error):

enter image description here

I clicked in 'elephant' icon, 'Manage Layers Toolbar' of QGIS, for adding new postgis layer; as it can be observed at next image. It worked.

enter image description here

Editing Note:

This is my ogr2ogr solution and it works perfectly (with another shapefile):

import os

dbname = 'utah'
host = 'localhost'
user = 'zeito'
password = 'my_password'
port = '5432'

command = "ogr2ogr -lco GEOMETRY_NAME=geom -a_srs epsg:32612 \
          -f 'PostgreSQL' PG:'dbname=" + dbname + \
          " host=" + host + \
          " user=" + user + \
          " password=" + password + \
          " port=" + port + "'" + \
          " /home/zeito/pyqgis_data/line1.shp"

os.system(command)
Related Question