11

Can anybody help me to create a QGIS processing script that adds a sequence to an existing unique identifier column (type: integer) in PostGIS?

This would be quite helpful, e.g. as a workaround for bug #6798. Unfortunately, I do not have any Python experience.

enter image description here

enter image description here

CREATE SEQUENCE /*input_schema*/./*input_table*/_/*uic*/_seq OWNED BY /*input_schema*/./*input_table*/./*uic*/;
SELECT SETVAL('/*input_schema*/./*input_table*/_/*uic*/_seq', (SELECT MAX(/*uic*/) FROM /*input_schema*/./*input_table*/));
ALTER TABLE /*input_schema*/./*input_table*/
ALTER COLUMN /*uic*/ SET DEFAULT nextval('/*input_schema*/./*input_table*/_/*uic*/_seq'::regclass);
3
  • 1
    I would ask why in your workflow, and the workflow described in the bug, you're not managing your PostgreSQL data using PGAdmin or other core admin tools for postgresql? I don't know why there is effort spent making this work in QGIS when the admin tools do it just fine! Dec 15, 2016 at 19:27
  • To me, managing tables in the QGIS DB-Manager is quite intuitive. However, I'm also interested to see how a processing script can run PostGIS queries. Dec 15, 2016 at 20:10
  • 3
    For us, PGAdmin and the SQL window are more of our "GIS" than QGIS! QGIS is just the visual client to our spatial data and outputs - all the work, including 'geo'-processing, scripts, etc. are done outside of QGIS... the tools that exist to do this are already perfected, and really, the workflow of using these non-QGIS tools with PostgresSQL/PostGIS data is a better practice... Dec 15, 2016 at 20:18

3 Answers 3

7
+100

Provided your SQL statement produces valid results, the scripts below should do what you are after. Unfortunately I have nothing at hand to test this, but you could try and give feedback.

I tried to comment it for convenience, basically the script carries out three steps:

  • obtain database connection parameters for the selected layer (should be postgres)
  • fill the connection parameters in the sql statement string
  • execute the sql statement

Note the protocol output of the script.

#!/usr/bin/env python
# -*- coding: utf-8 -*-

#--------- define Interface
##[my_scripts]=group
##Add Serial to PostgreSQL Table=name
##Postgres_Table=vector
##Unique_identifier_column=string replace_this_with_your_uic

from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4 import *

from qgis.core import *
from qgis.gui import *
from qgis.utils import *

import psycopg2

#get the parameters for the tpg table into a directory
#get the table
pg_table = processing.getObject(Postgres_Table)
#create empty dictionary for key/value pairs of the tables connection parameters
db_params = {}
db_params['uic'] = Unique_identifier_column
#iterate over connection string
progress.setInfo(20*'-' + '  Connection parameters')
for param in pg_table.dataProvider().dataSourceUri().split(' '):
    key_val = param.split('=')
    progress.setInfo(str(key_val))
    try:
        #set key/value pair
        db_params[key_val[0]] = key_val[1]
    except:
        pass

#generate the sql statement string
#the text in round brackets are the keys from the db_params dictionary created above
#the values belonging to the keys are inserted into the string
progress.setInfo(20*'-' + '  SQL statement')
sql = """CREATE SEQUENCE %(table)s_%(uic)s_seq OWNED BY %(table)s.%(uic)s;
            SELECT SETVAL(%(table)s_%(uic)s_seq, (SELECT MAX(%(uic)s) FROM %(table)s));
            ALTER TABLE %(table)s
            ALTER COLUMN %(uic)s SET DEFAULT nextval(%(table)s_%(uic)s_seq::regclass);""" % db_params
#remove double quotes
sql = sql.replace('"','') 
progress.setInfo(sql)

