In the first step of this tutorial we’re going to talk about QtSql. The QtSql module helps you provide seamless database integration to your Qt applications. There’s a lot of good documentation on the PySide website about the module QtSql but it’s lacking examples. So I’m going to try here to give you some code sample to get you started with Python and QtSql.
We’re going to use MySQL for this example, but Qt has support for most SQL databases. To be able to open a MySql connection with QtSql I had to install a separate library: libqt4-sql-mysql. On Ubuntu 12.04 it was in the repository, so I installed it using the Ubuntu Software Center.
If you get lost somewhere in this tutorial and don’t understand what’s going on, please check PySide QSqlQuery page or post a comment and I’ll be happy to give more details.
Great, let’s get coding!
I’m going to post pastebin links for the code because it’s difficult to indent text here.
So, we first import QtSql. We set the parameters for the connection and we open it, do our work and at the end close it.
We write a method which creates the a new table in the database. We instantiate an QtSql.QSqlQuery object and we use it to execute some SQL queries.
Here is a method that executes a SELECT query. The query returns a list of results from the database. Each result represents a row from the database. Think of QtSqlQuery as a cursor, with is pointing at some row in the database. You can retrieve the values from the columns of that row by calling the ‘value()’ method and passing it an integer parameter representing the column’s number.
You can navigate through the results using the methods: ‘first()’, ‘last()’, ‘next()’, ‘previous()’ and ‘seek()’. The method ‘next()’ retrieves the next record in the result, if available, and positions the query on the retrieved record.
In the example we iterate through all the results and print the id, first name, and last name for each row in the database.
Here we create methods for INSERT, UPDATE and DELETE. We also use transactions. We pass a database object as an argument to the method, and we use begin the transaction by trying to do ‘db.commit()’ after we execute our query. If the commit fails, we do a rollback on the database.
The ‘insert’ method also returns the id of the row we just inserted.
http://pastebin.com/eJAvUv7L An example of calling a stored procedure.
Here’s the code for the whole example: http://pastebin.com/dqeQFp7p
Don’t expect to paste it in the interpreter and just work. It’s meant to be an example, and give you some code samples to help you get started with QtSql.