Turn database column into Python list using PySide?

Question

Using PySide/Qt's QSqlTableModel is there a quick, elegant way to turn a database column into a Python list? In R, for example, this would be one, short line of code. Right now, I'm manually looping over rows in Python, which seems cumbersome for a high level language:

def get_value_idx(value):
    model = QSqlTableModel()
    model.setTable("pvalues")
    model.setFilter("val = '%s'" % (value))
    model.select()
    count = model.rowCount()
    if count >= 1:
        l = list()
        for n in range(count):
            id = model.record(n).value('id')
            l.append(id)
        return l  # return id list
    if count == 0:
        return -1 # return id that shows error

ekhumoro · Accepted Answer · 2017-10-04T18:30:43.597

There is no need to create a model just to get a set of values. It is much simpler and more efficient to use a query to get the values. This won't give you a one-liner - but one of the main strengths of Python is its readability, not its brevity.

The example below could easily be adapted to create a generic function that took a query-string and returned a list (or iterator) of values:

from PySide.QtSql import *

db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName(':memory:')
db.open()
db.transaction()
db.exec_('CREATE TABLE colors (id INTEGER PRIMARY KEY, color TEXT NOT NULL)')
db.exec_("INSERT INTO colors VALUES(1, 'Red')")
db.exec_("INSERT INTO colors VALUES(2, 'Blue')")
db.exec_("INSERT INTO colors VALUES(3, 'Green')")
db.exec_("INSERT INTO colors VALUES(4, 'Yellow')")
db.commit()

def list_colors():
    colors = []
    query = QSqlQuery('SELECT color FROM colors')
    while query.next():
        colors.append(query.value(0))
    return colors

print(list_colors())    

# or use a generator function:

def generate_colors():
    query = QSqlQuery('SELECT color FROM colors')
    while query.next():
        yield query.value(0)

print(list(generate_colors()))

EDIT:

Here is a generic fetchall function (akin to cursor.fetchall in the python's sqlite3 module). My implementation of this takes either a query string or an active QSqlQuery object, and returns either a list of values (for one column) or a tuple of values (for multiple columns):

def fetchall(query):
    if isinstance(query, str):
        query = QSqlQuery(query)
    result = []
    count = query.record().count()
    indexes = range(count)
    while query.next():
        if count == 1:
            result.append(query.value(0))
        else:
            result.append(tuple(query.value(i) for i in indexes))
    return result

# one liner ...

print(fetchall('SELECT color FROM colors'))

This could also be implemented as a generator, which would be more suitable for very large result sets.

EDIT2:

If you use a model for querying then, once the rows have been selected, you can use a list comprehension to pull out the column values:

model = QSqlTableModel()
model.setTable('colors')
model.select()

# one liner ...

print([model.index(i, 1).data() for i in range(model.rowCount())])

@davideps. No. But it's not as light-weight as a query, so it seems inefficient to create one every time you call a function. If I was going to use a model for querying, I would subclass `QSqlTableModel` and add some methods (like the generic `fetchall` I added to my answer). — ekhumoro, Oct 04 '17 at 18:14
@davideps. I've added an example which uses a model for querying, and a list comprehension to pull out the column values. There are probably dozens of ways to skin this particular cat ... — ekhumoro, Oct 04 '17 at 18:34
You need to add: while model.canFetchMore(): model.fetchMore() To get the full table. — kblst, Apr 17 '20 at 15:19

score 1 · Answer 2 · answered Oct 04 '17 at 15:59

The PySide layer is nearly 1 to 1 for all the Qt methods in C++. Some C++ code is given below.

Because the Qt Sql layer is abstracted for the backend database type, and geared towards Gui interfaces on an event loop, it doesn't have the same one liners available that R or other languages may have. Though you can do it in a few lines.

Also Qt's error handling methods for SQL is usually through querying the last error or looking at the return of the exec or the open call. Tuples aren't native in C++, so the python interface doesn't heavily use tuples.

http://doc.qt.io/qt-4.8/sql-sqlstatements.html

http://doc.qt.io/qt-4.8/qsqltablemodel.html#details

 QSqlTableModel model;
 model.setTable("employee");
 model.setFilter("salary > 50000");
 model.setSort(2, Qt::DescendingOrder);
 model.select();

 for (int i = 0; i < model.rowCount(); ++i) {
     QString name = model.record(i).value("name").toString();
     int salary = model.record(i).value("salary").toInt();
     qDebug() << name << salary;
 }

Alternate syntax specifying the query

QSqlQueryModel model;
model.setQuery("SELECT * FROM employee");
int salary = model.record(4).value("salary").toInt();

http://doc.qt.io/qt-4.8/qsqlresult.html#size

QSqlQuery query("SELECT country FROM artist");
while (query.next()) {
    QString country = query.value(0).toString();
    doSomething(country);
}

The real power of Qt's Sql interface is in how easy it is to make a GUI for representing a database in almost any sort of configuration you can think of, and how it is abstracted from the database engine.

Even with all that said about how Qt handles it's SQL calls... it still is on par with other Python libraries for interacting with databases:

How to retrieve SQL result column value using column name in Python?

cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT name, category FROM animal")
result_set = cursor.fetchall()
for row in result_set:
    print "%s, %s" % (row["name"], row["category"])

Hope that helps.

The example using query.next() is helpful. Thank you. – davideps Oct 04 '17 at 18:07 — davideps, Oct 04 '17 at 18:07

Turn database column into Python list using PySide?

2 Answers2