宜配屋

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

yipeiwu_com6年前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 "No module named pip"的问题

python 升级后导致不能使用原来的pip命令 windows平台 cmd中敲命令：python -m ensurepip 得到pip的setuptools 然后就可以用：easy_i...

详解Python编程中基本的数学计算使用

数 在 Python 中，对数的规定比较简单，基本在小学数学水平即可理解。 那么，做为零基础学习这，也就从计算小学数学题目开始吧。因为从这里开始，数学的基础知识列位肯定过关了。 &g...

Python实现App自动签到领取积分功能

Python实现App自动签到领取积分功能

要自动签到，最简单的是打开页面分析请求，然后我们用脚本实现请求的自动化。但是发现食行没有页面，只有 APP，这不是一个好消息，这意味着需要抓包处理了。 下面的操作就好办了，在电脑端的...

Flask框架web开发之零基础入门

Flask框架web开发之零基础入门

Flask框架是Python开发的一个基于Werkzeug和Jinja 2的web开发微框架，它的优势就是极其简洁， 但又非常灵活，而且容易学习和应用。因此Flask框架是Python新...

python实现识别相似图片小结

python实现识别相似图片小结

文章简介 在网上看到python做图像识别的相关文章后，真心感觉python的功能实在太强大，因此将这些文章总结一下，建立一下自己的知识体系。 当然了，图像识别这个话题作为计算机科学的...

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

Powered By Z-BlogPHP. Theme by TOYEAN.