PyQt application with sqlalchemy database

Question

I tried to create a PyQt application and the data is stored in a database. I want to use sqlachemy to access my data and display data using a model in PyQt.

In this example, I used combobox to display my data.

My code "works" poorly because the model checks the database thousands of times every time I click on my combo box, making it very lagged.

What is the canonical method?

I am using Python 3.4 and PyQt 4 on Windows. My skills in Python-Sqlalchemy-PyQt-english are pretty 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 made some improvements to your code, but I'm not familiar with PyQt, so I'm sure this can be improved even further. Anyway, one reason the database is often called is because UserListModel.data() is called whenever PyQt wants to redraw the list. This happens, for example, on mouse movements inside the combobox, so we really don't want to hit the database for every data() call.

Filling the list of users in __init__() , we cache the database result and avoid frequent queries. It only takes you so far, because you will also have to update this cache when the contents of the database change. Therefore, 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_())