4

I want to create some layers queries in my QGIS plugin using Python. I have find here a question and I try to follow with success (works fine for a shapefile).

from osgeo import ogr


filepath = 'C:/Users/username/Desktop/path/clip.shp' # set the filepath
layer_name = filepath[:-4].split('/')[-1] # get the layer name
driver = ogr.GetDriverByName ("ESRI Shapefile")
ogr_ds = driver.Open(filepath)
TEST=2
sql = "SELECT myfield FROM %s WHERE OBJECTID=%s" %(layer_name,TEST)
layer = ogr_ds.ExecuteSQL(sql)
feat = layer.GetNextFeature()
val = feat.GetField(0)

print (val.decode('utf-8'))

but that question is for shapefile what I need more if that is layer in PostGIS database.

I know only the connection like this:

import psycopg2


try:
    conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
except:
    print "I am unable to connect to the database"
3

If conn is your databse connection from your code above, you fire your sql-statement as follows:

c = conn.cursor()
c.execute('SELECT * FROM mytesttable')
c.fetchall()

Where fetchall() returns a list of tuples containing the column values from your SELECT (implies that you know their order when SELECTing *).

Note that psycopg2 as a database client does not auto-commit database changes when using statements like INSERT or UPDATE. In latter cases you have to implicitly end your statement with

c.execute('COMMIT;')
  • thnx you but I have some question how to take current postgis connection to do my query?for example in my QGIS project I have save postgis connection every time,how to get it to avoid connect again with my python plugin ? – Chris Papas May 10 '17 at 20:37
5

Adding PostGIS table to a QGIS map

In a QGIS plugin I would use QgsDataSourceURI from the pyqgis API. Here is some snippet form a project I worked on.

Database connection is wrapped in a function. All you need is the db_params. The password is only base64 encoded; Since the pw is not a big secret and the users where I work are not hackers.

def get_dbparams(self):
    # Get db connections params #
    DB = 'somedb'
    PORT = 5432
    HOST = '11.22.333.44'

    user = self.get_userrole()

    if user == GrukosUser.READER:
        db_params = {
            'dbname': DB,
            'user': 'grukosreader',
            'host': HOST,
            'password': base64.b64decode('SDFADSASDFADF'),
            'port': PORT
        }
        return db_params
    elif user == GrukosUser.WRITER:
        db_params = {
            'dbname': DB,
            'user': 'grukoswriter',
            'host': HOST,
            'password': base64.b64decode('SDFGSGSDFGSDFGSDF'),
            'port': PORT
        }
        return db_params
    else:
        return None

Get the parameters:

    db = MyDb()
    params = db.get_dbparams()

Set the datasource:

uri = QgsDataSourceURI()
uri.setConnection(params['host'], str(params['port']), params['dbname'], params['user'], params['password'])

If your using a filter on table with no geometry:

 uri.setDataSource("myschema", "mytablename", None, "mycolumn={}".format(myvalue))

If you using a filer on a table with a geometry:

uri.setDataSource("myschema", "mytablename", "mygeom", "mycolumn={}".format(myvalue))

If no filter and no geometry:

uri.setDataSource("myschema", "mytablename", None)

If no filter and geometry:

uri.setDataSource("myschema", "mytablename", "geom")

If using a spatial view. Must have unique column. Here called id:

uri.setDataSource("myschema", "mytablename", "geom", aKeyColumn='id')
uri.setKeyColumn('id')

Then:

vlayer = QgsVectorLayer(uri.uri(), "mylayername", "postgres")

Check validity:

if not vlayer.isValid():
   ...

Add to map:

QgsMapLayerRegistry.instance().addMapLayer(vlayer, True)

No map just need to get some data from Postgres

I use psycopg2 like:

def get_projectid(self, gkoid):
    """ Use a PostgreSQL function to translate gkoid to projectid """
    sql = 'SELECT myschema.mypgfunction({});'.format(gkoid)
    cur = self.execute_sql(sql)
    rows_tuple = cur.fetchone()
    projectid = rows_tuple[0]

    return projekcid, sql

def execute_sql(self, sql, dict_cursor=True, print_sql=False):

    """ Execute a SQL query
    :param sql: SQL to be executed
    :param dict_cursor: Flag indicating if cursor is a dict or not. Use false for scalar queries
    :param print_sql: Flag indicating if sql is to be printet
    :return: returns a cursor
    """

    if print_sql: print sql
    conn = psycopg2.connect(**self.get_dbparams())

    if dict_cursor:
        cur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
    else:
        cur = conn.cursor()

    try:
        cur.execute(sql)
        return cur
    except psycopg2.DatabaseError, e:
        print 'Some error {}'.format(e)
        sys.exit(1)
    finally:
        pass
        #TODO
        #if conn:
        #    conn.close()

Logging

In some case error in SQL will crash QGIS at once. A way of debugging is connecting to the QgsMessageLog. I wrap it in a function like this:

def enable_qgis_log(filename = 'D:\gqis.log', haltApp = False, haltMsg = 'stop'):
    """ Very useful when QGIS crashes on PGSQL error
    :param filename: Filename and path for log file
    :param haltApp: Halts the application with a modal dialog
    :param haltMsg: Message to user when showing model stopping dialog
    :rtype: None
    """
    def write_log_message(message, tag, level):
        with open(filename, 'a') as logfile:
            logfile.write('{tag}({level}): {message}'.format(tag=tag, level=level, message=message))

    QgsMessageLog.instance().messageReceived.connect(write_log_message)

    if haltApp:
        QtGui.QMessageBox.information(None, GrukosAux.GRUKOS, "{}".format(haltMsg.encode('cp1252')), QtGui.QMessageBox.Ok) 

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.