宜配屋

python3+PyQt5使用数据库窗口视图

yipeiwu_com7年前Python基础

能够为数据库数据提供的最简单的用户界面之一就是窗体，窗体可以一次性呈现出来自同一记录的各个域。本文通过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()

运行结果：

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持【听图阁-专注于Python设计】。

返回列表

上一篇：Python字符转换

下一篇：PHP生成静态页面详解

相关文章

python模块hashlib(加密服务)知识点讲解

官方文案：https://docs.python.org/zh-cn/3/library/hashlib.html hashlib --- 安全哈希与消息摘要 Python的hashl...

Python生成一个迭代器的实操方法

Python生成一个迭代器的实操方法

Python怎么生成一个迭代器，对于需要处理大型数据来说，迭代器是必不可少的，这样可节省大量内存空间，更加合理操作数据。 首先我们打开编辑器，这里以Sublime text3作为示范，...

Django结合ajax进行页面实时更新的例子

Django结合ajax进行页面实时更新的例子

Django结合ajax进行页面实时更新踩过的坑 简单记录一下在使用Django、echarts和ajax实现数据动态更新时遇到的一些坑： 1、url配置错误，路径没有找对（最后使用绝...

详解python中的装饰器

在了解装饰器之前，我们需要知道什么闭包是什么鬼！ 闭包：在一个函数内定义了一个函数f,并且这个函数f引用外部变量，在把这个函数f当做返回值返回。 上述说了闭包的三个条件： 1 函数内定义...

pycharm 2019 最新激活方式（pycharm破解、激活）

pycharm 2019 最新激活方式（pycharm破解、激活）

PyCharm是一种Python IDE，带有一整套可以帮助用户在使用Python语言开发时提高其效率的工具，比如调试、语法高亮、Project管理、代码跳转、智能提示、自动完成、单元测...

© YiPeiWu.com 【宜配屋】 粤ICP备17031333号

Powered By Z-BlogPHP. Theme by TOYEAN.