The PyQGIS Programmer's Guide

Extending Quantum GIS with Python

Creating a PostgreSQL Connection From a QGIS Layer Datasource

Sometimes you may want to send SQL to your PostgreSQL database from a plugin or PyQGIS script. To do this, you need a connection to your database. Since QGIS uses its own low-level interface to PostgreSQL, it can’t provide a connection for general query use. You can, however, use the information from the QGIS PostgreSQL data provider to create your own connection using QSqlDatabase, a Qt class.

You could use QSettings to read the stored connection information from your settings, but this may not always work if the password isn’t stored with the connection.

Here is an example that uses the active layer in QGIS to get the connection parameters, create a connection, and execute a query that returns some data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

"""Use the database provider to connect to PostgreSQL.
This script is set up to run under the Script Runner plugin, but
can be also be run from the Python console.
"""

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

from qgis.core import *


def run_script(iface):
    # get the active layer
    layer = iface.activeLayer()
    # get the underlying data provider
    provider = layer.dataProvider()
    if provider.name() == 'postgres':
        # get the URI containing the connection parameters
        uri = QgsDataSourceURI(provider.dataSourceUri())
        print uri.uri()
        # create a PostgreSQL connection using QSqlDatabase
        db = QSqlDatabase.addDatabase('QPSQL')
        # check to see if it is valid
        if db.isValid():
            print "QPSQL db is valid"
            # set the parameters needed for the connection
            db.setHostName(uri.host())
            db.setDatabaseName(uri.database())
            db.setPort(int(uri.port()))
            db.setUserName(uri.username())
            db.setPassword(uri.password())
            # open (create) the connection
            if db.open():
                print "Opened %s" % uri.uri()
                # execute a simple query 
                query = db.exec_("""select * from qgis_sample.airports
                    order by name""")
                # loop through the result set and print the name
                while query.next():
                    record = query.record()
                    print record.field('name').value().toString()
            else:
                err = db.lastError()
                print err.driverText()

Another approach is to use the psycopg2 Python module to create a connection using the data source URI of the active layer.

Comments