SQL queries in PostGIS database layer using Python and QGIS

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) 

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;')