QtSql in PyQt4 - handling databases

Check out the new site at https://rkblog.dev.

12 December 2008 Comments

In Qt/PyQt4 we have access to QtSql classes, that offer database handling classes (SQL execution, grids and more). However on Windows default PyQt4 package supports only SQLite and ODBC (PyQt4/Qt4 recompilation would be required to support other databases). If you need only SQL support - you can use standard Python libraries for your database.

Here is a basic QtSql usage - connecting and executing SQL query:

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

app = QApplication(sys.argv)
w = QTextBrowser()

# DB type, host, user, password...
db = QSqlDatabase.addDatabase("QMYSQL");
db.setHostName("localhost")
db.setDatabaseName("test")
db.setUserName("root")
db.setPassword("")
ok = db.open()

# True if connected
if ok:
	w.insertHtml('Connected to MySQL<br />')
else:
	w.insertHtml('ERROR connecting to MySQL<br />')

# do a query "on" a DB connection
query = QSqlQuery(db)
if query.exec_("SHOW TABLES"):
	w.insertHtml('<br />')
	while query.next():
		table = query.value(0).toString()
		w.insertHtml('%s<br />' % table)
	
	w.insertHtml('<br />')
	w.insertHtml('TOTAL %s TABLES' % query.size())

w.show()
sys.exit(app.exec_())

At start we select and connect to a database with QSqlDatabase class. To execute a SQL query we use QSqlQuery class, that takes db connection (QSqlDatabase object) as an argument. The exec_ method will execute the query returning True if it succeeds. For queries that return results QSqlQuery will get all rows with pointer set on the first row. To get all rows we use query.next(). "next" method will set the pointer to the next row, or return False if it doesn't exist. To get values from current row we use: query.value(0).toString() - all elements selected in a row are numerated from 0, so first field is "0", next is "1" and so on. More info in the QtSql documentation page.

RkBlog

PyQt and GUI, 12 December 2008

Check out the new site at https://rkblog.dev.