python3+PyQt5使用数据库窗口视图
作者:basisworker 发布时间:2023-03-08 10:44:26
标签:python3,PyQt5,数据库,视图
能够为数据库数据提供的最简单的用户界面之一就是窗体,窗体可以一次性呈现出来自同一记录的各个域。本文通过python3+pyqt5改写实现了python Qt gui 快速变成15章的例子。
#!/usr/bin/env python3
import os
import sys
from PyQt5.QtCore import (QDate, QDateTime, QFile, QVariant, Qt)
from PyQt5.QtWidgets import (QApplication, QDataWidgetMapper,QComboBox,
QDateTimeEdit, QDialog, QGridLayout, QHBoxLayout, QLabel,
QLineEdit, QMessageBox, QPushButton, QVBoxLayout)
from PyQt5.QtGui import QIcon,QPixmap,QCursor
from PyQt5.QtSql import (QSqlDatabase, QSqlQuery, QSqlRelation,
QSqlRelationalDelegate, QSqlRelationalTableModel)
import qrc_resources
MAC = True
try:
from PyQt5.QtGui import qt_mac_set_native_menubar
except ImportError:
MAC = False
ID, CALLER, STARTTIME, ENDTIME, TOPIC, OUTCOMEID = range(6)
DATETIME_FORMAT = "yyyy-MM-dd hh:mm"
def createFakeData():
import random
print("Dropping tables...")
query = QSqlQuery()
query.exec_("DROP TABLE calls")
query.exec_("DROP TABLE outcomes")
QApplication.processEvents()
print("Creating tables...")
query.exec_("""CREATE TABLE outcomes (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
name VARCHAR(40) NOT NULL)""")
query.exec_("""CREATE TABLE calls (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
caller VARCHAR(40) NOT NULL,
starttime DATETIME NOT NULL,
endtime DATETIME NOT NULL,
topic VARCHAR(80) NOT NULL,
outcomeid INTEGER NOT NULL,
FOREIGN KEY (outcomeid) REFERENCES outcomes)""")
QApplication.processEvents()
print("Populating tables...")
for name in ("Resolved", "Unresolved", "Calling back", "Escalate",
"Wrong number"):
query.exec_("INSERT INTO outcomes (name) VALUES ('{0}')".format(
name))
topics = ("Complaint", "Information request", "Off topic",
"Information supplied", "Complaint", "Complaint")
now = QDateTime.currentDateTime()
query.prepare("INSERT INTO calls (caller, starttime, endtime, "
"topic, outcomeid) VALUES (:caller, :starttime, "
":endtime, :topic, :outcomeid)")
for name in ('Joshan Cockerall', 'Ammanie Ingham',
'Diarmuid Bettington', 'Juliana Bannister',
'Oakley-Jay Buxton', 'Reilley Collinge',
'Ellis-James Mcgehee', 'Jazmin Lawton',
'Lily-Grace Smythe', 'Coskun Lant', 'Lauran Lanham',
'Millar Poindexter', 'Naqeeb Neild', 'Maxlee Stoddart',
'Rebia Luscombe', 'Briana Christine', 'Charli Pease',
'Deena Mais', 'Havia Huffman', 'Ethan Davie',
'Thomas-Jack Silver', 'Harpret Bray', 'Leigh-Ann Goodliff',
'Seoras Bayes', 'Jenna Underhill', 'Veena Helps',
'Mahad Mcintosh', 'Allie Hazlehurst', 'Aoife Warrington',
'Cameron Burton', 'Yildirim Ahlberg', 'Alissa Clayton',
'Josephine Weber', 'Fiore Govan', 'Howard Ragsdale',
'Tiernan Larkins', 'Seren Sweeny', 'Arisha Keys',
'Kiki Wearing', 'Kyran Ponsonby', 'Diannon Pepper',
'Mari Foston', 'Sunil Manson', 'Donald Wykes',
'Rosie Higham', 'Karmin Raines', 'Tayyibah Leathem',
'Kara-jay Knoll', 'Shail Dalgleish', 'Jaimie Sells'):
start = now.addDays(-random.randint(1, 30))
start = now.addSecs(-random.randint(60 * 5, 60 * 60 * 2))
end = start.addSecs(random.randint(20, 60 * 13))
start=start.toString(DATETIME_FORMAT)
end=end.toString(DATETIME_FORMAT)
topic = random.choice(topics)
outcomeid = int(random.randint(1, 5))
query.bindValue(":caller", name)
query.bindValue(":starttime", start)
query.bindValue(":endtime", end)
query.bindValue(":topic", topic)
query.bindValue(":outcomeid", outcomeid)
query.exec_()
QApplication.processEvents()
print("Calls:")
query.exec_("SELECT calls.id, calls.caller, calls.starttime, "
"calls.endtime, calls.topic, calls.outcomeid, "
"outcomes.name FROM calls, outcomes "
"WHERE calls.outcomeid = outcomes.id "
"ORDER by calls.starttime")
while query.next():
id = query.value(ID)
caller = str(query.value(CALLER))
starttime = str(query.value(STARTTIME))
endtime = str(query.value(ENDTIME))
topic = str(query.value(TOPIC))
outcome = str(query.value(6))
print("{0:02d}: {1} {2} - {3} {4} [{5}]".format(id, caller,
starttime, endtime, topic, outcome))
QApplication.processEvents()
class PhoneLogDlg(QDialog):
FIRST, PREV, NEXT, LAST = range(4)
def __init__(self, parent=None):
super(PhoneLogDlg, self).__init__(parent)
callerLabel = QLabel("&Caller:")
self.callerEdit = QLineEdit()
callerLabel.setBuddy(self.callerEdit)
today = QDate.currentDate()
startLabel = QLabel("&Start:")
self.startDateTime = QDateTimeEdit()
startLabel.setBuddy(self.startDateTime)
self.startDateTime.setDateRange(today, today)
self.startDateTime.setDisplayFormat(DATETIME_FORMAT)
endLabel = QLabel("&End:")
self.endDateTime = QDateTimeEdit()
endLabel.setBuddy(self.endDateTime)
self.endDateTime.setDateRange(today, today)
self.endDateTime.setDisplayFormat(DATETIME_FORMAT)
topicLabel = QLabel("&Topic:")
topicEdit = QLineEdit()
topicLabel.setBuddy(topicEdit)
outcomeLabel = QLabel("&Outcome:")
self.outcomeComboBox = QComboBox()
outcomeLabel.setBuddy(self.outcomeComboBox)
firstButton = QPushButton()
firstButton.setIcon(QIcon(":/first.png"))
prevButton = QPushButton()
prevButton.setIcon(QIcon(":/prev.png"))
nextButton = QPushButton()
nextButton.setIcon(QIcon(":/next.png"))
lastButton = QPushButton()
lastButton.setIcon(QIcon(":/last.png"))
addButton = QPushButton("&Add")
addButton.setIcon(QIcon(":/add.png"))
deleteButton = QPushButton("&Delete")
deleteButton.setIcon(QIcon(":/delete.png"))
quitButton = QPushButton("&Quit")
quitButton.setIcon(QIcon(":/quit.png"))
if not MAC:
addButton.setFocusPolicy(Qt.NoFocus)
deleteButton.setFocusPolicy(Qt.NoFocus)
fieldLayout = QGridLayout()
fieldLayout.addWidget(callerLabel, 0, 0)
fieldLayout.addWidget(self.callerEdit, 0, 1, 1, 3)
fieldLayout.addWidget(startLabel, 1, 0)
fieldLayout.addWidget(self.startDateTime, 1, 1)
fieldLayout.addWidget(endLabel, 1, 2)
fieldLayout.addWidget(self.endDateTime, 1, 3)
fieldLayout.addWidget(topicLabel, 2, 0)
fieldLayout.addWidget(topicEdit, 2, 1, 1, 3)
fieldLayout.addWidget(outcomeLabel, 3, 0)
fieldLayout.addWidget(self.outcomeComboBox, 3, 1, 1, 3)
navigationLayout = QHBoxLayout()
navigationLayout.addWidget(firstButton)
navigationLayout.addWidget(prevButton)
navigationLayout.addWidget(nextButton)
navigationLayout.addWidget(lastButton)
fieldLayout.addLayout(navigationLayout, 4, 0, 1, 2)
buttonLayout = QVBoxLayout()
buttonLayout.addWidget(addButton)
buttonLayout.addWidget(deleteButton)
buttonLayout.addStretch()
buttonLayout.addWidget(quitButton)
layout = QHBoxLayout()
layout.addLayout(fieldLayout)
layout.addLayout(buttonLayout)
self.setLayout(layout)
self.model = QSqlRelationalTableModel(self)
self.model.setTable("calls")
self.model.setRelation(OUTCOMEID,
QSqlRelation("outcomes", "id", "name"))
self.model.setSort(STARTTIME, Qt.AscendingOrder)
self.model.select()
self.mapper = QDataWidgetMapper(self)
self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)
self.mapper.setModel(self.model)
self.mapper.setItemDelegate(QSqlRelationalDelegate(self))
self.mapper.addMapping(self.callerEdit, CALLER)
self.mapper.addMapping(self.startDateTime, STARTTIME)
self.mapper.addMapping(self.endDateTime, ENDTIME)
self.mapper.addMapping(topicEdit, TOPIC)
relationModel = self.model.relationModel(OUTCOMEID)
self.outcomeComboBox.setModel(relationModel)
self.outcomeComboBox.setModelColumn(
relationModel.fieldIndex("name"))
self.mapper.addMapping(self.outcomeComboBox, OUTCOMEID)
self.mapper.toFirst()
firstButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.FIRST))
prevButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.PREV))
nextButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.NEXT))
lastButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.LAST))
addButton.clicked.connect(self.addRecord)
deleteButton.clicked.connect(self.deleteRecord)
quitButton.clicked.connect(self.done)
self.setWindowTitle("Phone Log")
def done(self, result=None):
self.mapper.submit()
QDialog.done(self, True)
def addRecord(self):
row = self.model.rowCount()
self.mapper.submit()
self.model.insertRow(row)
self.mapper.setCurrentIndex(row)
now = QDateTime.currentDateTime()
self.startDateTime.setDateTime(now)
self.endDateTime.setDateTime(now)
self.outcomeComboBox.setCurrentIndex(
self.outcomeComboBox.findText("Unresolved"))
self.callerEdit.setFocus()
def deleteRecord(self):
caller = self.callerEdit.text()
starttime = self.startDateTime.dateTime().toString(
DATETIME_FORMAT)
if (QMessageBox.question(self,
"Delete",
"Delete call made by<br>{0} on {1}?".format(caller,starttime),
QMessageBox.Yes|QMessageBox.No) ==
QMessageBox.No):
return
row = self.mapper.currentIndex()
self.model.removeRow(row)
self.model.submitAll()
self.model.select()
if row + 1 >= self.model.rowCount():
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
def saveRecord(self, where):
row = self.mapper.currentIndex()
self.mapper.submit()
if where == PhoneLogDlg.FIRST:
row = 0
elif where == PhoneLogDlg.PREV:
row = 0 if row <= 1 else row - 1
elif where == PhoneLogDlg.NEXT:
row += 1
if row >= self.model.rowCount():
row = self.model.rowCount() - 1
elif where == PhoneLogDlg.LAST:
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
def main():
app = QApplication(sys.argv)
filename = os.path.join(os.path.dirname(__file__), "phonelog-fk.db")
create = not QFile.exists(filename)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(filename)
if not db.open():
QMessageBox.warning(None, "Phone Log",
QString("Database Error: %1").arg(db.lastError().text()))
sys.exit(1)
splash = None
if create:
app.setOverrideCursor(QCursor(Qt.WaitCursor))
splash = QLabel()
pixmap = QPixmap(":/phonelogsplash.png")
splash.setPixmap(pixmap)
splash.setMask(pixmap.createHeuristicMask())
splash.setWindowFlags(Qt.SplashScreen)
rect = app.desktop().availableGeometry()
splash.move((rect.width() - pixmap.width()) / 2,
(rect.height() - pixmap.height()) / 2)
splash.show()
app.processEvents()
createFakeData()
form = PhoneLogDlg()
form.show()
if create:
splash.close()
app.processEvents()
app.restoreOverrideCursor()
sys.exit(app.exec_())
main()
运行结果:
来源:https://blog.csdn.net/xiaoyangyang20/article/details/70227453


