1

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

  • To work with PostGIS Databases in Python you need to use psycopg2 python module. Please, see my answer. – xunilk Jul 10 '17 at 22:09
2

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)
  • 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. – Luismi Jul 11 '17 at 6:31
  • @Luismi, can you provide your ogr2ogr solution as an answer? – artwork21 Jul 11 '17 at 15:45
  • @artwork21 I provided my ogr2ogr solution as Editing Note. It works perfectly. – xunilk Jul 11 '17 at 15:56
  • It's the same as I used :D – Luismi Jul 11 '17 at 16:13
  • @Luismi So, try with another layer. My two procedures produced successful result. – xunilk Jul 11 '17 at 16:17
0

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)

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.