Python高级开发————Mysql数据库
环境准备
在cmd里安装pymysql,并配置环境
需求
(1)创建一个数据表为user
(2)编写程序完成如下功能:
注册,终端输入用户名和密码,将用户名密码存入到数据库中,用户名不能重复
登录,从终端输入用户名和密码,如果该用户存在则得到登录成功,不存在则得到登录失败
实现
User.py
# noinspection PyUnresolvedReferences
from PyQt5 import QtWidgets,QtGui,QtCore
from PyQt5.QtWidgets import QMessageBox
from redistered import Ui_Form_registered
from menu import Ui_MainWindow_menu
from log_in import Ui_Form_log_in
import sys
import pymysql
db=pymysql.connect(host='localhost',
port=3306,
user='root',
passwd='123456',
database='user',
charset='utf8')
cur=db.cursor()
try:
class Main (QtWidgets.QMainWindow, Ui_MainWindow_menu):
def __init__(self):
super().__init__()
self.setupUi(self)
self.pushButton.clicked.connect(self.log_in)
self.pushButton_2.clicked.connect(self.registered)
def log_in(self):
self.log_in=Main_log()
self.log_in.show()
def registered(self):
self.registered=Main_registered()
self.registered.show()
class Main_log(QtWidgets.QWidget,Ui_Form_log_in):
def __init__(self):
super().__init__()
self.setupUi(self)
self.pushButton.clicked.connect(self.log_in)
self.pushButton_2.clicked.connect(self.return_main)
def log_in(self):
username = self.lineEdit.text()
password = self.lineEdit_2.text()
sql="select username from user where username=%s"
cur.execute(sql,username)
one_row=cur.fetchone()
if one_row:
sql="select password from user where username=%s"
cur.execute(sql,username)
one_row=cur.fetchone()
# print(one_row)
if (one_row[0]==password):
reply = QMessageBox.information(self, '标题', '登陆成功', QMessageBox.Ok)
else:
reply = QMessageBox.information(self, '标题', '用户名或密码错误', QMessageBox.Ok)
else:
reply = QMessageBox.information(self, '标题', '用户名或密码错误', QMessageBox.Ok)
# if username==all_username:
# if password==password_1:
# reply = QMessageBox.information(self, '标题', '登陆成功', QMessageBox.Ok)
def return_main(self):
self.destroy()
class Main_registered(QtWidgets.QWidget,Ui_Form_registered):
def __init__(self):
super().__init__()
self.setupUi(self)
self.pushButton.clicked.connect(self.registered)
self.pushButton_2.clicked.connect(self.return_main)
def registered(self):
username=self.lineEdit.text()
password=self.lineEdit_2.text()
password2=self.lineEdit_3.text()
sql="select * from user"
cur.execute(sql)
one_row=cur.fetchone()
if one_row:
sql = "select * from user where username=%s;"
cur.execute(sql,username)
one_row = cur.fetchone()
if (one_row):
reply = QMessageBox.information(self, '标题', '该用户名已被注册', QMessageBox.Ok)
else:
if (password == password2):
sql = "insert into user(username,password) values (%s,%s);"
cur.execute(sql, [username, password])
db.commit()
reply = QMessageBox.information(self, '标题', '成功注册', QMessageBox.Ok)
else:
reply = QMessageBox.information(self, '标题', '密码不一致', QMessageBox.Ok)
else:
if (password == password2):
sql="insert into user(username,password) values (%s,%s);"
cur.execute(sql,[username,password])
db.commit()
reply = QMessageBox.information(self, '标题', '成功注册', QMessageBox.Ok)
else:
reply = QMessageBox.information(self, '标题', '密码不一致', QMessageBox.Ok)
def return_main(self):
self.destroy()
QtCore.QCoreApplication.setAttribute(QtCore.Qt.AA_EnableHighDpiScaling) # 使窗体按照Qt设计显示
app = QtWidgets.QApplication(sys.argv)
main = Main()
main.show()
sys.exit(app.exec_())
except Exception as e:
db.rollback()
print("出现异常,数据回滚")
#关闭游标和数据库连接
cur.close()
db.close()
以下.py文件皆由.ui文件转换。使用GUI编程生成.ui文件,再由QT Designer生成.py文件。
log_in.py
# -*- coding: utf-8 -*-
# Form implementation generated from reading ui file 'log_in.ui'
#
# Created by: PyQt5 UI code generator 5.13.0
#
# WARNING! All changes made in this file will be lost!
from PyQt5 import QtCore, QtGui, QtWidgets
class Ui_Form_log_in(object):
def setupUi(self, Form):
Form.setObjectName("Form")
Form.resize(361, 392)
self.textBrowser = QtWidgets.QTextBrowser(Form)
self.textBrowser.setGeometry(QtCore.QRect(0, 0, 361, 131))
self.textBrowser.setObjectName("textBrowser")
self.gridLayoutWidget = QtWidgets.QWidget(Form)
self.gridLayoutWidget.setGeometry(QtCore.QRect(0, 130, 361, 191))
self.gridLayoutWidget.setObjectName("gridLayoutWidget")
self.gridLayout = QtWidgets.QGridLayout(self.gridLayoutWidget)
self.gridLayout.setContentsMargins(0, 0, 0, 0)
self.gridLayout.setObjectName("gridLayout")
self.label_2 = QtWidgets.QLabel(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.label_2.setFont(font)
self.label_2.setObjectName("label_2")
self.gridLayout.addWidget(self.label_2, 1, 0, 1, 1)
self.label = QtWidgets.QLabel(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.label.setFont(font)
self.label.setObjectName("label")
self.gridLayout.addWidget(self.label, 0, 0, 1, 1)
self.lineEdit = QtWidgets.QLineEdit(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.lineEdit.setFont(font)
self.lineEdit.setObjectName("lineEdit")
self.gridLayout.addWidget(self.lineEdit, 0, 1, 1, 1)
self.lineEdit_2 = QtWidgets.QLineEdit(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.lineEdit_2.setFont(font)
self.lineEdit_2.setObjectName("lineEdit_2")
self.gridLayout.addWidget(self.lineEdit_2, 1, 1, 1, 1)
self.pushButton = QtWidgets.QPushButton(Form)
self.pushButton.setGeometry(QtCore.QRect(60, 340, 75, 31))
font = QtGui.QFont()
font.setPointSize(20)
self.pushButton.setFont(font)
self.pushButton.setObjectName("pushButton")
self.pushButton_2 = QtWidgets.QPushButton(Form)
self.pushButton_2.setGeometry(QtCore.QRect(190, 340, 75, 31))
font = QtGui.QFont()
font.setPointSize(20)
self.pushButton_2.setFont(font)
self.pushButton_2.setObjectName("pushButton_2")
self.retranslateUi(Form)
QtCore.QMetaObject.connectSlotsByName(Form)
def retranslateUi(self, Form):
_translate = QtCore.QCoreApplication.translate
Form.setWindowTitle(_translate("Form", "Form"))
self.textBrowser.setHtml(_translate("Form", "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:\'SimSun\'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p align=\"center\" style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:36pt;\">赛尔号</span></p>\n"
"<p align=\"center\" style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:36pt;\">登陆界面</span></p></body></html>"))
self.label_2.setText(_translate("Form", "密码"))
self.label.setText(_translate("Form", "用户名"))
self.pushButton.setText(_translate("Form", "登录"))
self.pushButton_2.setText(_translate("Form", "返回"))
redistered.py
# -*- coding: utf-8 -*-
# Form implementation generated from reading ui file 'redistered.ui'
#
# Created by: PyQt5 UI code generator 5.13.0
#
# WARNING! All changes made in this file will be lost!
from PyQt5 import QtCore, QtGui, QtWidgets
class Ui_Form_registered(object):
def setupUi(self, Form):
Form.setObjectName("Form")
Form.resize(361, 392)
font = QtGui.QFont()
font.setPointSize(20)
Form.setFont(font)
self.textBrowser = QtWidgets.QTextBrowser(Form)
self.textBrowser.setGeometry(QtCore.QRect(0, 0, 361, 131))
self.textBrowser.setObjectName("textBrowser")
self.gridLayoutWidget = QtWidgets.QWidget(Form)
self.gridLayoutWidget.setGeometry(QtCore.QRect(0, 130, 361, 191))
self.gridLayoutWidget.setObjectName("gridLayoutWidget")
self.gridLayout = QtWidgets.QGridLayout(self.gridLayoutWidget)
self.gridLayout.setContentsMargins(0, 0, 0, 0)
self.gridLayout.setObjectName("gridLayout")
self.label = QtWidgets.QLabel(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.label.setFont(font)
self.label.setObjectName("label")
self.gridLayout.addWidget(self.label, 0, 0, 1, 1)
self.label_2 = QtWidgets.QLabel(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.label_2.setFont(font)
self.label_2.setObjectName("label_2")
self.gridLayout.addWidget(self.label_2, 1, 0, 1, 1)
self.lineEdit_2 = QtWidgets.QLineEdit(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.lineEdit_2.setFont(font)
self.lineEdit_2.setObjectName("lineEdit_2")
self.gridLayout.addWidget(self.lineEdit_2, 1, 1, 1, 1)
self.lineEdit = QtWidgets.QLineEdit(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.lineEdit.setFont(font)
self.lineEdit.setObjectName("lineEdit")
self.gridLayout.addWidget(self.lineEdit, 0, 1, 1, 1)
self.label_3 = QtWidgets.QLabel(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.label_3.setFont(font)
self.label_3.setObjectName("label_3")
self.gridLayout.addWidget(self.label_3, 2, 0, 1, 1)
self.lineEdit_3 = QtWidgets.QLineEdit(self.gridLayoutWidget)
font = QtGui.QFont()
font.setPointSize(18)
self.lineEdit_3.setFont(font)
self.lineEdit_3.setObjectName("lineEdit_3")
self.gridLayout.addWidget(self.lineEdit_3, 2, 1, 1, 1)
self.pushButton = QtWidgets.QPushButton(Form)
self.pushButton.setGeometry(QtCore.QRect(70, 340, 81, 31))
self.pushButton.setObjectName("pushButton")
self.pushButton_2 = QtWidgets.QPushButton(Form)
self.pushButton_2.setGeometry(QtCore.QRect(200, 340, 81, 31))
self.pushButton_2.setObjectName("pushButton_2")
self.retranslateUi(Form)
QtCore.QMetaObject.connectSlotsByName(Form)
def retranslateUi(self, Form):
_translate = QtCore.QCoreApplication.translate
Form.setWindowTitle(_translate("Form", "Form"))
self.textBrowser.setHtml(_translate("Form", "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:\'SimSun\'; font-size:20pt; font-weight:400; font-style:normal;\">\n"
"<p align=\"center\" style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:36pt;\">赛尔号</span></p>\n"
"<p align=\"center\" style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:36pt;\">注册界面</span></p></body></html>"))
self.label.setText(_translate("Form", "用户名"))
self.label_2.setText(_translate("Form", "输入密码"))
self.label_3.setText(_translate("Form", "再次输入密码"))
self.pushButton.setText(_translate("Form", "注册"))
self.pushButton_2.setText(_translate("Form", "返回"))
menu.py
# -*- coding: utf-8 -*-
# Form implementation generated from reading ui file 'menu.ui'
#
# Created by: PyQt5 UI code generator 5.13.0
#
# WARNING! All changes made in this file will be lost!
from PyQt5 import QtCore, QtGui, QtWidgets
class Ui_MainWindow_menu(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(291, 338)
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.verticalLayoutWidget = QtWidgets.QWidget(self.centralwidget)
self.verticalLayoutWidget.setGeometry(QtCore.QRect(0, 130, 291, 161))
self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
self.verticalLayout.setContentsMargins(0, 0, 0, 0)
self.verticalLayout.setObjectName("verticalLayout")
self.pushButton = QtWidgets.QPushButton(self.verticalLayoutWidget)
font = QtGui.QFont()
font.setPointSize(22)
self.pushButton.setFont(font)
self.pushButton.setObjectName("pushButton")
self.verticalLayout.addWidget(self.pushButton)
self.pushButton_2 = QtWidgets.QPushButton(self.verticalLayoutWidget)
font = QtGui.QFont()
font.setPointSize(22)
self.pushButton_2.setFont(font)
self.pushButton_2.setObjectName("pushButton_2")
self.verticalLayout.addWidget(self.pushButton_2)
self.textBrowser = QtWidgets.QTextBrowser(self.centralwidget)
self.textBrowser.setGeometry(QtCore.QRect(0, 0, 291, 71))
self.textBrowser.setObjectName("textBrowser")
MainWindow.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(MainWindow)
self.menubar.setGeometry(QtCore.QRect(0, 0, 291, 23))
self.menubar.setObjectName("menubar")
MainWindow.setMenuBar(self.menubar)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
self.pushButton.setText(_translate("MainWindow", "登录"))
self.pushButton_2.setText(_translate("MainWindow", "注册"))
self.textBrowser.setHtml(_translate("MainWindow", "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:\'SimSun\'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p align=\"center\" style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\"><span style=\" font-size:36pt;\">赛尔号</span></p></body></html>"))