PyQt application with sqlalchemy database

Question

I tried to create a PyQt application, and the data is stored into a database. I want to use sqlachemy to access my data and show the data with a model in PyQt.

In this example, I used a combobox to show my data.

My code "works" bad, because the model checks the database thousands of times everytime I click on my combobox, making it very laggy.

What is the "canonical method" to do this?

I'm using Python 3.4 and PyQt 4 under Windows. My skill in Python-Sqlalchemy-PyQt-english is quite low.

#!/usr/bin/env python3
# -*- coding: utf-8 -*- 

import sys
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

from PyQt4 import QtCore, QtGui, uic

# My base structure

base = declarative_base()

class User(base):
    __tablename__ = "users"
    id_ = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)

    name = sqlalchemy.Column(sqlalchemy.String(20))

    def __repr__(self):
        return "<User(id='%s', name='%s')>" % (self.id_, self.name)

# Creating my base and my session
engine = sqlalchemy.create_engine("sqlite:///my_db.db")
base.metadata.create_all(engine)

DBsession = sqlalchemy.orm.sessionmaker(bind=engine)
session = DBsession()

# My model, i just want to show data
class UserListModel(QtCore.QAbstractListModel):

    def __init__(self, session, parent = None):
        QtCore.QAbstractListModel.__init__(self, parent)
        self.session = session

    def rowCount(self, parent):
        return len(get_users(self.session))

    def data(self, index, role):
        users = get_users(self.session)

        # Only for debug
        print(users)

        if role == QtCore.Qt.DisplayRole:
            value = users[index.row()]
            return "%s : %s" % (value.id_, value.name)

def add_user(session, tmp_name):
    session.add(User(name=tmp_name))
    session.commit()

def get_users(session):
    users = session.query(User).all()
    return users

# Populating my db
session.add(User(name="edouard"))
session.add(User(name="jean"))
session.add(User(name="albert"))
session.commit()

# Creating my app
app = QtGui.QApplication(sys.argv)
mywindow = QtGui.QWidget()

# Combobox and his model
combobox = QtGui.QComboBox(mywindow)
combobox.setModel(UserListModel(session))

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

Answer 1

I've made some improvements to your code, but I'm not familiar with PyQt, so I'm sure this could be improved even more. Anyway, one reason the database is called often, is that UserListModel.data() is called whenever PyQt wants to redraw the list. This happens e.g. on mouse movements inside the combobox, so we really don't want to hit the database for every call to data().

By populating the list of users in __init__(), we cache the database result and avoid the frequent queries. This will only take you so far, because you will also have to update this cache when modifying the contents of the database. So when you add or remove a user, you also need to call UserListModel.refresh().

#!/usr/bin/env python3
# -*- coding: utf-8 -*- 

import sys
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

from PyQt4 import QtCore, QtGui, uic

# My base structure

base = declarative_base()

class User(base):
    __tablename__ = "users"
    id_ = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)

    name = sqlalchemy.Column(sqlalchemy.String(20))

    def __repr__(self):
        return "<User(id='%s', name='%s')>" % (self.id_, self.name)

# Creating my base and my session
engine = sqlalchemy.create_engine("sqlite:///my_db.db", echo='debug')
base.metadata.create_all(engine)

DBsession = sqlalchemy.orm.sessionmaker(bind=engine)
session = DBsession()

# My model, i just want to show data
class UserListModel(QtCore.QAbstractListModel):

    def __init__(self, session, parent = None):
        QtCore.QAbstractListModel.__init__(self, parent)
        self.session = session
        self.refresh()

    def refresh(self):
        self.users = self.session.query(User).all()

    def rowCount(self, parent):
        return len(self.users)

    def data(self, index, role):
        if role == QtCore.Qt.DisplayRole:
            value = self.users[index.row()]
            return "%s : %s" % (value.id_, value.name)

def add_user(session, tmp_name):
    session.add(User(name=tmp_name))
    session.commit()

# Populating my db
if not session.query(User).count():
    session.add(User(name="edouard"))
    session.add(User(name="jean"))
    session.add(User(name="albert"))
    session.commit()

# Creating my app
app = QtGui.QApplication(sys.argv)
mywindow = QtGui.QWidget()

# Combobox and his model
combobox = QtGui.QComboBox(mywindow)
combobox.setModel(UserListModel(session))

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