pg_create_sequence_qgis_2x.py
1 |
#!/usr/bin/env python
|
---|---|
2 |
# -*- coding: utf-8 -*-
|
3 |
|
4 |
# ----- define Interface -----
|
5 |
##PostgreSQL/PostGIS administration (QGIS 2.x)=group
|
6 |
##Create sequence as default value=name
|
7 |
##Input_layer=vector
|
8 |
##Input_column=field Input_layer
|
9 |
|
10 |
from PyQt4.QtCore import * |
11 |
from PyQt4.QtGui import * |
12 |
from PyQt4 import * |
13 |
|
14 |
from qgis.core import * |
15 |
from qgis.gui import * |
16 |
from qgis.utils import * |
17 |
|
18 |
import psycopg2 |
19 |
import re |
20 |
|
21 |
# ----- get input parameters from GUI -----
|
22 |
inlayer = processing.getObject(Input_layer) |
23 |
inlayer_dp = inlayer.dataProvider().dataSourceUri() |
24 |
|
25 |
# get database parameters ('Input layer a')
|
26 |
db_name = QgsDataSourceURI(inlayer_dp).database() |
27 |
db_host = QgsDataSourceURI(inlayer_dp).host() |
28 |
db_user = QgsDataSourceURI(inlayer_dp).username() |
29 |
db_password = QgsDataSourceURI(inlayer_dp).password() |
30 |
|
31 |
# get layer a parameters
|
32 |
inlayer_schema = QgsDataSourceURI(inlayer_dp).schema() |
33 |
inlayer_table = QgsDataSourceURI(inlayer_dp).table() |
34 |
inlayer_column = Input_column |
35 |
|
36 |
# ----- check input layers -----
|
37 |
# ...
|
38 |
|
39 |
# ----- connect to database -----
|
40 |
conn = psycopg2.connect( "dbname={0} host={1} user={2} password={3}".format( db_name, db_host, db_user, db_password ) )
|
41 |
|
42 |
# create and run query
|
43 |
with conn.cursor() as curs: |
44 |
sql = """CREATE SEQUENCE "{0}"."{1}_{2}_seq" OWNED BY "{0}"."{1}"."{2}";
|
45 |
SELECT SETVAL('"{0}"."{1}_{2}_seq"', (SELECT MAX("{2}") FROM "{0}"."{1}"));
|
46 |
ALTER TABLE "{0}"."{1}"
|
47 |
ALTER COLUMN "{2}" SET DEFAULT nextval('"{0}"."{1}_{2}_seq"'::regclass);
|
48 |
""".format ( inlayer_schema, inlayer_table, inlayer_column )
|
49 |
|
50 |
curs.execute(sql) |
51 |
|
52 |
# commit changes if everything went OK
|
53 |
conn.commit() |