#make connection string
constr = """dbname=%(dbname)s host=%(host)s port=%(port)s user=%(user)s     password=%(password)s""" % db_params
progress.setInfo(20*'-' + '  DB Connection string')
progress.setInfo(constr)
#make db connection
con = psycopg2.connect(constr)
cur = con.cursor()
#execute the above created sql statement
progress.setInfo(20*'-' + '  Executing SQL statement ...')
cur.execute(sql)
progress.setInfo(20*'-' + '  ... done.')
14
  • I've tested the script and the processing log says unexpected indent (, line 32) See log for more details. Is there something I'm doing wrong? The SQL statement is working in the DB-Manager. Dec 20, 2016 at 11:09
  • File "C:/Users/abc/.qgis2/python/plugins\processing\core\GeoAlgorithm.py", line 230, in execute self.processAlgorithm(progress) File "C:/Users/abc/.qgis2/python/plugins\processing\script\ScriptAlgorithm.py", line 298, in processAlgorithm exec((script), ns) File "<string>", line 32 try: ^ Dec 20, 2016 at 11:12
  • Yep, my fault. The try statement had wrong indentation. Just fixed this. Dec 20, 2016 at 11:52
  • Thx for fixing this, but I get a Python error when running the script. Dec 20, 2016 at 12:10
  • Traceback (most recent call last): File "C:/Users/abc/.qgis2/python/plugins\processing\gui\AlgorithmDialog.py", line 219, in accept if runalg(self.alg, self): File "C:/Users/abc/.qgis2/python/plugins\processing\gui\AlgorithmExecutor.py", line 51, in runalg alg.execute(progress) File "C:/Users/abc/.qgis2/python/plugins\processing\core\GeoAlgorithm.py", line 244, in execute unicode(e) + self.tr('\nSee log for more details')) UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 44: ordinal not in range(128) Dec 20, 2016 at 12:10
5

It is worth noting, that python module psycopg2 doesn't seem to automatically COMMIT a transaction (as other clients like QGIS DB Manager or pgAdmin do), therefore the COMMIT statement has to be part of the sql string in the script.

This does not matter with SELECT statements because in theses cases a COMMIT is obviously carried out when getting results via cur.fetchall().

This is a reworked version of the script from my answer above:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

#--------- define Interface
##[my_scripts]=group
##Add Serial to PostgreSQL Table=name
##Postgres_Table=vector
##Unique_identifier_column=field Postgres_Table

from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4 import *

from qgis.core import *
from qgis.gui import *
from qgis.utils import *

import psycopg2

#get the parameters for the tpg table into a directory
#get the table
pg_table = processing.getObject(Postgres_Table)
#create empty dictionary for key/value pairs of the tables connection parameters
db_params = {}
db_params['uic'] = Unique_identifier_column
#iterate over connection string
progress.setInfo(20*'-' + '  Connection parameters')
for param in pg_table.dataProvider().dataSourceUri().split(' '):
    key_val = param.split('=')
    progress.setInfo(str(key_val))
    try:
        #set key/value pair
        db_params[key_val[0]] = key_val[1]
    except:
        pass

#generate the sql statement string
#the text in round brackets are the keys from the db_params dictionary created above
#the values belonging to the keys are inserted into the string
progress.setInfo(20*'-' + '  SQL statement')
sql = """CREATE SEQUENCE %(table)s_%(uic)s_seq OWNED BY %(table)s.%(uic)s;
SELECT SETVAL('%(table)s_%(uic)s_seq', (SELECT MAX(%(uic)s) FROM %(table)s)); 
ALTER TABLE %(table)s ALTER COLUMN %(uic)s SET DEFAULT nextval('%(table)s_%(uic)s_seq'::regclass);
COMMIT;""" % db_params
#remove double quotes
sql = sql.replace('"','') 
progress.setInfo(sql)

#make connection string
constr = """dbname=%(dbname)s host=%(host)s port=%(port)s user=%(user)s     password=%(password)s""" % db_params
progress.setInfo(20*'-' + '  DB Connection string')
progress.setInfo(constr)
#make db connection
con = psycopg2.connect(constr)
cur = con.cursor()
#execute the above created sql statement
progress.setInfo(20*'-' + '  Executing SQL statement ...')
cur.execute(sql)
progress.setInfo(20*'-' + '  ... done.')
4

There seems to be a similar plugin already (although it creates a new unique ID field for you, rather than creating a sequence.)

This assumes you already have a unique id field (this doesn't need to be numeric), but want a simple numeric ID instead (1,2,3..)

In Processing toolbox, go to Scripts > Tools > Get Scripts from Online...

Expand "Not installed" and choose "EquivalentNumField" . Remember to click the checkbox before clicking on OK. That caught me out... ;-)

enter image description here

To find it quickly, type "Equiv" in the processing search bar, and you should be able to double-click it from there.

enter image description here

Here's an example. These woods had a unique field (osm_id) but the plugin has added a NUM_FIELD with simple numeric values instead

enter image description here

2
  • Steve, this is a useful script, but I'm looking for something different. Dec 19, 2016 at 18:56
  • @eclipsed_by_the_moon how is this answer not what you are looking for? It appears to ultimately solve your problem of needing a unique identifier column.
    – kttii
    Dec 19, 2016 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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