我试图创建一个PyQt应用程序,并将数据存储到数据库中.我想使用sqlachemy来访问我的数据并使用PyQt中的模型显示数据.
在这个例子中,我用一个组合框来显示我的数据.
我的代码“工作”不好,因为每次点击我的组合框时,模型都会检查数据库数千次,这使得它非常迟钝.
这样做的“规范方法”是什么？
我在Windows下使用Python 3.4和PyQt 4.我在Python-sqlalchemy-PyQt-english中的技巧非常低.
#!/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_())
解决方法
我对你的代码做了一些改进,但我不熟悉PyQt,所以我相信这可以进一步改进.无论如何,经常调用数据库的一个原因是,只要PyQt想要重绘列表,就会调用UserListModel.data().这发生在例如关于组合框内的鼠标移动,所以我们真的不希望每次调用data()时都会访问数据库.
通过填充__init __()中的用户列表,我们缓存数据库结果并避免频繁查询.这只会带你到目前为止,因为在修改数据库的内容时你还必须更新这个缓存.因此,当您添加或删除用户时,还需要调用UserListModel.refresh().
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sys
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from PyQt4 import QtCore,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,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,role):
if role == QtCore.Qt.displayRole:
value = self.users[index.row()]
return "%s : %s" % (value.id_,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_())