Connecting PyQGIS plugin with PostgreSQL

Question

I created a plugin in QGIS.

Then I created a Qt GUI for my plugin and finally created a PostgreSQL database.

Now I need to make a connection between Qt-GUI and PostgreSQL database.

I wrote this:

 from PyQt4.QtSql import *

 db = QSqlDatabase.addDatabase("QPSQL")
 db.setHostName("localhost")
 db.setDatabaseName("siti")
 db.setUserName("postgres")
 db.setPassword("bina")
 bool ok = db.open()

but it doesn't work.

Can you help me?

Answer 1

Here is my code that I use in my QGIS plugin:

from PyQt4.QtSql import *

db = QSqlDatabase('QPSQL')
if db.isValid():
    # string
    db.setHostName('your_host')
    # string
    db.setDatabaseName('your_database_name')
    # string
    db.setUserName('your_username')
    # string
    db.setPassword('your_password')
    # integer e.g. 5432
    db.setPort('your_host')
    if db.open():
        # assume you have a table called 'users'
        query = db.exec_("""select * from users""")
        # iterate over the rows
        while query.next(): 
            record = query.record()
            # print the value of the first column
            print record.value(0) 

Answer 2

I had also problems with QSqlDatabase, so I used the psycopg2 library instead:

http://wiki.postgresql.org/wiki/Psycopg2_Tutorial

import psycopg2

conn = psycopg2.connect("dbname='postgis'host='localhost' user='postgis'")
cur = conn.cursor()
sql = "SELECT ST_AsGeoJSON(geomfield) from testtable;"
cur.execute(sql)
result = cur.fetchone()
print result