猜你喜欢
- 下面主要介绍配置SQL Server数据库恢复模式的两种方法。用T-SQL设置恢复模式你可以使用“ALTER DATABASE”命令加“SE
- 今天项目中遇到了中文编码的zip文件,处理了蛮长时间,所以记录下,以免下次踩坑。Python2下Python2中读取zip文件,zipfil
- 项目说明 该电商项目类似于京东商城,主要模块有验证、用户、第三方登录、首页广告、商品、购物车、订单、支付以及后台管理系统。项目开发模式采用前
- 目录楔子使用方法创建一个文件创建一个目录重命名将文件1.txt移动到子目录test_child, 所以此时会伴随一个创建、一个删除以及一个修
- 前言在浏览博客时,偶然看到了用python将汉字转为拼音的第三方包,但是在实现的过程中发现一些参数已经更新,现在将两种方法记录一下。xpin
- 1、安装requests、xlrd、json、unittest库<1>pip 命令安装:pip install requests
- 本篇阅读的代码片段来自于30-seconds-of-python(https://github.com/30-seconds...)。1.
- 在网页上,有一些内容是通过执行Ajax请求动态加载数据渲染出来的。对于需要获取这些内容的需求,我们可以使用Python来实现数据的抓取。Aj
- Q:Dreamweaver(简称DW)中如何输入空格?A:按下“Ctrl+Shift+空格键”或者在中
- 简介在SQL SERVER中,数据库在硬盘上的存储方式和普通文件在Windows中的存储方式没有什么不同,仅仅是几个文件而已.SQL SER
- 游标概念数据库操作中我们常常会遇到这样情况,即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢?游标为我们提供了一种极为优秀的解决方
- 初级画心学Python,感觉你们的都好复杂,那我来个简单的,我是直接把心形看作是一个正方形+两个半圆:于是这就很简单了,十行代码解决:imp
- 这次让我们来看看一个真实场景吧:银行转账一、举例银行转账假设现在有一个xuewei的账号里面有 100W。然后有多个任务在转账,转入转出都是
- range()是Python的内置函数,用于创建整数的列表,可以生成递增或者递减的数列。xrange也有相同的功能, 今天来看下它们之间的
- 这是一家游戏公司,他面试通过后 擅长的机试却没答出来,不过还是被录用了。这道题内容大概这样有条蛇它长度不固定,蛇头朝北顺时针盘旋着,请打印出
- 一、Python安装Window系统下,python的安装很简单。访问python.org/download,下载最新版本,安装过程与其他w
- import retext='V101_renow.Android.2.2.Normal.1.Alpha.apk?IMSI=4600
- 我想把存在数据库里的每天24小时来访者数另放到一个Excel文件中去,可以吗?可以,其实就是将数据库里面的内容生成一个Excel文件:toe
- <P><HTML><HEAD><TITLE>javascriptboy</TITLE&
- FileField中的upload_to属性可以设定上传文件的存储目录和名称,它可以是个字符串,也可以是个callable,比如一个方法。当