Empty layer after import into PostGIS Database. Pyqgis

Question

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

• xunilk: To work with PostGIS Databases in Python you need to use psycopg2 python module. Please, see my answer.

Answer 1

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)

• Luismi: Thanks a lot for your complete and detailed answer! But I had to workaround with ogr2ogr utility. With just a few lines I'm able to import layers programatically to PostGIS.
• artwork21: @Luismi, can you provide your ogr2ogr solution as an answer?
• xunilk: @artwork21 I provided my ogr2ogr solution as Editing Note. It works perfectly.
• Luismi: It's the same as I used :D
• xunilk: @Luismi So, try with another layer. My two procedures produced successful result.

Answer 2

This method worked well to import layers to PostGIS:

dbname= 'dbname'
host= 'localhost'
user= 'postgres'
password= 'password'
port= '5432'
vlayerPath='path/to/shapeFile/file.shp'
import os
command = "ogr2ogr -lco GEOMETRY_NAME=geom -a_srs epsg:25830 -f 'PostgreSQL' PG:'dbname=" + dbname + \
 " host=" + host + \
 " user=" + user + \
 " password=" + password + \
 " port=" + port + "'" + \
 " '"+ vlayerPath+"'"
os.system(command)