7

I'm writing a python script using QGIS API. I have to get results from postgres database table. The table has geometry field and other non-geometry fields.

I want to perform SQL queries on non-geometry fields and get results in my code. How to do this? Is there any class to do so?

I know about QgsDataSourceURI class but as far as I know it only gets results from geometry fields.

  • What is dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) ? >>> import psycopg2 >>> psycopg2.extras gives me a Traceback Traceback (most recent call last): File "<input>", line 1, in <module> AttributeError: 'module' object has no attribute 'extras' – Jochen Schwarze Jan 25 '16 at 16:33
11

1) With PyQt4.QtSql: Gary Sherman shows how to query a spatial table in Creating a PostgreSQL Connection From a QGIS Layer Datasource:

from PyQt4.QtSql import *
layer = iface.activeLayer()
uri = QgsDataSourceURI()
uri.setConnection("localhost", "5432", "testpostgis", "me", "")
uri.setDataSource("public", "teststrati", "the_geom")
# add the layer to the canvas
vlayer = QgsVectorLayer(uri.uri(), "tot", "postgres")
# now query the table
db = QSqlDatabase.addDatabase("QPSQL");
db.setDatabaseName(uri.database())
db.setPort(int(uri.port()))
db.setUserName(uri.username())
db.setPassword(uri.password())
db.open()
# query the table
query = db.exec_("""select * from teststrati""")
query.next()
query.value(0)
130
# etc read the documentation of QtSQL

And you can open all the other tables/views (spatial or non spatial) similarly:

db = QSqlDatabase.addDatabase("QPSQL")
db.setHostName("localhost")
db.setPort(5432)
# non spatial table or view
db.setDatabaseName("people")
db.setUserName("me")
db.setPassword("")
query = QSqlQuery(db)
query.exec_("select * from people;")
# etc.

2) Or you can use the standard Python module for PostgreSQL/PostGIS: Psycopg2:

import psycopg2
conn = psycopg2.connect("dbname='testpostgis'host='localhost' user='me'")
cur = conn.cursor()
sql = """SELECT "DIP_DIR","DIP", ST_AsGeoJSON(the_geom) from teststrati;"""
cur.execute(sql)
result = cur.fetchone()
print result
(130, 30, u'{"type":"Point","coordinates":[272070.600040999997873,155389.387920000008307]}')

With a non spatial table or view and the result as a dictionary:

conn = psycopg2.connect("dbname='testpostgis'host='localhost' user='me'")  
dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
dict_cur.execute("""SELECT * from people;""")
rec = dict_cur.fetchone()
rec.keys()
['name', 'id']
rec.values()
('Jon Doe',1)
# etc read the documentation of the module
1

It depends on what DB you are using. If it is Postgresql, you have to install and import psycopg2 the PostgreSQL + Python library

to install use:

pip install psycopg2

or:

easy_install install psycopg2

Follow this tutorial, or else, for the basics

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.