class DatabaseQueryView(QTableView):
def __init__(self, db, query, parent=None):
super().__init__(parent)
self.db = db
self.query = query
self._init_context_menu()
self._init_model()
def _init_context_menu(self):
self.setContextMenuPolicy(Qt.CustomContextMenu)
self.customContextMenuRequested.connect(self._on_context_menu)
def _init_model(self):
self.model = QSqlQueryModel()
self.model.setQuery(QSqlQuery(self.query, self.db))
self.setModel(self.model)
def set_query(self, query):
self.query = query
self._init_model()
def set_header_labels(self, labels):
for idx, label in enumerate(labels):
self.model.setHeaderData(idx + 1, Qt.Horizontal, label)
def _on_context_menu(self, pos):
_context_menu = QMenu(self)
_refresh_action = QAction("刷新")
_context_menu.addAction(_refresh_action)
action = _context_menu.exec_(self.viewport().mapToGlobal(pos))
if action == _refresh_action:
self.set_query(self.query)
def attractionView(self):
model = QSqlQueryModel()
model.setQuery('SELECT id, name FROM t_attraction')
model.setHeaderData(0, Qt.Horizontal, "ID")
model.setHeaderData(1, Qt.Horizontal, "Name")
self.tableView_Attraction_List.setModel(model)
pass
def _createModel():
oi_get_query = QSqlQuery()
oi_get_query.prepare("""select symbol
,desc
,substr(oi_date, 1,4) || '-' || substr(oi_date, 5,2) || '-' || substr(oi_date,7,2)
,globex_volume
,volume
,open_interest
,change
from oi_reports
where symbol = "EUR"
order by cast(oi_date as int) desc
;
""")
#oi_get_query.addBindValue(symbol)
oi_get_query.exec()
queryModel = QSqlQueryModel()
queryModel.setQuery(oi_get_query)
headers = ['Symbol', 'Description', 'Date', 'Globex', 'Volume', 'Open Interest', 'Change']
for columnIndex, header in enumerate(headers):
queryModel.setHeaderData(columnIndex, Qt.Horizontal, header)
#queryModel.setHeaderData(0, Qt.Horizontal, 'Symbol')
#queryModel.setHeaderData(1, Qt.Horizontal, 'Description')
#queryModel.setHeaderData(2, Qt.Horizontal, 'Date')
#queryModel.setHeaderData(3, Qt.Horizontal, 'Globex')
#queryModel.setHeaderData(4, Qt.Horizontal, 'Volume')
#queryModel.setHeaderData(5, Qt.Horizontal, 'Open Interest')
#queryModel.setHeaderData(6, Qt.Horizontal, 'Change')
return queryModel
def competitorView(self):
model = QSqlQueryModel()
model.setQuery('SELECT id, name FROM t_competitor')
model.setHeaderData(0, Qt.Horizontal, "ID")
model.setHeaderData(1, Qt.Horizontal, "Name")
self.tableView_Competitor_List.setModel(model)
pass
def btn3f(self):
conn = sqlite3.connect('company.db')
curs = conn.cursor()
curs.execute("select name from sqlite_master where type='table'")
tabs = QTabWidget()
table_name = 'stock_price'
table_model = QSqlQueryModel()
for i, column_data in enumerate(curs.description):
table_model.setHeaderData(i, Qt.Horizontal, column_data[0])
table_model.setQuery(f"select * from {table_name}")
table_view = QTableView()
table_view.setModel(table_model)
tabs.addTab(table_view, table_name)
screen = QWidget()
content = QVBoxLayout()
line1 = QHBoxLayout()
line1.addWidget(tabs)
content.addLayout(line1)
self.k = popup3()
self.k.setGeometry(800, 350, 400, 200)
self.k.setWindowTitle("Stock Prices")
self.k.setLayout(content)
self.k.show()
class TabTableNonEditable(InvertedTable):
"""an inverted table presenting a QSqlQueryModel
"""
def __init__(self, log, db, tab_nr, query, headers = None, myfilter = ""):
super().__init__(log, db)
self.nr = tab_nr
self.filter = myfilter
self.headers = headers
self.query = query
self.create_model()
self.invert_model()
self.add_headers()
def create_model(self):
"""creates the table model
"""
q = QSqlQuery(self.query + " " + self.filter)
self.model = QSqlQueryModel()
q.exec_(self.query)
self.model.setQuery(q)
def add_headers(self):
"""adds headers
"""
if self.headers:
for i in self.headers:
column = self.headers[i]
self.model.setHeaderData(i, Qt.Horizontal, column, Qt.DisplayRole)
def refresh(self, myfilter = ""):
"""refreshes the displayed data after data changes in the model
"""
if myfilter:
self.filter = myfilter
self.model.setQuery(self.query + " where " + self.filter)
class GeneralResultModel():
def __init__(self, query, label):
self.model = QSqlQueryModel()
self.query = query
self.model.setQuery(self.query)
self.model.setHeaderData(0, Qt.Horizontal, label)
def select(self):
self.model.setQuery(self.query)
def songProperties(self):
model = QSqlQueryModel()
model.setQuery('SELECT t_artist.name FROM fk_song_artist '
'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id '
'WHERE fk_song_artist.id_song = ' +
str(self.tableView_Song_List.currentIndex().sibling(
self.tableView_Song_List.currentIndex().row(), 0).data()))
model.setHeaderData(0, Qt.Horizontal, "Artist Name")
self.tableView_Song_Properties.setModel(model)
pass
def getList(self, table, header, where='1=1', page=1, pagesize=100):
model = QSqlQueryModel()
# model.setTable(table)
model.setQuery(
'select * from %s where %s limit %s , %s' %
(table, where, (page - 1) * pagesize, pagesize), self.db)
i = 0
for h in header:
model.setHeaderData(i, Qt.Horizontal, h)
i += 1
return model
def get_model(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('sports.db')
self.db.open()
model = QSqlQueryModel()
query_str = """ select name, count(name) as counts from domains group by name order by counts desc
"""
model.setQuery(query_str, db=self.db)
model.setHeaderData(0, Qt.Horizontal, "Word")
model.setHeaderData(1, Qt.Horizontal, "Count")
return model
def get_model(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('sports.db')
self.db.open()
model = QSqlQueryModel()
query_str = """ select name, count(name) as counts from domains group by name order by counts desc
"""
model.setQuery(query_str, db=self.db)
model.setHeaderData(0, Qt.Horizontal, "Word")
model.setHeaderData(1, Qt.Horizontal, "Count")
return model
def asc(ui,sqlCommand):
q = QSqlQuery()
q.prepare(sqlCommand)
# q.prepare('insert into buff1 (id,name) value(1,"shit")')
print("执行一次asc():sql语句为:"+sqlCommand)
q.exec()
model = QSqlQueryModel()
model.setQuery(q)
#设置表头
modelDic = ['id','物品名','挂刀比例','倒货收益率','倒货收益','steam底价',
'buff底价','buff在售数','buff求购数','buff求购价','磨损','品类','物品种类','steam商城链接','上次更新时间']
for i in range(0,14):
model.setHeaderData(i, Qt.Orientation(1),modelDic[i])
ui.tableView.setModel(model)
def songView(self):
model = QSqlQueryModel()
model.setQuery('SELECT t_song.id, t_song.name, t_song.file, t_song_type.name, t_anime.name, t_anime_serie.name '
'FROM t_song '
'INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id '
'INNER JOIN t_anime ON t_song.id_anime = t_anime.id '
'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id')
model.setHeaderData(0, Qt.Horizontal, "Song ID")
model.setHeaderData(1, Qt.Horizontal, "Song Name")
model.setHeaderData(2, Qt.Horizontal, "Song File")
model.setHeaderData(3, Qt.Horizontal, "Song Type")
model.setHeaderData(4, Qt.Horizontal, "Anime Name")
model.setHeaderData(5, Qt.Horizontal, "Anime Serie")
self.tableView_Song_List.setModel(model)
pass
class HospitalData(QWidget):
def __init__(self, parent=None):
super(HospitalData, self).__init__(parent)
# Declare Database Connections
self.db = None
# Layout Manager
self.layout = QVBoxLayout()
# Query Model
self.queryModel = QSqlQueryModel()
# Table View
self.tableView = QTableView()
self.tableView.setModel(self.queryModel)
self.initUI()
self.initializedModel()
def initUI(self):
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
self.layout.addWidget(self.tableView)
self.setLayout(self.layout)
self.setWindowTitle("HospitalData")
self.resize(1300, 600)
def initializedModel(self):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("project.db")
if not self.db.open():
return False
self.queryModel.setHeaderData(0, Qt.Horizontal, "Hospital Name")
self.queryModel.setHeaderData(1, Qt.Horizontal, "Address")
self.queryModel.setHeaderData(3, Qt.Horizontal, "City")
self.queryModel.setHeaderData(3, Qt.Horizontal, "State")
self.queryModel.setHeaderData(3, Qt.Horizontal, "Contact Details")
# Get all the records of the table
sql = "SELECT * FROM hospital"
self.queryModel.setQuery(sql, self.db)
self.totalRecordCount = self.queryModel.rowCount()
def closeEvent(self, event):
self.db.close()
def reloadGameCompetitors(self, id_attraction):
model_Game_Competitors = QSqlQueryModel()
model_Game_Competitors.setQuery('SELECT SUM(point), name FROM (SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor ON '
't_point.id_competitor = t_competitor.id '
'WHERE t_point.id_competitor_take = 1 '
'AND t_point.id_attraction = ' + id_attraction + ' UNION ALL '
'SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor '
'ON t_point.id_competitor_take = t_competitor.id '
'WHERE t_point.id_competitor_take != 1 '
'AND t_point.id_attraction = ' + id_attraction + ') '
'GROUP BY name ORDER BY point DESC')
model_Game_Competitors.setHeaderData(0, Qt.Horizontal, "Points")
model_Game_Competitors.setHeaderData(1, Qt.Horizontal, "Name")
self.tableView_Game_Competitors.setModel(model_Game_Competitors)
pass
def selectComponentButtonEvent(self):
"""
hsj 搜索产品的相关组件
:return:
"""
# 判断复选框是否只选中一个
a = self.isCorrect()
if a == 0:
return
select_num = self.queryModel.selectNum()
db = openDB()
queryModel = QSqlQueryModel()
query = QSqlQuery()
sql = "SELECT * FROM T_ProductComponent_New WHERE ProductID = '%s'" % (
self.queryModel.data_list[select_num][0])
# print(sql)
query.exec(sql)
if not query.next():
QMessageBox.information(QDialog(), "提示", "该产品无其他组件！",
QMessageBox.Yes, QMessageBox.Yes)
return
queryModel.setQuery(
"SELECT * FROM T_ProductComponent_New WHERE ProductID = '%s'" %
(self.queryModel.data_list[select_num][0]))
headerRow = [
"组件ID", "产品ID", "组件代号", "组件名称", "父节点ID", "组件类型", "组件数量", "寿命类型",
"寿命", "据到期提醒", "最初维保时间", "维保间隔", "距维保提醒"
]
for i in range(len(headerRow)):
queryModel.setHeaderData(i, Qt.Horizontal, headerRow[i])
form = QDialog()
tableView = QTableView()
tableView.setModel(queryModel)
tableView.show()
tableView.horizontalHeader().setStretchLastSection(True)
layout = QVBoxLayout()
layout.addWidget(tableView)
form.setLayout(layout)
form.showMaximized()
form.exec()
class Ui_MainWindow(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(800, 600)
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.verticalLayout = QtWidgets.QVBoxLayout(self.centralwidget)
self.verticalLayout.setObjectName("verticalLayout")
self.tv = tableView = QtWidgets.QTableView(self.centralwidget)
self.tv.setObjectName("tableView")
self.verticalLayout.addWidget(self.tv)
MainWindow.setCentralWidget(self.centralwidget)
self.model = None
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("test.db")
if db.open():
print("open DB success.")
query = QSqlQuery()
query.prepare("insert into user ('nom','prenom' )values ('Bauer', 'Jack')")
# query.exec_("create table person(id int primary key, name varchar(20), address varchar(30))")
query.exec()
# query.exec_("insert into user values('Jack', 'shanghai')")
# query.exec_("insert into user values('Alex', 'chengdu')")
# query.prepare("SELECT * FROM user")
# query.exec()
self.model = QSqlQueryModel()
self.model.setQuery("SELECT * FROM user LIMIT 10,10")
self.model.setHeaderData(0,QtCore.Qt.Horizontal,"Nom")
self.model.setHeaderData(1,QtCore.Qt.Horizontal,"Prénom")
self.model.setHeaderData(2,QtCore.Qt.Horizontal,"ID")
# self.tv.setHorizontalHeaderLabels(QString("Nom;Prénom;ID").split(";"))
# self.model.setHeaderData(1, Qt.Horizontal, "Prénom")
self.tv.setModel(self.model)
self.model.setQuery("SELECT * FROM user LIMIT 5")
# self.tv.setModel(self.model)
db.close()
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
def gameProperties(self):
model = QSqlQueryModel()
model.setQuery('SELECT t_song.name, t_song_type.name, t_anime.name, t_anime_serie.name, t_artist.name '
'FROM t_song INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id '
'INNER JOIN t_anime ON t_song.id_anime = t_anime.id '
'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id '
'INNER JOIN fk_song_artist ON t_song.id = fk_song_artist.id_song '
'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id '
'WHERE t_song.id = ' +
str(self.tableView_Game_List.currentIndex().sibling(
self.tableView_Game_List.currentIndex().row(), 3).data()))
model.setHeaderData(0, Qt.Horizontal, "Song Name")
model.setHeaderData(1, Qt.Horizontal, "Song Type")
model.setHeaderData(2, Qt.Horizontal, "Anime Name")
model.setHeaderData(3, Qt.Horizontal, "Anime Serie")
model.setHeaderData(4, Qt.Horizontal, "Artist Name")
self.tableView_Game_Properties.setModel(model)
pass
class MainWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.setGeometry(100, 100, 400, 400)
self.setWindowTitle("Database GUI App")
self.createDbConnection()
self.createEmployeeTable()
# self.addTestData()
self.createCentralWidget()
def createDbConnection(self):
self.con = QSqlDatabase.addDatabase("QSQLITE")
self.con.setDatabaseName("test2.sqlite")
if not self.con.open():
QMessageBox.critical(None, "Database Error!",
f"{con.lastError().databaseText()}")
sys.exit()
def checkIfTableExists(self, tableName):
tables = self.con.tables()
return tableName in tables
def createEmployeeTable(self):
if not self.checkIfTableExists("employee"):
queryString = """
CREATE TABLE employee (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
name VARCHAR(40) NOT NULL,
job VARCHAR(50),
email VARCHAR(40) NOT NULL,
phone VARCHAR(20),
age INTEGER
)
"""
query = QSqlQuery()
query.exec(queryString)
def addTestData(self):
data = [
("Harrison", "Senior Back-End Developer",
"*****@*****.**", "0799204524", 22),
("Joe", "Senior Front-End Developer", "*****@*****.**",
"0799001654", 21),
("Lara", "Project Manager", "*****@*****.**", "0710204524", 25),
("David", "Data Analyst", "*****@*****.**", "0799204500", 24),
("Jane", "Senior Python Developer", "*****@*****.**",
"0799200520", 23),
]
query = QSqlQuery()
query.prepare("""
INSERT INTO employee (
name,
job,
email,
phone,
age
)
VALUES (?, ?, ?, ?, ?)
""")
for name, job, email, phone, age in data:
query.addBindValue(name)
query.addBindValue(job)
query.addBindValue(email)
query.addBindValue(phone)
query.addBindValue(age)
query.exec()
query.finish()
def createCentralWidget(self):
self.model = QSqlQueryModel(self)
self.model.setQuery(
"SELECT id, name, job, email,phone,age FROM employee")
self.model.setHeaderData(0, Qt.Horizontal, "ID")
self.model.setHeaderData(1, Qt.Horizontal, "Name")
self.model.setHeaderData(2, Qt.Horizontal, "Job")
self.model.setHeaderData(3, Qt.Horizontal, "Email")
self.model.setHeaderData(4, Qt.Horizontal, "Phone")
self.model.setHeaderData(5, Qt.Horizontal, "Age")
self.view = QTableView()
self.view.setModel(self.model)
# self.view.setColumnCount(6)
# self.view.setHorizontalHeaderLabels(["ID", "Name", "Job", "Email","Phone","Age"])
# employeesData = self.fetchEmployees()
# while employeesData.next():
# rows = self.view.rowCount()
# self.view.setRowCount(rows+1)
# self.view.setItem(rows,0,QTableWidgetItem(str(employeesData.value(0))))
# self.view.setItem(rows,1,QTableWidgetItem(employeesData.value(1)))
# self.view.setItem(rows,2,QTableWidgetItem(employeesData.value(2)))
# self.view.setItem(rows,3,QTableWidgetItem(employeesData.value(3)))
# self.view.setItem(rows,4,QTableWidgetItem(employeesData.value(4)))
# self.view.setItem(rows,5,QTableWidgetItem(str(employeesData.value(5))))
self.view.resizeColumnsToContents()
self.setCentralWidget(self.view)
def fetchEmployees(self):
self.employeesQuery = QSqlQuery()
self.employeesQuery.exec(
"SELECT id, name, job, email,phone,age FROM employee")
return self.employeesQuery
class LogDialog(QDialog):
"""
日志弹窗类
"""
def __init__(self, parent=None):
super(LogDialog, self).__init__(parent)
self.setWindowTitle("打卡日志")
self.setWindowModality(Qt.ApplicationModal) # 隐藏父窗口
self.setFixedSize(600, 480)
self.table = None
self.button_export = None
self.model = None
self.file = None
self.load_data()
self.log_dialog()
def log_dialog(self):
"""
日志弹窗
:return:
"""
self.table = QTableView(self)
self.table.resize(600, 400)
self.table.setModel(self.model)
self.table.setEditTriggers(QTableView.NoEditTriggers) # 设置表单不可编辑
self.table.setSelectionMode(QTableView.NoSelection) # 设置表单不可选中
self.table.resizeColumnsToContents() # 列根据内容调整大小
self.table.resizeRowsToContents() # 行根据内容调整大小
self.table.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch) # 表单自适应
self.button_export = QPushButton("导出日志", self)
self.button_export.clicked.connect(self.export_xls)
self.button_export.move(220, 415)
def load_data(self):
"""
使用自带的QSqlQueryModel方法进行数据库查询
:return:
"""
db = QSqlDatabase.addDatabase("QSQLITE") # 选着数据库类型
db.setDatabaseName("./sys_db.db")
db.open()
self.model = QSqlQueryModel()
self.model.setQuery(
"""select tb1.id,tb1.sname,tb2.clcokdate,tb2.clocktime,tb2.latetime from
staff_tb as tb1 join logcat_tb as tb2 on tb1.id = tb2.id""")
self.model.setHeaderData(0, Qt.Horizontal, "ID")
self.model.setHeaderData(1, Qt.Horizontal, "姓名")
self.model.setHeaderData(2, Qt.Horizontal, "打卡日期")
self.model.setHeaderData(3, Qt.Horizontal, "打卡时间")
self.model.setHeaderData(4, Qt.Horizontal, "迟到时长")
def export_xls(self):
self.file = xlwt.Workbook(encoding="utf-8")
log = load_logcat()
sheet = self.file.add_sheet(u"日志")
row0 = [u"ID", u"姓名", u"打卡日期", u"打卡时间", u"迟到时长"]
for i in range(len(row0)):
sheet.write(0, i, row0[i])
for i in range(len(log)):
for j in range(len(log[i])):
print(log[i][j])
sheet.write(i + 1, j, log[i][j])
cu_time = time.strftime(u'%Y-%m-%d', time.localtime(time.time()))
self.file.save("./" + cu_time + "日志.xls")
class VentanaDetalleRegistro(QMainWindow):
def __init__(self, id, parent=None):
super(VentanaDetalleRegistro, self).__init__(parent)
loadUi("ventanas\DetalleVenta.ui", self)
self.id = id
delegateFloat = InitialDelegate(2, self.tableView)
self.tableView.setItemDelegateForColumn(4, delegateFloat)
self.tableView.setItemDelegateForColumn(5, delegateFloat)
self.modelo = QSqlQueryModel()
self.tableView.setModel(self.modelo)
self.tableView.setDragDropOverwriteMode(False)
self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tableView.setTextElideMode(Qt.ElideRight)
self.tableView.setWordWrap(False)
self.tableView.setSortingEnabled(False)
self.tableView.horizontalHeader().setDefaultAlignment(Qt.AlignHCenter
| Qt.AlignVCenter
| Qt.AlignCenter)
self.tableView.horizontalHeader().setHighlightSections(False)
self.tableView.verticalHeader().setSectionResizeMode(
QHeaderView.ResizeToContents)
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.verticalHeader().setVisible(False)
self.tableView.setAlternatingRowColors(True)
self.tableView.horizontalHeader().setFont(
QFont("Franklin Gothic Book", 9, QFont.Bold))
self.query = QSqlQuery(db=dba)
self.query.prepare(
"SELECT idConcepto, Refaccion.nombre, Concepto.cantidad, Refaccion.uniMedida, Refaccion.precio, importe FROM Concepto "
"INNER JOIN Refaccion ON Refaccion.idRefaccion = Concepto.refaccion "
"INNER JOIN Venta ON Venta.numVenta = Concepto.numVenta "
"WHERE Venta.numVenta = :numVenta")
self.actualizarQuery()
self.modelo.setHeaderData(0, Qt.Horizontal, "Id concepto")
self.modelo.setHeaderData(1, Qt.Horizontal, "Nombre")
self.modelo.setHeaderData(2, Qt.Horizontal, "Cantidad")
self.modelo.setHeaderData(3, Qt.Horizontal, "Unidad de\nmedida")
self.modelo.setHeaderData(4, Qt.Horizontal, "Precio unitario")
self.modelo.setHeaderData(5, Qt.Horizontal, "Importe")
def actualizarQuery(self):
numVenta = self.id
self.query.bindValue(":numVenta", numVenta)
self.query.exec_()
self.modelo.setQuery(self.query)
class DataGrid(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("分页查询例子")
self.resize(750,300)
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
# 总数页文本
self.totalPageLabel = None
# 当前页文本
self.currentPageLabel = None
# 转到页输入框
self.switchPageLineEdit = None
# 前一页按钮
self.prevButton = None
# 后一页按钮
self.nextButton = None
# 转到页按钮
self.switchPageButton = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 5
self.db = None
self.initUI()
def initUI(self):
# 创建窗口
self.createWindow()
# 设置表格
self.setTableView()
# 信号槽连接
self.prevButton.clicked.connect(self.onPrevButtonClick )
self.nextButton.clicked.connect(self.onNextButtonClick )
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick )
def closeEvent(self, event):
# 关闭数据库
self.db.close()
# 创建窗口
def createWindow(self):
# 操作布局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一页")
self.nextButton = QPushButton("后一页")
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setText(str(self.currentPage))
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("转到第")
page = QLabel("页")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget( QSplitter())
# 状态布局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget( QSplitter() )
statusLayout.addWidget(self.totalRecordLabel)
# 设置表格属性
self.tableView = QTableView()
# 表格宽度的自适应调整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 创建界面
mainLayout = QVBoxLayout(self);
mainLayout.addLayout(operatorLayout);
mainLayout.addWidget(self.tableView);
mainLayout.addLayout(statusLayout);
self.setLayout(mainLayout)
# 设置表格
def setTableView(self):
print('*** step2 SetTableView' )
self.db = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
self.db.setDatabaseName('./db/database.db')
# 打开数据库
self.db.open()
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount) )
print('totalPage=' + str(self.totalPage) )
# 设置表格表头
self.queryModel.setHeaderData(0,Qt.Horizontal,"编号")
self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名")
self.queryModel.setHeaderData(2,Qt.Horizontal,"性别")
self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄")
self.queryModel.setHeaderData(4,Qt.Horizontal,"院系")
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from student")
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount) )
return rowCount
# 得到页数
def getPageCount(self):
if self.totalRecrodCount % self.PageRecordCount == 0 :
return (self.totalRecrodCount / self.PageRecordCount )
else :
return (self.totalRecrodCount / self.PageRecordCount + 1)
# 记录查询
def recordQuery(self, limitIndex ):
szQuery = ("select * from student limit %d,%d" % ( limitIndex , self.PageRecordCount ) )
print('query sql=' + szQuery )
self.queryModel.setQuery(szQuery)
# 刷新状态
def updateStatus(self):
szCurrentText = ("当前第%d页" % self.currentPage )
self.currentPageLabel.setText( szCurrentText )
#设置按钮是否可用
if self.currentPage == 1 :
self.prevButton.setEnabled( False )
self.nextButton.setEnabled( True )
elif self.currentPage == self.totalPage :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( False )
else :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( True )
# 设置总数页文本
def setTotalPageLabel(self):
szPageCountText = ("总共%d页" % self.totalPage )
self.totalPageLabel.setText(szPageCountText)
# 设置总记录数
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d条" % self.totalRecrodCount )
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText )
self.totalRecordLabel.setText(szTotalRecordText)
# 前一页按钮按下
def onPrevButtonClick(self):
print('*** onPrevButtonClick ');
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage -= 1
self.updateStatus()
# 后一页按钮按下
def onNextButtonClick(self):
print('*** onNextButtonClick ');
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage += 1
self.updateStatus()
# 转到页按钮按下
def onSwitchPageButtonClick(self):
# 得到输入字符串
szText = self.switchPageLineEdit.text()
#数字正则表达式
pattern = re.compile(r'^[0-9]+$')
match = pattern.match(szText)
# 判断是否为数字
if not match :
QMessageBox.information(self, "提示", "请输入数字" )
return
# 是否为空
if szText == '' :
QMessageBox.information(self, "提示" , "请输入跳转页面" )
return
#得到页数
pageIndex = int(szText)
#判断是否有指定页
if pageIndex > self.totalPage or pageIndex < 1 :
QMessageBox.information(self, "提示", "没有指定的页面，请重新输入" )
return
#得到查询起始行号
limitIndex = (pageIndex-1) * self.PageRecordCount
#记录查询
self.recordQuery(limitIndex)
#设置当前页
self.currentPage = pageIndex
#刷新状态
self.updateStatus()
class QmyMainWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui = Ui_MainWindow() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.setCentralWidget(self.ui.splitter)
# tableView显示属性设置
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
## self.ui.tableView.resizeColumnsToContents()
## ==============自定义功能函数============
def __getFieldNames(self): ##获取所有字段名称
emptyRec = self.qryModel.record() #获取空记录，只有字段名
self.fldNum = {} #字段名与序号的字典
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
def __openTable(self): ##查询数据
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province,
Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误,错误信息\n" + self.qryModel.lastError().text())
return
self.ui.statusBar.showMessage("记录条数：%d" % self.qryModel.rowCount())
self.__getFieldNames() #获取字段名和序号
##设置字段显示名，直接使用序号
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
## self.qryModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal, "工号")
## self.qryModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal, "姓名")
## self.qryModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal, "性别")
## self.qryModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal, "出生日期")
## self.qryModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal, "省份")
## self.qryModel.setHeaderData(self.fldNum["Department"], Qt.Horizontal, "部门")
## self.qryModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal, "工资")
##创建界面组件与数据模型的字段之间的数据映射
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.qryModel) #设置数据模型
## self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit)
##界面组件与qryModel的具体字段之间的联系
## self.mapper.addMapping(self.ui.dbSpinEmpNo, self.fldNum["empNo"])
## self.mapper.addMapping(self.ui.dbEditName, self.fldNum["Name"])
## self.mapper.addMapping(self.ui.dbComboSex, self.fldNum["Gender"])
## self.mapper.addMapping(self.ui.dbEditBirth, self.fldNum["Birthday"])
## self.mapper.addMapping(self.ui.dbComboProvince, self.fldNum["Province"] )
## self.mapper.addMapping(self.ui.dbComboDep, self.fldNum["Department"] )
## self.mapper.addMapping(self.ui.dbSpinSalary,self.fldNum["Salary"] )
self.mapper.addMapping(self.ui.dbSpinEmpNo, 0)
self.mapper.addMapping(self.ui.dbEditName, 1)
self.mapper.addMapping(self.ui.dbComboSex, 2)
self.mapper.addMapping(self.ui.dbEditBirth, 3)
self.mapper.addMapping(self.ui.dbComboProvince, 4)
self.mapper.addMapping(self.ui.dbComboDep, 5)
self.mapper.addMapping(self.ui.dbSpinSalary, 6)
self.mapper.toFirst() #移动到首记录
self.selModel = QItemSelectionModel(self.qryModel) #关联选择模型
self.selModel.currentRowChanged.connect(
self.do_currentRowChanged) #选择行变化时
self.ui.tableView.setModel(self.qryModel) #设置数据模型
self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型
self.ui.actOpenDB.setEnabled(False)
def __refreshTableView(self): ##刷新tableView显示
index = self.mapper.currentIndex()
curIndex = self.qryModel.index(index, 1) #QModelIndex
self.selModel.clearSelection() #清空选择项
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
## ==========由connectSlotsByName() 自动连接的槽函数==================
@pyqtSlot() ##“打开数据库”按钮
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
#打开数据库
self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动
self.DB.setDatabaseName(dbFilename) #设置数据库名称
## DB.setHostName()
## DB.setUserName()
## DB.setPassword()
if self.DB.open(): #打开数据库
self.__openTable() #打开数据表
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot() ##首记录
def on_actRecFirst_triggered(self):
self.mapper.toFirst()
self.__refreshTableView()
@pyqtSlot() ##前一记录
def on_actRecPrevious_triggered(self):
self.mapper.toPrevious()
self.__refreshTableView()
@pyqtSlot() ##后一条记录
def on_actRecNext_triggered(self):
self.mapper.toNext()
self.__refreshTableView()
@pyqtSlot() ##最后一条记录
def on_actRecLast_triggered(self):
self.mapper.toLast()
self.__refreshTableView()
## =============自定义槽函数===============================
def do_currentRowChanged(self, current, previous): ##记录移动时触发
if (current.isValid() == False):
self.ui.dbLabPhoto.clear() #清除图片显示
return
self.mapper.setCurrentIndex(current.row()) #更新数据映射的行号
first = (current.row() == 0) #是否首记录
last = (current.row() == self.qryModel.rowCount() - 1) #是否尾记录
self.ui.actRecFirst.setEnabled(not first) #更新使能状态
self.ui.actRecPrevious.setEnabled(not first)
self.ui.actRecNext.setEnabled(not last)
self.ui.actRecLast.setEnabled(not last)
curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型
empNo = curRec.value("EmpNo") #不需要加 toInt()函数
query = QSqlQuery(self.DB)
query.prepare(
'''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''')
query.bindValue(":ID", empNo)
## if not query.exec_(): #注意，在PyQt5.11.2之前的版本里只能使用exec_()函数
if not query.exec(
): #注意，在PyQt5.11.2添加了遗漏的overload型exec()函数，在PyQt5.11.2里没问题了
QMessageBox.critical(self, "错误",
"执行SQL语句错误\n" + query.lastError().text())
return
else:
query.first()
picData = query.value("Photo")
if (picData == None): #图片字段内容为空
self.ui.dbLabPhoto.clear()
else: #显示照片
pic = QPixmap()
pic.loadFromData(picData)
W = self.ui.dbLabPhoto.size().width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W))
memoData = query.value("Memo") #显示备注
self.ui.dbEditMemo.setPlainText(memoData)
class WarehouseController(QObject):
def __init__(self, list, table, addButton, editButton, deleteButton, dbase):
super().__init__()
self.list = list
self.table = table
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.shopModel = QSqlTableModel(db = dbase)
self.shopModel.setTable("shop")
self.shopModel.select()
self.list.setModel(ComplexListModel(self.shopModel, "{name}"))
self.list.selectionModel().currentChanged.connect(self.listSelectionChanged)
self._checkPrivileges()
if self.only_select:
self.addButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def _checkPrivileges(self):
query = QSqlQuery("SHOW GRANTS")
only_select = None
table_pattern = "`{}`".format("shop_detail").lower()
while query.next():
s = query.value(0).lower()
if table_pattern in s:
if "select" in s and only_select is None:
only_select = True
else:
only_select = False
self.only_select = bool(only_select)
def listSelectionChanged(self, cur, prev):
if not cur.isValid():
return self.table.setModel(None)
else:
self.setShopIndex(cur.row())
def setShopIndex(self, row):
record = self.shopModel.record(row)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as dtl, shop_detail.quantity as qnt \
FROM shop_detail INNER JOIN detail \
ON shop_detail.detail_id = detail.id \
WHERE shop_detail.shop_id={} ORDER BY dtl".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Количество")
self.table.setModel(self.detailModel)
self.table.hideColumn(0)
self.table.resizeColumnsToContents()
self.table.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
def tableSelectionChanged(self, cur, prev):
if self.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM shop_detail \
WHERE shop_id={}))".format(shop.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар на склад: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
1, 1)
if not ok: return
detail_id = details[choice]
shop_id = shop.value("id")
query = QSqlQuery("INSERT INTO shop_detail (shop_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(shop_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
self.table.selectionModel().clearSelection()
def editButtonClicked(self):
detail = self.detailModel.record(self.table.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
shop = self.shopModel.record(self.list.currentIndex().row())
if qnt > 0:
query = QSqlQuery("UPDATE shop_detail SET quantity={} \
WHERE shop_id={} AND detail_id={}".format(qnt,
shop.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def deleteButtonClicked(self):
if not self.table.currentIndex().isValid(): return
detail = self.detailModel.record(self.table.currentIndex().row())
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def update(self):
cur = self.list.currentIndex()
if cur.isValid():
row = cur.row()
else:
row = 0
self.shopModel.select()
self.list.reset()
self.selectRow(row)
def selectRow(self, row):
self.list.selectionModel().clearSelection()
self.list.selectionModel().setCurrentIndex(
self.shopModel.index(row, 0), QItemSelectionModel.Select)
class MainWindow(QWidget):
def __init__(self, token: str):
connection = sqlite3.connect("users.sqlite")
query = """
SELECT
privileges.privilege
FROM
privileges
JOIN users ON privileges.username = users.username
WHERE
users.username = ?;
"""
self.privileges = set(
_[0] for _ in connection.execute(query, (token, )).fetchall())
connection.close()
super().__init__()
self.setWindowTitle("CSM")
tabs = QTabWidget()
if "SELECT" in self.privileges:
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("data.sqlite")
db.open()
if any(privilege in self.privileges
for privilege in ("UPDATE", "INSERT", "DELETE")):
self.people_model = QSqlTableModel()
self.people_model.setTable("people")
if "UPDATE" in self.privileges:
self.people_model.setEditStrategy(
QSqlTableModel.OnFieldChange)
self.people_model.select()
self.addresses_model = QSqlTableModel()
self.addresses_model.setTable("addresses")
if "UPDATE" in self.privileges:
self.addresses_model.setEditStrategy(
QSqlTableModel.OnFieldChange)
self.addresses_model.select()
else:
self.people_model = QSqlQueryModel()
self.people_model.setQuery("SELECT * FROM people;")
self.addresses_model = QSqlQueryModel()
self.addresses_model.setQuery("SELECT * FROM addresses;")
self.people_model.setHeaderData(1, Qt.Horizontal, "Full name")
self.people_model.setHeaderData(2, Qt.Horizontal, "Telephone")
self.addresses_model.setHeaderData(1, Qt.Horizontal, "Person id")
self.addresses_model.setHeaderData(2, Qt.Horizontal, "Street")
self.addresses_model.setHeaderData(3, Qt.Horizontal, "City")
self.addresses_model.setHeaderData(4, Qt.Horizontal, "State")
self.people_table = QTableView()
self.people_table.setModel(self.people_model)
if "UPDATE" not in self.privileges:
self.people_table.setEditTriggers(
QAbstractItemView.NoEditTriggers)
self.people_table.horizontalHeader().setSectionResizeMode(
0, QHeaderView.ResizeToContents)
self.people_table.horizontalHeader().setSectionResizeMode(
1, QHeaderView.Stretch)
self.people_table.horizontalHeader().setSectionResizeMode(
2, QHeaderView.Stretch)
self.people_table.setSizeAdjustPolicy(
QAbstractScrollArea.AdjustToContents)
self.people_table.resizeColumnsToContents()
self.addresses_table = QTableView()
self.addresses_table.setModel(self.addresses_model)
if "UPDATE" not in self.privileges:
self.addresses_table.setEditTriggers(
QAbstractItemView.NoEditTriggers)
self.addresses_table.hideColumn(0)
self.addresses_table.horizontalHeader().setSectionResizeMode(
1, QHeaderView.ResizeToContents)
self.addresses_table.horizontalHeader().setSectionResizeMode(
2, QHeaderView.Stretch)
self.addresses_table.horizontalHeader().setSectionResizeMode(
3, QHeaderView.Stretch)
self.addresses_table.horizontalHeader().setSectionResizeMode(
4, QHeaderView.Stretch)
self.addresses_table.setSizeAdjustPolicy(
QAbstractScrollArea.AdjustToContents)
self.addresses_table.resizeColumnsToContents()
self.people_table.setMinimumWidth(self.people_table.width())
self.addresses_table.setMinimumWidth(self.addresses_table.width())
tabs.addTab(self.people_table, "People")
tabs.addTab(self.addresses_table, "Addresses")
tabs.currentChanged.connect(self.tab_switch_handler)
HLayout = QHBoxLayout(self)
HLayout.addWidget(tabs)
self.VLayout = QVBoxLayout()
self.VLayout.setAlignment(Qt.AlignTop)
token_label = QLabel(token)
self.VLayout.addWidget(token_label)
if "INSERT" in self.privileges:
self.insert_stack = QStackedWidget()
self.people_insert = people_InsertWidget()
self.people_insert.submit_button.clicked.connect(
self.insert_people_handler)
self.addresses_insert = addresses_InsertWidget()
self.addresses_insert.submit_button.clicked.connect(
self.insert_address_handler)
self.insert_stack.addWidget(self.people_insert)
self.insert_stack.addWidget(self.addresses_insert)
self.insert_stack.setCurrentWidget(self.people_insert)
self.insert_stack.setFixedSize(self.insert_stack.sizeHint())
self.VLayout.addWidget(self.insert_stack)
self.VLayout.addSpacing(10)
if "DELETE" in self.privileges:
self.delete_stack = QStackedWidget()
self.people_delete = QPushButton("DELETE FROM people")
self.people_delete.clicked.connect(self.delete_people_handler)
self.address_delete = QPushButton("DELETE FROM addresses")
self.address_delete.clicked.connect(self.delete_address_handler)
self.delete_stack.addWidget(self.people_delete)
self.delete_stack.addWidget(self.address_delete)
self.delete_stack.setCurrentWidget(self.people_delete)
self.delete_stack.setFixedSize(self.delete_stack.sizeHint())
self.VLayout.addWidget(self.delete_stack)
HLayout.addLayout(self.VLayout)
#self.resize(self.sizeHint())
def tab_switch_handler(self, tab: int):
if tab == 0:
if "INSERT" in self.privileges:
self.insert_stack.setCurrentWidget(self.people_insert)
if "DELETE" in self.privileges:
self.delete_stack.setCurrentWidget(self.people_delete)
elif tab == 1:
if "INSERT" in self.privileges:
self.insert_stack.setCurrentWidget(self.addresses_insert)
if "DELETE" in self.privileges:
self.delete_stack.setCurrentWidget(self.address_delete)
else:
QMessageBox.warning(self, "Error", "Something went wrong!")
def insert_people_handler(self):
full_name = self.people_insert.full_name.text()
telephone = self.people_insert.telephone.text()
record = self.people_model.record()
record.setGenerated("id", True)
record.setValue("full_name", full_name)
record.setValue("telephone", telephone)
if self.people_model.insertRecord(-1, record):
self.people_model.select()
else:
QMessageBox.warning(
self, "Error",
"Cannot insert {} {}".format(full_name, telephone))
def insert_address_handler(self):
user_id = int(self.addresses_insert.user_id.text())
street = self.addresses_insert.street.text()
city = self.addresses_insert.city.text()
state = self.addresses_insert.state.text()
record = self.addresses_model.record()
record.setGenerated("id", True)
record.setValue("user_id", user_id)
record.setValue("street", street)
record.setValue("city", city)
record.setValue("state", state)
if self.addresses_model.insertRecord(-1, record):
self.addresses_model.select()
else:
QMessageBox.warning(
self, "Error",
"Cannot insert {} {} {} {}".format(user_id, street, city,
state))
def delete_people_handler(self):
rows = self.people_table.selectionModel().selectedRows()
if len(rows) == 1:
if not self.people_model.removeRow(rows[0].row()):
QMessageBox.warning(
self, "Error",
"Cannot delete row {}".format(rows[0].row() + 1))
else:
self.people_model.select()
else:
QMessageBox.warning(self, "Error",
"Only 1 row can be deleted at a time")
def delete_address_handler(self):
rows = self.addresses_table.selectionModel().selectedRows()
if len(rows) == 1:
if not self.addresses_model.removeRow(rows[0].row()):
QMessageBox.warning(
self, "Error",
"Cannot delete row {}".format(rows[0].row() + 1))
else:
self.addresses_model.select()
else:
QMessageBox.warning(self, "Error",
"Only 1 row can be deleted at a time")
class ProjectAlleles(FilterableTable):
"""a widget to display all alleles of one project,
with their most important data
"""
changed_allele = pyqtSignal(str, int, str)
change_view = pyqtSignal(int)
def __init__(self, log, mydb):
super().__init__(log, mydb, add_color_proxy = (4,5))
self.proxy.setFilterKeyColumn(2)
self.filter_cb.setCurrentIndex(2)
self.header_lbl.setText("Alleles:")
self.table.verticalHeader().hide()
self.table.customContextMenuRequested.connect(self.open_menu)
self.project = ""
self.filter(self.project)
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
q = QSqlQuery()
query = """SELECT project_name, project_nr,
(sample_id_int || ' #' || allele_nr || ' (' || gene || ')'),
local_name,
allele_status, lab_status,
sample_id_int, allele_nr
FROM alleles
order by project_nr
"""
q.exec_(query)
self.check_error(q)
self.model.setQuery(q)
self.model.setHeaderData(1, Qt.Horizontal, "Nr")
self.model.setHeaderData(2, Qt.Horizontal, "Target Allele")
self.model.setHeaderData(3, Qt.Horizontal, "Allele Name")
self.model.setHeaderData(4, Qt.Horizontal, "Allele Status")
self.model.setHeaderData(5, Qt.Horizontal, "Lab Status")
self.log.debug("\t=> Done!")
def filter(self, project):
self.project = project
self.proxy.layoutAboutToBeChanged.emit()
self.proxy.setFilterKeyColumn(0)
self.proxy.setFilterFixedString(project)
self.proxy.layoutChanged.emit()
for col in [0, 6, 7]:
self.table.hideColumn(col)
@pyqtSlot()
def refresh(self):
"""refreshes the table's content
"""
self.log.debug("\tRefreshing ProjectView's allele list...")
self.model.setQuery(self.model.query().lastQuery())
@pyqtSlot(QPoint)
def open_menu(self, pos):
"""provides a context menu
"""
try:
menu = QMenu()
open_allele_act = menu.addAction("Open Allele View")
action = menu.exec_(self.table.mapToGlobal(pos))
if action:
row = self.table.indexAt(pos).row()
sample = self.proxy.data(self.proxy.index(row, 6))
allele_nr = int(self.proxy.data(self.proxy.index(row, 7)))
if action == open_allele_act:
self.changed_allele.emit(sample, allele_nr, self.project)
self.change_view.emit(4)
self.log.debug("ProjectAlleles emitted changed_allele to {} #{} ({}) & change_view to AlleleView".format(sample, allele_nr, self.project))
except Exception as E:
self.log.exception(E)
class QmyMainWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui = Ui_MainWindow() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.setCentralWidget(self.ui.tableView)
# tableView显示属性设置
## self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
## self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
## self.ui.tableView.resizeColumnsToContents()
## ==============自定义功能函数============
def __getFieldNames(self): ##获取所有字段名称
emptyRec = self.qryModel.record() #获取空记录，只有字段名
self.fldNum = {} #字段名与序号的字典
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
def __openTable(self): #查询数据
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province,
Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误,错误信息\n" + self.qryModel.lastError().text())
return
self.__getFieldNames() #获取字段名和序号
##字段显示名
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
## self.qryModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal,"工号")
## self.qryModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal,"姓名")
## self.qryModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal,"性别")
## self.qryModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal,"出生日期")
## self.qryModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal,"省份")
## self.qryModel.setHeaderData(self.fldNum["Department"],Qt.Horizontal,"部门")
## self.qryModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal,"工资")
self.selModel = QItemSelectionModel(self.qryModel) #关联选择模型
##选择行变化时
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.qryModel) #设置数据模型
self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型
self.ui.actOpenDB.setEnabled(False)
self.ui.actRecInsert.setEnabled(True)
self.ui.actRecDelete.setEnabled(True)
self.ui.actRecEdit.setEnabled(True)
self.ui.actScan.setEnabled(True)
self.ui.actTestSQL.setEnabled(True)
def __updateRecord(self, recNo): ##更新一条记录
curRec = self.qryModel.record(recNo) #获取当前记录
empNo = curRec.value("EmpNo") #获取EmpNo
query = QSqlQuery(self.DB) #查询出当前记录的所有字段
query.prepare("SELECT * FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
query.exec() #
query.first()
if (not query.isValid()): #是否为有效记录
return
curRec = query.record() #获取当前记录的数据，QSqlRecord类型
dlgData = QmyDialogData(self) #创建对话框
dlgData.setUpdateRecord(curRec) #调用对话框函数更新数据和界面
ret = dlgData.exec() # 以模态方式显示对话框
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData() #获得对话框返回的记录
query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender,
Birthday=:Birthday, Province=:Province,
Department=:Department, Salary=:Salary,
Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''')
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
query.bindValue(":ID", empNo)
## if (not query.exec_()):
if (not query.exec()): #PyQt 5.11.2以前应该使用exec_()函数
QMessageBox.critical(self, "错误",
"记录更新错误\n" + query.lastError().text())
else:
self.qryModel.query().exec() #数据模型重新查询数据，更新tableView显示
## ==========由connectSlotsByName() 自动连接的槽函数==================
@pyqtSlot() ##打开数据库
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
#打开数据库
self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动
self.DB.setDatabaseName(dbFilename) #设置数据库名称
## DB.setHostName()
## DB.setUserName()
## DB.setPassword()
if self.DB.open(): #打开数据库
self.__openTable() #查询数据
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot() ##插入记录
def on_actRecInsert_triggered(self):
query = QSqlQuery(self.DB)
query.exec("select * from employee where EmpNo =-1") #实际不查询出记录，只查询字段信息
curRec = query.record() #获取当前记录,实际为空记录,但有字段信息
curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000)
dlgData = QmyDialogData(self)
dlgData.setInsertRecord(curRec) #插入记录
ret = dlgData.exec() #以模态方式显示对话框
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData()
query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday,
Province,Department,Salary,Memo,Photo)
VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province,
:Department,:Salary,:Memo,:Photo)''')
query.bindValue(":EmpNo", recData.value("EmpNo"))
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
res = query.exec() #执行SQL语句
if (res == False):
QMessageBox.critical(self, "错误",
"插入记录错误\n" + query.lastError().text())
else: #插入，删除记录后需要重新设置SQL语句查询
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
@pyqtSlot() ##删除记录
def on_actRecDelete_triggered(self):
curRecNo = self.selModel.currentIndex().row()
curRec = self.qryModel.record(curRecNo) #获取当前记录
if (curRec.isEmpty()): #当前为空记录
return
empNo = curRec.value("EmpNo") #获取员工编号
query = QSqlQuery(self.DB)
query.prepare("DELETE FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
if (query.exec() == False):
QMessageBox.critical(self, "错误",
"删除记录出现错误\n" + query.lastError().text())
else: #插入，删除记录后需要重新设置SQL语句查询
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
@pyqtSlot() ##编辑记录
def on_actRecEdit_triggered(self):
curRecNo = self.selModel.currentIndex().row()
self.__updateRecord(curRecNo)
## @pyqtSlot() ##双击编辑记录
def on_tableView_doubleClicked(self, index):
curRecNo = index.row()
self.__updateRecord(curRecNo)
@pyqtSlot() ##遍历记录，涨工资
def on_actScan_triggered(self):
qryEmpList = QSqlQuery(self.DB) #员工工资信息列表
qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo")
qryUpdate = QSqlQuery(self.DB) #临时 QSqlQuery
qryUpdate.prepare(
'''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''')
qryEmpList.first()
while (qryEmpList.isValid()): #当前记录有效
empID = qryEmpList.value("empNo") #获取empNo
salary = qryEmpList.value("Salary") #获取Salary
salary = salary + 500 #涨工资
qryUpdate.bindValue(":ID", empID)
qryUpdate.bindValue(":Salary", salary) #设置SQL语句参数
qryUpdate.exec() #执行update语句
if not qryEmpList.next(): #移动到下一条记录，并判断是否到末尾了
break
self.qryModel.query().exec() #数据模型重新查询数据，更新tableView的显示
QMessageBox.information(self, "提示", "涨工资计算完毕")
@pyqtSlot() ##SQL语句测试
def on_actTestSQL_triggered(self):
query = QSqlQuery(self.DB)
## # SQL语句测试1， exec_() 和exec()都可以直接执行不带参数的SQL语句
## query.exec('''UPDATE employee SET Salary=3000 where Gender="女" ''')
## query.exec_('''UPDATE employee SET Salary=4500 where Gender="女" ''')
# SQL语句测试2，执行带参数的SQL语句，只能用 exec_()，不能用exec()
## query.prepare('''UPDATE employee SET Salary=9000 where Gender=:Gender ''')
## query.bindValue(":Gender","男")
## query.exec()
query.exec('''UPDATE employee SET Salary=500+Salary ''')
## query.bindValue(":Gender","男")
## query.exec()
## query.prepare("UPDATE employee SET Department=?, Salary=? where Name=?")
## query.bindValue(0, "技术部")
## query.bindValue(1, 5500)
## query.bindValue(2, "张三")
## query.exec_() #只能用exec_()，而不能用exec()函数
## self.qryModel.query().exec() #不增减记录时更新显示
## 增减记录后的更新显示
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
print("SQL OK")
## =============自定义槽函数===============================
def do_currentRowChanged(self, current, previous): ##行切换时触发
if (current.isValid() == False):
return
curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型
empNo = curRec.value("EmpNo") #不需要加 toInt()函数
self.ui.statusBar.showMessage("当前记录：工号=%d" % empNo)
class QmyMainWindow(QtWidgets.QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.setCentralWidget(self.ui.splitter)
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
@pyqtSlot()
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
self.DB = QSqlDatabase.addDatabase("QSQLITE")
self.DB.setDatabaseName(dbFilename)
if self.DB.open():
self.__openTable()
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
def __openTable(self):
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误，错误信息\n" + self.qryModel.lastError().text())
return
self.ui.statusBar.showMessage("记录条数:%d" % self.qryModel.rowCount())
self.__getFieldNames()
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.qryModel)
self.mapper.addMapping(self.ui.dbSpinEmpNo, 0)
self.mapper.addMapping(self.ui.dbEditName, 1)
self.mapper.addMapping(self.ui.dbComboSex, 2)
self.mapper.addMapping(self.ui.dbEditBirth, 3)
self.mapper.addMapping(self.ui.dbComboProvince, 4)
self.mapper.addMapping(self.ui.dbComboDep, 5)
self.mapper.addMapping(self.ui.dbSpinSalary, 6)
self.mapper.toFirst()
self.selModel = QItemSelectionModel(self.qryModel)
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.qryModel)
self.ui.tableView.setSelectionModel(self.selModel)
self.ui.actOpenDB.setEnabled(False)
def __refreshTableView(self):
index = self.mapper.currentIndex()
curIndex = self.qryModel.index(index, 1)
self.selModel.clearSelection()
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
def __getFieldNames(self):
emptyRec = self.qryModel.record()
self.fldNum = {}
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
@pyqtSlot()
def on_actRecFirst_triggered(self):
self.mapper.toFirst()
self.__refreshTableView()
@pyqtSlot()
def on_actRecPrevious_triggered(self):
self.mapper.toPrevious()
self.__refreshTableView()
@pyqtSlot()
def on_actRecNext_triggered(self):
self.mapper.toNext()
self.__refreshTableView()
@pyqtSlot()
def on_actRecLast_triggered(self):
self.mapper.toLast()
self.__refreshTableView()
def do_currentRowChanged(self, current, previous):
if (current.isValid() == False):
self.ui.dbLabPhoto.clear()
return
self.mapper.setCurrentIndex(current.row())
first = (current.row() == 0)
last = (current.row() == self.qryModel.rowCount() - 1)
self.ui.actRecFirst.setEnabled(not first)
self.ui.actRecPrevious.setEnabled(not first)
self.ui.actRecNext.setEnabled(not last)
self.ui.actRecLast.setEnabled(not last)
curRec = self.qryModel.record(current.row())
empNo = curRec.value("EmpNo")
query = QSqlQuery(self.DB)
query.prepare(
'''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''')
query.bindValue(":ID", empNo)
if not query.exec():
QMessageBox.critical(self, "错误",
"执行SQL语句错误\n" + query.lastError().text())
return
else:
query.first()
picData = query.value("Photo")
if (picData == None):
self.ui.dbLabPhoto.clear()
else:
pic = QPixmap()
pic.loadFromData(picData)
W = self.ui.dbLabPhoto.size().width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W))
memoData = query.value("Memo")
self.ui.dbEditMemo.setPlainText(memoData)
class QmyMainWindow(QtWidgets.QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.setCentralWidget(self.ui.tableView)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
def __getFieldNames(self):
emptyRec = self.qryModel.record()
self.fldNum = {}
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
def __openTable(self):
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误,错误信息\n" + self.qryModel.lastError().text())
return
self.__getFieldNames()
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
self.selModel = QItemSelectionModel(self.qryModel)
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.qryModel)
self.ui.tableView.setSelectionModel(self.selModel)
self.ui.actOpenDB.setEnabled(False)
self.ui.actRecInsert.setEnabled(True)
self.ui.actRecDelete.setEnabled(True)
self.ui.actRecEdit.setEnabled(True)
self.ui.actScan.setEnabled(True)
self.ui.actTestSQL.setEnabled(True)
def __updateRecord(self, recNo):
curRec = self.qryModel.record(recNo)
empNo = curRec.value("EmpNo")
query = QSqlQuery(self.DB)
query.prepare("SELECT * FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
query.exec()
query.first()
if (not query.isValid()):
return
curRec = query.record()
dlgData = QmyDialogData(self)
dlgData.setUpdateRecord(curRec)
ret = dlgData.exec()
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData()
query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender,
Birthday=:Birthday, Province=:Province,
Department=:Department, Salary=:Salary,
Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''')
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
query.bindValue(":ID", empNo)
if (not query.exec()):
QMessageBox.critical(self, "错误",
"记录更新错误\n" + query.lastError().text())
else:
self.qryModel.query().exec()
@pyqtSlot()
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
self.DB = QSqlDatabase.addDatabase("QSQLITE")
self.DB.setDatabaseName(dbFilename)
if self.DB.open():
self.__openTable()
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot()
def on_actRecInsert_triggered(self):
query = QSqlQuery(self.DB)
query.exec("select * from employee where EmpNo = -1")
curRec = query.record()
curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000)
dlgData = QmyDialogData(self)
dlgData.setInsertRecord(curRec)
ret = dlgData.exec()
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData()
query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday,
Province,Department,Salary,Memo,Photo)
VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province,
:Department,:Salary,:Memo,:Photo)''')
query.bindValue(":EmpNo", recData.value("EmpNo"))
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
res = query.exec()
if (res == False):
QMessageBox.critical(self, "错误",
"插入记录错误\n" + query.lastError().text())
else:
sqlStr = self.qryModel.query().executedQuery()
self.qryModel.setQuery(sqlStr)
@pyqtSlot()
def on_actRecDelete_triggered(self):
curRecNo = self.selModel.currentIndex().row()
curRec = self.qryModel.record(curRecNo)
if (curRec.isEmpty()):
return
empNo = curRec.value("EmpNo")
query = QSqlQuery(self.DB)
query.prepare("DELETE FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
if (query.exec() == False):
QMessageBox.critical(self, "错误",
"删除记录出现错误\n" + query.lastError().text())
else:
sqlStr = self.qryModel.query().executedQuery()
self.qryModel.setQuery(sqlStr)
@pyqtSlot()
def on_actRecEdit_triggered(self):
curRecNo = self.selModel.currentIndex().row()
self.__updateRecord(curRecNo)
def on_tableView_doubleClicked(self, index):
curRecNo = index.row()
self.__updateRecord(curRecNo)
@pyqtSlot()
def on_actScan_triggered(self):
qryEmpList = QSqlQuery(self.DB)
qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo")
qryUpdate = QSqlQuery(self.DB)
qryUpdate.prepare(
'''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''')
qryEmpList.first()
while (qryEmpList.isValid()):
empID = qryEmpList.value("empNo")
salary = qryEmpList.value("Salary")
salary = salary + 500
qryUpdate.bindValue(":ID", empID)
qryUpdate.bindValue(":Salary", salary)
qryUpdate.exec()
if not qryEmpList.next():
break
self.qryModel.query().exec()
QMessageBox.information(self, "提示", "涨工资计算完毕")
@pyqtSlot()
def on_actTestSQL_triggered(self):
query = QSqlQuery(self.DB)
query.exec('''UPDATE employee SET Salary=500+Salary''')
sqlStr = self.qryModel.query().executedQuery()
self.qryModel.setQuery(sqlStr)
print("SQL OK")
def do_currentRowChanged(self, current, previous):
if (current.isValid() == False):
return
curRec = self.qryModel.record(current.row())
empNo = curRec.value("EmpNo")
self.ui.statusBar.showMessage("当前记录：工号 = %d" % empNo)
# Fill
for i in range(1000 - 1):
value = ''.join(string.ascii_lowercase[j]
for j in map(int,
str(i).zfill(3)))
db.exec(f"INSERT OR IGNORE INTO Dict (key, value) VALUES ({i}, {value!r})")
WINDOW_WIDTH = 400
WINDOW_HEIGHT = 300
app = QApplication([])
model = QSqlQueryModel()
model.setQuery("SELECT id, key, value FROM Dict")
model.setHeaderData(0, Qt.Horizontal, "ID")
model.setHeaderData(1, Qt.Horizontal, "KEY")
model.setHeaderData(2, Qt.Horizontal, "VALUE")
list_view = QListView()
list_view.setWindowTitle('QListView')
list_view.setModel(model)
list_view.move(100, 50)
list_view.resize(WINDOW_WIDTH, WINDOW_HEIGHT)
list_view.show()
list_view_2 = QListView()
list_view_2.setWindowTitle('QListView + ItemDelegate')
list_view_2.setItemDelegate(ListViewDelegate())
list_view_2.setModel(model)
list_view_2.move(list_view.geometry().right(), 50)
class DataGrid(QWidget):
def __init__(self, input_num):
super().__init__()
self.setWindowTitle("订单信息查询，按号查找")
self.setWindowFlags(Qt.WindowStaysOnTopHint)
self.input_num = input_num
self.resize(750, 300)
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
self.labelMMM = None
# 总数页文本
self.totalPageLabel = None
# 当前页文本
self.currentPageLabel = None
# 转到页输入框
self.switchPageLineEdit = None
# 前一页按钮
self.prevButton = None
# 后一页按钮
self.nextButton = None
# 转到页按钮
self.switchPageButton = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 5
self.db = None
self.initUI()
def initUI(self):
# 创建窗口
self.createWindow()
# 设置表格
self.setTableView()
# 信号槽连接
self.prevButton.clicked.connect(self.onPrevButtonClick)
self.nextButton.clicked.connect(self.onNextButtonClick)
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick)
self.changeButton.clicked.connect(self.change)
self.deleteButton.clicked.connect(self.delete)
def closeEvent(self, event):
# 关闭数据库
self.db.close()
# 创建窗口
def createWindow(self):
# 操作布局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一页")
self.nextButton = QPushButton("后一页")
self.changeButton = QPushButton("修改")
self.deleteButton = QPushButton("删除")
self.labelMMM = QLabel(" " * 20)
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("转到第")
page = QLabel("页")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget(self.labelMMM)
operatorLayout.addWidget(self.changeButton)
operatorLayout.addWidget(self.deleteButton)
operatorLayout.addWidget(QSplitter())
# 状态布局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget(QSplitter())
statusLayout.addWidget(self.totalRecordLabel)
# 设置表格属性
self.tableView = QTableView()
# 表格宽度的自适应调整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
# 创建界面
mainLayout = QVBoxLayout(self)
mainLayout.addLayout(operatorLayout)
mainLayout.addWidget(self.tableView)
mainLayout.addLayout(statusLayout)
self.setLayout(mainLayout)
# 设置表格
def setTableView(self):
print('*** step2 SetTableView')
self.db = QSqlDatabase.addDatabase('QMYSQL')
self.db.setHostName("67.209.xxx.xxx")
self.db.setDatabaseName("db_sharedbike")
self.db.setUserName("root")
self.db.setPassword("xxxxxxxxx")
# 打开数据库
self.db.open()
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 设置表格表头
self.queryModel.setHeaderData(0, Qt.Horizontal, "订单编号")
self.queryModel.setHeaderData(1, Qt.Horizontal, "用户编号")
self.queryModel.setHeaderData(2, Qt.Horizontal, "自行车编号")
self.queryModel.setHeaderData(3, Qt.Horizontal, "下单时间")
self.queryModel.setHeaderData(4, Qt.Horizontal, "结账时间")
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from order_info where id = %s" %
(self.input_num))
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount))
return rowCount
# 得到页数
def getPageCount(self):
if self.totalRecrodCount == 0:
self.currentPage = 0
if self.totalRecrodCount % self.PageRecordCount == 0:
return (self.totalRecrodCount / self.PageRecordCount)
else:
return (int(self.totalRecrodCount / self.PageRecordCount) + 1)
# 记录查询
def recordQuery(self, limitIndex):
szQuery = ("select * from order_info where id=%s limit %d,%d" %
(self.input_num, limitIndex, self.PageRecordCount))
print('query sql=' + szQuery)
self.queryModel.setQuery(szQuery)
# 刷新状态
def updateStatus(self):
szCurrentText = ("当前第%d页" % self.currentPage)
self.currentPageLabel.setText(szCurrentText)
#设置按钮是否可用
if self.currentPage == 0:
self.prevButton.setEnabled(False)
self.nextButton.setEnabled(False)
return
if self.currentPage == 1:
self.prevButton.setEnabled(False)
else:
self.prevButton.setEnabled(True)
if self.currentPage == self.totalPage:
self.nextButton.setEnabled(False)
else:
self.nextButton.setEnabled(True)
# 设置总数页文本
def setTotalPageLabel(self):
szPageCountText = ("总共%d页" % self.totalPage)
self.totalPageLabel.setText(szPageCountText)
# 设置总记录数
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d条" % self.totalRecrodCount)
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText)
self.totalRecordLabel.setText(szTotalRecordText)
def update(self):
print("it is me")
# 设置表格
self.setTableView()
def change(self):
from .chaneAdminByNum import chaneAdmin
self.chanadminbynum = chaneAdmin(self.update)
self.chanadminbynum.show()
def delete(self):
from .DeleteAdminByNum import DeleteAdminByNum
self.deleteadminbynum = DeleteAdminByNum(self.update)
self.deleteadminbynum.show()
# 前一页按钮按下
def onPrevButtonClick(self):
print('*** onPrevButtonClick ')
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage -= 1
self.updateStatus()
# 后一页按钮按下
def onNextButtonClick(self):
print('*** onNextButtonClick ')
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage += 1
self.updateStatus()
# 转到页按钮按下
def onSwitchPageButtonClick(self):
# 得到输入字符串
szText = self.switchPageLineEdit.text()
#数字正则表达式
#pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
#match = pattern.match(szText)
try:
match = None
match = int(szText)
except Exception as e:
print(e)
# 判断是否为数字
if not match:
QMessageBox.information(self, "提示", "请输入数字")
return
# 是否为空
if szText == '':
QMessageBox.information(self, "提示", "请输入跳转页面")
return
#得到页数
pageIndex = int(szText)
#判断是否有指定页
if pageIndex > self.totalPage or pageIndex < 1:
QMessageBox.information(self, "提示", "没有指定的页面，请重新输入")
return
#得到查询起始行号
limitIndex = (pageIndex - 1) * self.PageRecordCount
#记录查询
self.recordQuery(limitIndex)
#设置当前页
self.currentPage = pageIndex
#刷新状态
self.updateStatus()
class StudentMainUi(QMainWindow):
def __init__(self):
super().__init__()
#self.search_layout = QtWidgets.QHBoxLayout()
self.OpenRuslt = 0
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecord = 0
# 每页数据数
self.pageRecord = 10
self.initUi()
# 初始化UI界面
def initUi(self):
# 设置窗口标题
self.setWindowTitle("欢迎使用图书查询")
# 设置窗口大小
self.resize(700, 500)
self.widget2 = QtWidgets.QWidget()
self.layout = QGridLayout()
self.Hlayout1 = QHBoxLayout()
self.Hlayout2 = QHBoxLayout()
self.Hlayout3 = QHBoxLayout()
self.Hlayout = QHBoxLayout()
# Hlayout1
# 创建一个文本框部件
self.line = QtWidgets.QLineEdit()
self.ButtonSerch = QtWidgets.QPushButton('查询')
self.combox = QtWidgets.QComboBox()
self.item = ['ISBN', 'BookName']
self.combox.addItems(self.item)
self.Hlayout1.addWidget(self.line)
self.Hlayout1.addWidget(self.ButtonSerch)
self.Hlayout1.addWidget(self.combox)
# Hlayout2初始化
self.jumpToLabel = QLabel("跳转到第")
self.pageEdit = QLineEdit()
self.pageEdit.setFixedWidth(30)
s = "/" + str(self.totalPage) + "页"
self.pageLabel = QLabel(s)
self.jumpToButton = QtWidgets.QPushButton("跳转")
self.prevButton = QtWidgets.QPushButton("前一页")
self.prevButton.setFixedWidth(60)
self.backButton = QtWidgets.QPushButton("后一页")
self.backButton.setFixedWidth(60)
Hlayout = QHBoxLayout()
Hlayout.addWidget(self.jumpToLabel)
Hlayout.addWidget(self.pageEdit)
Hlayout.addWidget(self.pageLabel)
Hlayout.addWidget(self.jumpToButton)
Hlayout.addWidget(self.prevButton)
Hlayout.addWidget(self.backButton)
widget = QtWidgets.QWidget()
widget.setLayout(Hlayout)
self.Hlayout3.addWidget(widget)
# 创建一个按钮组
self.group_box = QtWidgets.QGroupBox()
self.group_box_layout = QtWidgets.QVBoxLayout()
self.group_box.setLayout(self.group_box_layout)
# Table_View
self.tableView = QtWidgets.QTableView()
self.tableView.setFixedWidth(500)
# self.tableView.horizontalHeader().setStretchLastSection(True)
# self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.queryModel = QSqlQueryModel()
self.ButtonSerch_clicked()
self.tableView.setModel(self.queryModel)
self.queryModel.setHeaderData(0, Qt.Horizontal, "ISBN")
self.queryModel.setHeaderData(1, Qt.Horizontal, "BookName")
self.queryModel.setHeaderData(2, Qt.Horizontal, "Author")
self.queryModel.setHeaderData(3, Qt.Horizontal, "Publisher")
self.queryModel.setHeaderData(4, Qt.Horizontal, "Date")
self.queryModel.setHeaderData(5, Qt.Horizontal, "Score")
self.queryModel.setHeaderData(6, Qt.Horizontal, "Photo")
# 创建按钮组的按钮
self.ButtonCreatDb = QtWidgets.QPushButton("打开数据库")
self.ButtonCreatDb.setFixedHeight(40)
self.ButtonCreatDb.setFixedWidth(90)
self.ButtonClose = QtWidgets.QPushButton("退出")
self.ButtonClose.setFixedHeight(40)
self.ButtonClose.setFixedWidth(90)
self.StudentInfo = QtWidgets.QTextEdit(" ")
self.StudentInfo.setFixedHeight(100)
self.StudentInfo.setFixedWidth(200)
# 添加按钮到按钮组中
self.group_box_layout.addWidget(self.StudentInfo)
self.group_box_layout.addWidget(self.ButtonCreatDb)
self.group_box_layout.addWidget(self.ButtonClose)
self.Hlayout2.addWidget(self.group_box)
self.Hlayout2.addWidget(self.tableView)
self.layout.addLayout(self.Hlayout2, 1, 0)
self.layout.addLayout(self.Hlayout1, 0, 0)
self.layout.addLayout(self.Hlayout3, 2, 0)
# self.layout.addWidget(self.StudentInfo,0,0)
self.widget2.setLayout(self.layout)
# 发射信号 clicked Or Triggered
self.ButtonCreatDb.clicked.connect(self.openDb) # 打开数据库信号
self.ButtonClose.clicked.connect(self.close) # 关闭信号
self.ButtonSerch.clicked.connect(self.ButtonSerch_clicked) # 查询
self.backButton.clicked.connect(self.backButtonClicked)
self.prevButton.clicked.connect(self.prevButtonClicked)
self.jumpToButton.clicked.connect(self.jumpToButtonClicked)
self.setCentralWidget(self.widget2)
self.show()
# -----------------------------------------------槽函数----------------------------------------
"""打开数据库的槽函数"""
def openDb(self):
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('F:/pycharm项目/图书管理系统NEW/AllDataBase/book.db')
db.open()
# 显示数据库
self.model = QtSql.QSqlTableModel()
# 将数据库显示在表中
self.tableView.setModel(self.model)
self.model.setTable('BookData') # 设置数据模型的数据表
self.model.setEditStrategy(
QtSql.QSqlTableModel.OnManualSubmit) # 不允许字段更改
self.totalRecord = self.model.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
self.model.select() # 查询所有数据
# 设置表格头
self.model.setHeaderData(0, QtCore.Qt.Horizontal, 'ISBN')
self.model.setHeaderData(1, QtCore.Qt.Horizontal, 'BookName')
self.model.setHeaderData(2, QtCore.Qt.Horizontal, 'Author')
self.model.setHeaderData(3, QtCore.Qt.Horizontal, 'Publisher')
self.model.setHeaderData(4, QtCore.Qt.Horizontal, 'Date')
self.model.setHeaderData(5, QtCore.Qt.Horizontal, 'Score')
self.model.setHeaderData(6, QtCore.Qt.Horizontal, 'Photo')
self.OpenRuslt += 1
# 显示有多少数据
self.queryModel = QSqlQueryModel()
queryCondition = "select * from BookData"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
def setButtonStatus(self):
if(self.currentPage == self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(False)
if(self.currentPage == 1):
self.backButton.setEnabled(True)
self.prevButton.setEnabled(False)
if(self.currentPage < self.totalPage and self.currentPage > 1):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(True)
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("SELECT * FROM BookData")
self.totalRecord = self.queryModel.rowCount()
return
# 得到总页数
def getPageCount(self):
self.getTotalRecordCount()
# 上取整
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
print(self.totalPage)
return
def on_tableWidget_currentCellChanged(
self,
currentRow,
currentColumn,
previousRow,
previousColumn):
"""
当前单元格改变
"""
pass
def recordQuery(self, index):
"""
查找
"""
self.queryModel = QSqlQueryModel()
self.tableView.setModel(self.queryModel)
conditionChoice = self.combox.currentText()
if (conditionChoice == "ISBN"):
conditionChoice = 'ISBN'
elif (conditionChoice == "BookName"):
conditionChoice = 'BookName'
if (self.line.text() == ""):
queryCondition = "select * from BookData"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select * from BookData ORDER BY %s limit %d,%d " %
(conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
# 得到模糊查询条件
temp = self.line.text()
s = '%'
for i in range(0, len(temp)):
s = s + temp[i] + "%"
queryCondition = (
"SELECT * FROM BookData WHERE %s LIKE '%s' ORDER BY %s " %
(conditionChoice, s, conditionChoice))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"SELECT * FROM BookData WHERE %s LIKE '%s' ORDER BY %s LIMIT %d,%d " %
(conditionChoice, s, conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
def ButtonSerch_clicked(self):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向前翻页
def prevButtonClicked(self):
self.currentPage -= 1
if (self.currentPage <= 1):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向后翻页
def backButtonClicked(self):
self.currentPage += 1
if (self.currentPage >= int(self.totalPage)):
self.currentPage = int(self.totalPage)
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击跳转
def jumpToButtonClicked(self):
if (self.pageEdit.text().isdigit()):
self.currentPage = int(self.pageEdit.text())
if (self.currentPage > self.totalPage):
self.currentPage = self.totalPage
if (self.currentPage <= 1):
self.currentPage = 1
else:
self.currentPage = 1
index = (self.currentPage - 1) * self.pageRecord
self.pageEdit.setText(str(self.currentPage))
self.recordQuery(index)
return
class DataGrid(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("分页查询例子")
self.resize(750,300)
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
# 总数页文本
self.totalPageLabel = None
# 当前页文本
self.currentPageLabel = None
# 转到页输入框
self.switchPageLineEdit = None
# 前一页按钮
self.prevButton = None
# 后一页按钮
self.nextButton = None
# 转到页按钮
self.switchPageButton = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 5
self.initUI()
def initUI(self):
# 创建窗口
self.createWindow()
# 设置表格
self.setTableView()
# 信号槽连接
self.prevButton.clicked.connect(self.onPrevButtonClick )
self.nextButton.clicked.connect(self.onNextButtonClick )
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick )
# 创建数据库
# 创建窗口
def createWindow(self):
# 操作布局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一页")
self.nextButton = QPushButton("后一页")
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("转到第")
page = QLabel("页")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget( QSplitter())
# 状态布局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget( QSplitter() )
statusLayout.addWidget(self.totalRecordLabel)
# 设置表格属性
self.tableView = QTableView()
# 表格宽度的自适应调整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 创建界面
mainLayout = QVBoxLayout(self);
mainLayout.addLayout(operatorLayout);
mainLayout.addWidget(self.tableView);
mainLayout.addLayout(statusLayout);
self.setLayout(mainLayout)
# 设置表格
def setTableView(self):
print('*** step2 SetTableView' )
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount) )
print('totalPage=' + str(self.totalPage) )
# 设置表格表头
self.queryModel.setHeaderData(0,Qt.Horizontal,"编号")
self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名")
self.queryModel.setHeaderData(2,Qt.Horizontal,"性别")
self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄")
self.queryModel.setHeaderData(4,Qt.Horizontal,"院系")
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from student")
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount) )
return rowCount
# 得到页数
def getPageCount(self):
if self.totalRecrodCount % self.PageRecordCount == 0 :
return (self.totalRecrodCount / self.PageRecordCount )
else :
return (self.totalRecrodCount / self.PageRecordCount + 1)
# 记录查询
def recordQuery(self, limitIndex ):
szQuery = ("select * from student limit %d,%d" % ( limitIndex , self.PageRecordCount ) )
print('query sql=' + szQuery )
self.queryModel.setQuery(szQuery)
# 刷新状态
def updateStatus(self):
szCurrentText = ("当前第%d页" % self.currentPage )
self.currentPageLabel.setText( szCurrentText )
#设置按钮是否可用
if self.currentPage == 1 :
self.prevButton.setEnabled( False )
self.nextButton.setEnabled( True )
elif self.currentPage == self.totalPage :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( False )
else :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( True )
# 设置总数页文本
def setTotalPageLabel(self):
szPageCountText = ("总共%d页" % self.totalPage )
self.totalPageLabel.setText(szPageCountText)
# 设置总总记录数
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d条" % self.totalRecrodCount )
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText )
self.totalRecordLabel.setText(szTotalRecordText)
# 前一页按钮按下
def onPrevButtonClick(self):
print('*** onPrevButtonClick ');
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage -= 1
self.updateStatus()
# 后一页按钮按下
def onNextButtonClick(self):
print('*** onNextButtonClick ');
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage += 1
self.updateStatus()
# 转到页按钮按下
def onSwitchPageButtonClick(self):
# 得到输入字符串
szText = self.switchPageLineEdit.text()
#数字正则表达式
pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
match = pattern.match(szText)
# 判断是否为数字
if not match :
QMessageBox.information(self, "提示", "请输入数字" )
return
# 是否为空
if szText == '' :
QMessageBox.information(self, "提示" , "请输入跳转页面" )
return
#得到页数
pageIndex = int(szText)
#判断是否有指定页
if pageIndex > self.totalPage or pageIndex < 1 :
QMessageBox.information(self, "提示", "没有指定的页面，请重新输入" )
return
#得到查询起始行号
limitIndex = (pageIndex-1) * self.PageRecordCount
#记录查询
self.recordQuery(limitIndex);
#设置当前页
self.currentPage = pageIndex
#刷新状态
self.updateStatus();
class Window(QMainWindow, Ui_MainWindow):
def __init__(self):
super().__init__()
self.setupUi(self)
self.model = QSqlQueryModel()
self.connectToSql()
# Обработка действий по меню
# нажата кнопка меню "Справочники -> Подразделения"
self.unit_action.triggered.connect(self.unit_dialog)
# нажата кнопка меню "Справочники -> Сотрудники"
self.user_action.triggered.connect(self.user_dialog)
# нажата кнопка меню "Справочники -> Сервисные службы"
self.services_action.triggered.connect(self.services_dialog)
# Работа с основной формой
# Нажата кнопка "Добавить строку" на форме
self.btProblemAdd.clicked.connect(self.bt_add)
# Нажата кнопка "Обновить строку" на форме
self.btProblemUpdate.clicked.connect(self.bt_upd)
# Нажата кнопка "Удалить строку" на форме
self.btProblemDel.clicked.connect(self.bt_del)
# Найти заявку
self.btProblemFind.clicked.connect(self.findText)
# Сбросить фильтр
self.btProblemReset.clicked.connect(self.reset)
def connectToSql(self):
"""
Открываем базу и создаем запрос и соединяем модель с таблицей
"""
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id""")
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeToContents)
self.model.setHeaderData(0, Qt.Horizontal, '№ заявки')
self.model.setHeaderData(1, Qt.Horizontal, 'Дата')
self.model.setHeaderData(2, Qt.Horizontal, 'Служба')
self.model.setHeaderData(3, Qt.Horizontal, 'Текст заявки')
self.model.setHeaderData(4, Qt.Horizontal, 'ФИО заявителя')
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
def bt_add(self):
"""
Вызывается по нажатию кнопки "Добавить строку"
"""
dialog_add = Ed_win(self)
# Устанавливаем пустые значения в поля на форме
dialog_add.edit_text.setText = ''
dialog_add.cb_ser_name.setCurrentIndex(-1)
dialog_add.cb_fio.setCurrentIndex(-1)
# открываем окно добавления
dialog_add.show()
# Если нажата кнопка ОК на форме добавления
if dialog_add.exec() == QDialog.Accepted:
ser = dialog_add.cb_ser_name.currentText()
user = dialog_add.cb_fio.currentText()
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry1 = QSqlQuery(self.db)
qry1.prepare("""SELECT s.id FROM services s
where s.name =:ser""")
qry1.bindValue(":ser", ser)
qry1.exec()
qry1.next()
servId = qry1.value(0)
qry1.prepare("""SELECT u.id FROM User u
where u.FIO =:user""")
qry1.bindValue(":user", user)
qry1.exec()
qry1.next()
userId = qry1.value(0)
qry1.prepare(
"""INSERT INTO def_message(idServices, text,idUser)
VALUES(:idService, :text,:idUser)""")
qry1.bindValue(":idService", servId)
qry1.bindValue(":text", dialog_add.edit_text.text())
qry1.bindValue(":idUser", userId)
qry1.exec()
self.qry = QSqlQuery(self.db)
self.qry.prepare("""SELECT d.id, dat, s.name, d.text, u.FIO
FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id""")
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error',
self.model.lastError().text())
def bt_upd(self):
"""
Вызывается по нажатию кнопки "Изменить строку"
"""
dialog_add = Ed_win(self)
# определяем индекс строки в таблице
index = self.tableView.currentIndex().row()
print(index)
if index == -1:
QMessageBox.information(self, 'Ошибка', 'Строка не выбрана')
else:
# определяем id строки в таблице в базе данных
nom = str(
self.model.data(self.model.index(index, 0), Qt.DisplayRole))
# устанавливаем текст заявки в поле edit_text
dialog_add.edit_text.setText(
str(self.model.data(self.model.index(index, 3),
Qt.DisplayRole)))
# Данные по сервисной службе
index_serv = dialog_add.cb_ser_name.findText(
str(self.model.data(self.model.index(index, 2),
Qt.DisplayRole)))
if index_serv > -1:
dialog_add.cb_ser_name.setCurrentIndex(index_serv)
else:
dialog_add.cb_ser_name.setCurrentIndex(-1)
# Данные по сотруднику, который подал заявку
index_fio = dialog_add.cb_fio.findText(
str(self.model.data(self.model.index(index, 4),
Qt.DisplayRole)))
if index_fio > -1:
dialog_add.cb_fio.setCurrentIndex(index_fio)
else:
dialog_add.cb_fio.setCurrentIndex(-1)
# ОТкрываем окно с данными для редактирования
dialog_add.show()
# если нажата кнопка ОК в окне для редактирования строки
if dialog_add.exec() == QDialog.Accepted:
ser = dialog_add.cb_ser_name.currentText()
user = dialog_add.cb_fio.currentText()
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry1 = QSqlQuery(self.db)
qry1.prepare("""SELECT s.id FROM services s
where s.name =:ser""")
qry1.bindValue(":ser", ser)
qry1.exec()
qry1.next()
servId = qry1.value(0)
qry1.prepare("""SELECT u.id FROM User u
where u.FIO =:user""")
qry1.bindValue(":user", user)
qry1.exec()
qry1.next()
userId = qry1.value(0)
qry1.prepare(
"""UPDATE def_message SET idServices=:servId, text=:txt,idUser=:userId WHERE id=:id"""
)
qry1.bindValue(":servId", servId)
qry1.bindValue(":txt", dialog_add.edit_text.text())
qry1.bindValue(":userId", userId)
qry1.bindValue(":id", nom)
qry1.exec()
# снова делаем выборку данных после проведения обновления данных
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id""")
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error',
self.model.lastError().text())
def bt_del(self):
"""
Вызывается по нажатию кнопки "Удалить строку"
"""
quit_msg = "Подтвердите удаление записи"
reply = QMessageBox.question(self, 'Confirm', quit_msg,
QMessageBox.Yes, QMessageBox.No)
if reply == QMessageBox.Yes:
index = self.tableView.currentIndex().row()
nom = str(
self.model.data(self.model.index(index, 0), Qt.DisplayRole))
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry1 = QSqlQuery(self.db)
qry1.prepare("""DELETE FROM def_message
where id =:id""")
qry1.bindValue(":id", nom)
qry1.exec()
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id"""
)
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error',
self.model.lastError().text())
def findText(self):
"""
Вызывается по кнопке "Найти заявку"
"""
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id where d.text Like :txt """)
self.qry.bindValue(":txt", '%' + self.EditFind.text() + '%')
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
def reset(self):
"""
Вызывается по кнопке "Сбросить фильтр"
"""
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id """)
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
def unit_dialog(self):
"""
Функция выполняется при нажатии кнопки меню "Справочники -> Подразделения"
:return: Открывает окно с таблицей информации по подразделениям (Unit_win)
"""
dialog_unit = Unit_win(self)
dialog_unit.show()
# Скрываем основное окно программы
wnd.hide()
def user_dialog(self):
"""
Функция выполняется при нажатии кнопки меню "Справочники -> Сотрудники"
:return: Открывает окно с таблицей информации по отрудникам (User_win)
"""
dialog_user = User_win(self)
dialog_user.show()
# Скрываем основное окно программы
wnd.hide()
def services_dialog(self):
"""
Функция выполняется при нажатии кнопки меню "Справочники -> Сервисные службы"
:return: Открывает окно с таблицей информации по сервисным службам (Services_win)
"""
dialog = Services_win(self)
dialog.show()
# Скрываем основное окно программы
wnd.hide()
class DataGrid(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("分頁查詢範例")
self.resize(750, 300)
# 查詢模型
self.queryModel = None
# 資料表
self.tableView = None
# 總頁數
self.totalPageLabel = None
# 目前頁
self.currentPageLabel = None
# 轉到頁輸入框
self.switchPageLineEdit = None
# 前一頁按鈕
self.prevButton = None
# 後一頁按鈕
self.nextButton = None
# 轉到頁按鈕
self.switchPageButton = None
# 目前頁
self.currentPage = 0
# 總頁數
self.totalPage = 0
# 總記錄數
self.totalRecrodCount = 0
# 每頁顯示記錄數
self.PageRecordCount = 5
self.initUI()
def initUI(self):
# 建立視窗
self.createWindow()
# 設定表格
self.setTableView()
# 訊號/槽連接
self.prevButton.clicked.connect(self.onPrevButtonClick)
self.nextButton.clicked.connect(self.onNextButtonClick)
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick)
# 建立視窗
def createWindow(self):
# 操作佈局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一頁")
self.nextButton = QPushButton("後一頁")
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("轉到第")
page = QLabel("頁")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget(QSplitter())
# 狀態佈局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget(QSplitter())
statusLayout.addWidget(self.totalRecordLabel)
# 設定表格屬性
self.tableView = QTableView()
# 表格寬度的自我調整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
# 建立介面
mainLayout = QVBoxLayout(self)
mainLayout.addLayout(operatorLayout)
mainLayout.addWidget(self.tableView)
mainLayout.addLayout(statusLayout)
self.setLayout(mainLayout)
# 設定表格
def setTableView(self):
print('*** step2 SetTableView')
# 宣告查詢模型
self.queryModel = QSqlQueryModel(self)
# 設定目前頁
self.currentPage = 1
# 取得總記錄數
self.totalRecrodCount = self.getTotalRecordCount()
# 取得總頁數
self.totalPage = self.getPageCount()
# 刷新狀態
self.updateStatus()
# 設定總頁數標籤
self.setTotalPageLabel()
# 設定總記錄數標籤
self.setTotalRecordLabel()
# 記錄查詢
self.recordQuery(0)
# 設定模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 設定表格表頭
self.queryModel.setHeaderData(0, Qt.Horizontal, "編號")
self.queryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.queryModel.setHeaderData(2, Qt.Horizontal, "性別")
self.queryModel.setHeaderData(3, Qt.Horizontal, "年齡")
self.queryModel.setHeaderData(4, Qt.Horizontal, "院系")
# 取得記錄數
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from student")
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount))
return rowCount
# 取得頁數
def getPageCount(self):
if self.totalRecrodCount % self.PageRecordCount == 0:
return (self.totalRecrodCount / self.PageRecordCount)
else:
return (self.totalRecrodCount / self.PageRecordCount + 1)
# 記錄查詢
def recordQuery(self, limitIndex):
szQuery = ("select * from student limit %d,%d" %
(limitIndex, self.PageRecordCount))
print('query sql=' + szQuery)
self.queryModel.setQuery(szQuery)
# 刷新狀態
def updateStatus(self):
szCurrentText = ("目前第%d頁" % self.currentPage)
self.currentPageLabel.setText(szCurrentText)
#設定按鈕是否可用
if self.currentPage == 1:
self.prevButton.setEnabled(False)
self.nextButton.setEnabled(True)
elif self.currentPage == self.totalPage:
self.prevButton.setEnabled(True)
self.nextButton.setEnabled(False)
else:
self.prevButton.setEnabled(True)
self.nextButton.setEnabled(True)
# 設取總數頁標籤
def setTotalPageLabel(self):
szPageCountText = ("總共%d頁" % self.totalPage)
self.totalPageLabel.setText(szPageCountText)
# 設定總記錄數標籤
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d筆" % self.totalRecrodCount)
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText)
self.totalRecordLabel.setText(szTotalRecordText)
# 按下前一頁按鈕
def onPrevButtonClick(self):
print('*** onPrevButtonClick ')
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage -= 1
self.updateStatus()
# 按下後一頁按鈕
def onNextButtonClick(self):
print('*** onNextButtonClick ')
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery(limitIndex)
self.currentPage += 1
self.updateStatus()
# 按下轉到頁按鈕
def onSwitchPageButtonClick(self):
# 取得輸入字串
szText = self.switchPageLineEdit.text()
# 數字規則運算式
pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
match = pattern.match(szText)
# 判斷是否為數字
if not match:
QMessageBox.information(self, "提示", "請輸入數字")
return
# 是否為空
if szText == '':
QMessageBox.information(self, "提示", "請輸入跳越頁面")
return
# 取得頁數
pageIndex = int(szText)
# 判斷是否有指定頁
if pageIndex > self.totalPage or pageIndex < 1:
QMessageBox.information(self, "提示", "沒有指定的頁面，請重新輸入")
return
# 取得查詢起始列號
limitIndex = (pageIndex - 1) * self.PageRecordCount
# 記錄查詢
self.recordQuery(limitIndex)
# 設定目前頁
self.currentPage = pageIndex
# 刷新狀態
self.updateStatus()
class RecordsViewer(QDialog):
def __init__(self):
super(RecordsViewer, self).__init__()
self.resize(800, 500)
self.setWindowTitle('myOCR——我的识别历史')
self.setWindowIcon(QIcon('./images/icon.png'))
self.queryModel = None
self.tableView = None
self.currentPage = 0
self.totalPage = 0
self.totalRecord = 0
self.pageRecord = 10
self.initUI()
def initUI(self):
self.vbox = QVBoxLayout()
self.h1box = QHBoxLayout()
self.h2box = QHBoxLayout()
self.searchEdit = QLineEdit()
self.searchEdit.setFixedHeight(32)
self.searchEdit.setFont(QFont("苏新诗柳楷繁", 15))
self.searchButton = QPushButton("查询")
self.searchButton.setFixedHeight(32)
self.searchButton.setFont(QFont("苏新诗柳楷繁", 15))
self.condisionComboBox = QComboBox()
searchCondision = ['按识别编号查询', '按识别时间查询', '按识别类型查询', '按识别文字查询']
self.condisionComboBox.setFixedHeight(32)
self.condisionComboBox.setFont(QFont("苏新诗柳楷繁", 15))
self.condisionComboBox.addItems(searchCondision)
self.h1box.addWidget(self.searchEdit)
self.h1box.addWidget(self.condisionComboBox)
self.h1box.addWidget(self.searchButton)
self.jumpToLabel = QLabel(self)
self.jumpToLabel.setText("跳转到第")
self.jumpToLabel.setFont(QFont("苏新诗柳楷繁", 12))
self.jumpToLabel.setFixedWidth(90)
self.pageEdit = QLineEdit()
self.pageEdit.setFixedWidth(30)
self.pageEdit.setFont(QFont("苏新诗柳楷繁", 12))
s = "/" + str(self.totalPage) + "页"
self.pageLabel = QLabel(s)
self.pageLabel.setFont(QFont("苏新诗柳楷繁", 12))
self.pageLabel.setFixedWidth(40)
self.jumpToButton = QPushButton(self)
self.jumpToButton.setText("跳转")
self.jumpToButton.setFont(QFont("苏新诗柳楷繁", 12))
self.jumpToButton.setFixedHeight(30)
self.jumpToButton.setFixedWidth(60)
self.prevButton = QPushButton("前一页")
self.prevButton.setFont(QFont("苏新诗柳楷繁", 12))
self.prevButton.setFixedHeight(30)
self.prevButton.setFixedWidth(80)
self.backButton = QPushButton("后一页")
self.backButton.setFont(QFont("苏新诗柳楷繁", 12))
self.backButton.setFixedHeight(30)
self.backButton.setFixedWidth(80)
self.detailbutton = QPushButton(self)
self.detailbutton.setText("详细信息")
self.detailbutton.setFixedWidth(90)
self.detailbutton.setFont(QFont("苏新诗柳楷繁", 12))
self.detailbutton.clicked.connect(self.detailInfo)
self.hbox = QHBoxLayout()
self.hbox.addWidget(self.jumpToLabel)
self.hbox.addWidget(self.pageEdit)
self.hbox.addWidget(self.pageLabel)
self.hbox.addWidget(self.jumpToButton)
self.hbox.addStretch(1)
self.hbox.addWidget(self.detailbutton)
self.hbox.addStretch(1)
self.hbox.addWidget(self.prevButton)
self.hbox.addWidget(self.backButton)
widget = QWidget()
widget.setLayout(self.hbox)
widget.setFixedWidth(600)
self.h2box.addWidget(widget)
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName('./db/myOCR.db')
self.db.open()
self.tableView = QTableView()
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tableView.setFont(QFont("苏新诗柳楷繁", 12))
self.tableView.horizontalHeader().setFont(QFont("苏新诗柳楷繁", 12))
self.queryModel = QSqlQueryModel()
self.searchButtonClicked()
self.tableView.setModel(self.queryModel)
self.queryModel.setHeaderData(0, Qt.Horizontal, "识别编号")
self.queryModel.setHeaderData(1, Qt.Horizontal, "识别时间")
self.queryModel.setHeaderData(2, Qt.Horizontal, "识别类型")
self.queryModel.setHeaderData(3, Qt.Horizontal, "识别文字")
self.vbox.addLayout(self.h1box)
self.vbox.addWidget(self.tableView)
self.vbox.addLayout(self.h2box)
self.setLayout(self.vbox)
self.searchButton.clicked.connect(self.searchButtonClicked)
self.prevButton.clicked.connect(self.prevButtonClicked)
self.backButton.clicked.connect(self.backButtonClicked)
self.jumpToButton.clicked.connect(self.jumpToButtonClicked)
self.searchEdit.returnPressed.connect(self.searchButtonClicked)
def setButtonStatus(self):
if (self.currentPage == self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(False)
if (self.currentPage == 1):
self.backButton.setEnabled(True)
self.prevButton.setEnabled(False)
if (self.currentPage < self.totalPage and self.currentPage > 1):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(True)
def getTotalRecordCount(self):
self.queryModel.setQuery(
"select RecordId,RecordTime,RecordType,RecordText from records")
self.totalRecord = self.queryModel.rowCount()
return
def getPageCount(self):
self.getTotalRecordCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
return
def recordQuery(self, index):
conditionChoice = self.condisionComboBox.currentText()
if (conditionChoice == "按识别编号查询"):
conditionChoice = 'RecordId'
elif (conditionChoice == "按识别时间查询"):
conditionChoice = 'RecordTime'
elif (conditionChoice == "按识别类型查询"):
conditionChoice = 'RecordType'
else:
conditionChoice = 'RecordText'
if (self.searchEdit.text() == ""):
queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d "
% (conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
temp = self.searchEdit.text()
s = '%'
for i in range(0, len(temp)):
s = s + temp[i] + "%"
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc"
% (conditionChoice, s, conditionChoice))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
if (self.totalRecord == 0):
print(
QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes,
QMessageBox.Yes))
queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC"
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d "
% (conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
self.totalPage = int(
(self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
queryCondition = (
"select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc limit %d,%d "
% (conditionChoice, s, conditionChoice, index, self.pageRecord))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
def searchButtonClicked(self):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
def prevButtonClicked(self):
self.currentPage -= 1
if (self.currentPage <= 1):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
def backButtonClicked(self):
self.currentPage += 1
if (self.currentPage >= int(self.totalPage)):
self.currentPage = int(self.totalPage)
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
def jumpToButtonClicked(self):
if (self.pageEdit.text().isdigit()):
self.currentPage = int(self.pageEdit.text())
if (self.currentPage > self.totalPage):
self.currentPage = self.totalPage
if (self.currentPage <= 1):
self.currentPage = 1
else:
self.currentPage = 1
index = (self.currentPage - 1) * self.pageRecord
self.pageEdit.setText(str(self.currentPage))
self.recordQuery(index)
return
def detailInfo(self):
index_ = self.tableView.currentIndex().row()
if (index_ == -1):
print(
QMessageBox.warning(self, "警告", "您没有选中任何记录", QMessageBox.Yes,
QMessageBox.Yes))
return
else:
str = self.queryModel.data(self.queryModel.index(index_, 0))
type = self.queryModel.data(self.queryModel.index(index_, 2))
if (type == '身份证识别'):
self.recordDetail_id(str)
else:
self.recordDetail(str)
def recordDetail(self, RecordId):
recorddetaildialog = RecordDetailDialog(RecordId)
recorddetaildialog.show()
recorddetaildialog.exec_()
def recordDetail_id(self, RecordId):
recorddetail_id = RecordDetailDialog_id(RecordId)
recorddetail_id.show()
recorddetail_id.exec_()
def dbToTableView(self,commandSQL):
try:
QApplication.processEvents()
if QSqlDatabase.contains("qt_sql_default_connection"):
db = QSqlDatabase.database("qt_sql_default_connection")
else:
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(self.dbPath)
db.open()
projectModel = QSqlQueryModel()
projectModel.setQuery(commandSQL, db)
projectModel.setHeaderData(0, Qt.Horizontal, 'پلاک')
projectModel.setHeaderData(1, Qt.Horizontal, 'متقاضی')
projectModel.setHeaderData(2, Qt.Horizontal, 'نوع انجام کار')
projectModel.setHeaderData(3, Qt.Horizontal, 'تاریخ بازدید')
projectModel.setHeaderData(4, Qt.Horizontal, 'ساعت بازدید')
projectModel.setHeaderData(5, Qt.Horizontal, 'نقشه بردار')
projectModel.setHeaderData(6, Qt.Horizontal, 'نماینده')
projectModel.setHeaderData(7, Qt.Horizontal, 'تاریخ ثبت')
projectModel.setHeaderData(8, Qt.Horizontal, 'توضیحات')
self.ui.tableView_result.setModel(projectModel)
self.rowCount = projectModel.rowCount()
self.tableResult = projectModel
db.close()
QApplication.processEvents()
except:
self.errorM('مشکل در ارتباط با دیتابیس\n {}')
def set_model(self):
if user_id is None:
return
if not self.db.connect().open():
return print('Нет подключения к бд')
sql = '{CALL dbo.operation_info_for_current_user(' + str(user_id) + ')}'
query = QSqlQuery(sql, self.db.connect())
model = QSqlQueryModel()
model.setQuery(query)
model.setHeaderData(0, Qt.Horizontal, "Пользователь")
model.setHeaderData(1, Qt.Horizontal, "IP")
model.setHeaderData(2, Qt.Horizontal, "Дата операции")
model.setHeaderData(3, Qt.Horizontal, "В ожидании")
model.setHeaderData(4, Qt.Horizontal, "В работе")
model.setHeaderData(5, Qt.Horizontal, "Выполнено")
model.setHeaderData(6, Qt.Horizontal, "Выполнено с ошибкой")
self.ui.tableView.setModel(model)
self.ui.tableView.show()
class OrderController(QObject):
def __init__(self, form, orderTable, addButton, editButton, deleteButton, dbase, mainwindow):
super().__init__()
self.form = form
self.orderTable = orderTable
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.mainwindow = mainwindow
form.currentRecordChanged.connect(self.recordChanged)
form.recordInserted.connect(lambda: self.recordChanged(None))
form.recordDeleted.connect(lambda: self.recordChanged(None))
if form.only_select:
self.addButton.setEnabled(False)
self.editButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def recordChanged(self, record):
#print("record changed", record)
if record is None:
for button in (self.addButton, self.editButton, self.deleteButton):
button.setEnabled(False)
self.orderTable.setModel(None)
self._hiddingHack(True)
else:
if not self.form.only_select:
self.addButton.setEnabled(True)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, detail.article as article, detail.name as name, order_detail.quantity as qnt, \
detail.price as sole_price, detail.price*order_detail.quantity as total_price\
FROM order_detail INNER JOIN detail \
ON order_detail.detail_id = detail.id \
WHERE order_detail.order_id={} ORDER BY article".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Артикул")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(3, Qt.Horizontal, "Количество")
self.detailModel.setHeaderData(4, Qt.Horizontal, "Цена за штуку")
self.detailModel.setHeaderData(5, Qt.Horizontal, "Суммарная цена")
self.orderTable.setModel(self.detailModel)
self.orderTable.hideColumn(0)
self.orderTable.resizeColumnsToContents()
self.orderTable.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self._hiddingHack(False)
def _hiddingHack(self, val):
ui = self.mainwindow.ui
if val is True:
ui.client_hack.setCurrentWidget(ui.client_hide_page)
ui.shop_hack.setCurrentWidget(ui.shop_hide_page)
ui.emp_hack.setCurrentWidget(ui.emp_hide_page)
else:
ui.client_hack.setCurrentWidget(ui.client_ok_page)
ui.shop_hack.setCurrentWidget(ui.shop_ok_page)
ui.emp_hack.setCurrentWidget(ui.emp_ok_page)
def tableSelectionChanged(self, cur, prev):
if self.form.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
order = self.form.currentRecord()
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM order_detail \
WHERE order_id={}))".format(order.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар к заказу: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1)
if not ok: return
detail_id = details[choice]
order_id = order.value("id")
query = QSqlQuery("INSERT INTO order_detail (order_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(order_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.form.update()
def editButtonClicked(self):
detail = self.detailModel.record(self.orderTable.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
order = self.form.currentRecord()
if qnt > 0:
query = QSqlQuery("UPDATE order_detail SET quantity={} \
WHERE order_id={} AND detail_id={}".format(qnt,
order.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def deleteButtonClicked(self):
if not self.orderTable.currentIndex().isValid(): return
detail = self.detailModel.record(self.orderTable.currentIndex().row())
order = self.form.currentRecord()
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def selectRow(self, row):
self.form.selectRow(row)
def update(self):
self.form.update()
def show_demo(self):
def test(x):
print(len(x))
# Создаем модель
sqm = QSqlQueryModel(parent=self)
sqm.setQuery(
'SELECT id, lastName, firstName, patrName, birthDate, sex, notes '
'FROM Client WHERE deleted = 0 AND deathDate IS NULL '
'LIMIT 20')
# Задаем заголовки для столбцов модели
sqm.setHeaderData(1, Qt.Horizontal, 'Фамилия')
sqm.setHeaderData(2, Qt.Horizontal, 'Имя')
sqm.setHeaderData(3, Qt.Horizontal, 'Отчество')
sqm.setHeaderData(4, Qt.Horizontal, 'Дата рождения')
sqm.setHeaderData(5, Qt.Horizontal, 'Пол')
sqm.setHeaderData(6, Qt.Horizontal, 'Примечание')
# Задаем для таблицы только что созданную модель
self.clients_table.setModel(sqm)
self.clients_table.hideColumn(0)
self.clients_table.resizeColumnsToContents()
self.clients_table.horizontalHeader().setStretchLastSection(True)
self.clients_table.horizontalHeader().setHighlightSections(False)
self.clients_table.verticalHeader().hide()
self.clients_table.clearSelection()
self.lbl_suitable_customers_count.setText(str(sqm.rowCount()))
self.lbl_selected_customers_count.setText('0')
self.clients_table.selectionModel().selectionChanged.connect(
self.show_selected_customers_count)
class DatabaseSearchWidget(QWidget, Ui_DatabaseSearchWidget):
# Signal to be emitted when user wants item to be added
signal_add_item = QtCore.pyqtSignal(int)
# Signal to be emitted when user selection changes
signal_current_selection = QtCore.pyqtSignal(int)
def __init__(self, queries, headers, parent=None):
super(DatabaseSearchWidget, self).__init__(parent)
self.setupUi(self)
# Setup database connection
self.database = pyqt_database_connection()
self.database.open()
self.databaseModel = QSqlQueryModel(self)
self.dataView_search_results.setModel(self.databaseModel)
# Store bound queries for usage
self.queries = queries
self.combo_search_options.addItems(sorted(queries.keys()))
# Connect signals
self.dataView_search_results.selectionModel().selectionChanged.connect(
self.slot_emit_selection_changed)
self.dataView_search_results.doubleClicked.connect(
self.slot_emit_add_item)
self.pushButton_search.clicked.connect(self.update_query)
self.combo_search_options.currentIndexChanged.connect(
self.update_query)
# Setup header
for element in headers:
self.databaseModel.setHeaderData(0, QtCore.Qt.Horizontal, element)
self.installEventFilter(self)
@QtCore.pyqtSlot()
def update_query(self):
""" Update the query with the search term
Returns
-------
None
"""
LOGGER.debug("Updating search query..")
query = self.queries[self.combo_search_options.currentText()]
# Todo: Slow query setting freezes ui
self.databaseModel.setQuery(
query.format(input=self.lineEdit_search_input.text().strip()))
LOGGER.debug(str(self.databaseModel.query().executedQuery()))
LOGGER.debug("Search complete.")
def selected_id(self):
""" Return the id of the currently selected item
Returns
-------
int
"""
selection = self.dataView_search_results.get_selected_rows()
if len(selection) != 1:
return -1
else:
return int(
self.databaseModel.data(
self.databaseModel.index(selection[0], 0)))
def eventFilter(self, source, event):
if event.type() == QtCore.QEvent.KeyPress and event.key() in (
QtCore.Qt.Key_Return, QtCore.Qt.Key_Enter):
self.update_query()
return True
return False
@QtCore.pyqtSlot()
def slot_emit_selection_changed(self):
""" Emit database id of the currently selected item
Returns
-------
None
"""
self.signal_current_selection.emit(self.selected_id())
@QtCore.pyqtSlot()
def slot_emit_add_item(self):
""" Emit database id of the currently selected item
Returns
-------
None
"""
self.signal_add_item.emit(self.selected_id())
# Устанавливаем соединение с базой данных
conn = QSqlDatabase.addDatabase('QSQLITE')
# Определяем путь до базы данных
conn.setDatabaseName('test.sqlite3')
# Открываем базу данных
conn.open()
T_WIND = QTableView()
T_WIND.setWindowTitle("Виджет-таблица на основе модели-запроса")
# Создаем модель
T_QUERY = QSqlQueryModel(parent=T_WIND)
T_QUERY.setQuery("SELECT * FROM vendors ORDER BY name")
# Задаем заголовки для столбцов модели
T_QUERY.setHeaderData(1, QtCore.Qt.Horizontal, 'Название')
T_QUERY.setHeaderData(2, QtCore.Qt.Horizontal, 'Телефон')
T_QUERY.setHeaderData(3, QtCore.Qt.Horizontal, 'Адрес')
# Задаем для таблицы только что созданную модель
T_WIND.setModel(T_QUERY)
# Скрываем первый столбец, в котором выводится идентификатор
T_WIND.hideColumn(0)
T_WIND.setColumnWidth(1, 150)
T_WIND.setColumnWidth(2, 100)
T_WIND.setColumnWidth(3, 200)
T_WIND.resize(470, 130)
T_WIND.show()
sys.exit(APP.exec_())
