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

Answer 1

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())])