一、问题描述
在我们实际前端项目开发中,时常会遇到将自己建立的Exel表数据写进MySQL数据库中,之后又可以在数据库中显示出来。那么具体该怎么操作呢?
二、问题思路
- 建立一个自己的Exel表格。(本文中我建立的Exel表是一些书的名称);
- pyqt5实现界面获取选择电脑上文件路径的功能;
- 将Exel表中的数据上传到MySQL数据库中;
——先在数据库中建立与自己Exel表格式一样的表头,名称命名与Exel表名称相同。
——读取Exel表中的数据。
——将Exel表中的数据写进数据库中。 - 在界面中将写进数据库中的数据显示出来;
工具:Pycharm,pyqt5,MySQL。
三、问题解决
- 界面制作与代码(界面文件命名为experiment_7.ui,生成python文件为experiment_7.py):
from PyQt5 import QtCore, QtGui, QtWidgets
class Ui_MainWindow(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(800, 600)
MainWindow.setStyleSheet("background-color: rgb(85, 170, 255);")
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.tableWidget = QtWidgets.QTableWidget(self.centralwidget)
self.tableWidget.setGeometry(QtCore.QRect(170, 130, 411, 351))
self.tableWidget.setStyleSheet("background-color: rgb(255, 255, 255);")
self.tableWidget.setObjectName("tableWidget")
self.tableWidget.setColumnCount(2)
self.tableWidget.setRowCount(20)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(0, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(1, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(2, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(3, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(4, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(5, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(6, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(7, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(8, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(9, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(10, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(11, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(12, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(13, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(14, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(15, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(16, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(17, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(18, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setVerticalHeaderItem(19, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(0, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(1, item)
self.layoutWidget = QtWidgets.QWidget(self.centralwidget)
self.layoutWidget.setGeometry(QtCore.QRect(172, 51, 427, 30))
self.layoutWidget.setObjectName("layoutWidget")
self.horizontalLayout = QtWidgets.QHBoxLayout(self.layoutWidget)
self.horizontalLayout.setContentsMargins(0, 0, 0, 0)
self.horizontalLayout.setObjectName("horizontalLayout")
self.lineEdit = QtWidgets.QLineEdit(self.layoutWidget)
self.lineEdit.setStyleSheet("background-color: rgb(255, 255, 255);")
self.lineEdit.setObjectName("lineEdit")
self.horizontalLayout.addWidget(self.lineEdit)
self.toolButton = QtWidgets.QToolButton(self.layoutWidget)
self.toolButton.setObjectName("toolButton")
self.horizontalLayout.addWidget(self.toolButton)
self.uploadData_Button = QtWidgets.QPushButton(self.layoutWidget)
self.uploadData_Button.setObjectName("uploadData_Button")
self.horizontalLayout.addWidget(self.uploadData_Button)
self.showData_Button = QtWidgets.QPushButton(self.layoutWidget)
self.showData_Button.setObjectName("showData_Button")
self.horizontalLayout.addWidget(self.showData_Button)
MainWindow.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(MainWindow)
self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 26))
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"))
item = self.tableWidget.verticalHeaderItem(0)
item.setText(_translate("MainWindow", "1"))
item = self.tableWidget.verticalHeaderItem(1)
item.setText(_translate("MainWindow", "2"))
item = self.tableWidget.verticalHeaderItem(2)
item.setText(_translate("MainWindow", "3"))
item = self.tableWidget.verticalHeaderItem(3)
item.setText(_translate("MainWindow", "4"))
item = self.tableWidget.verticalHeaderItem(4)
item.setText(_translate("MainWindow", "5"))
item = self.tableWidget.verticalHeaderItem(5)
item.setText(_translate("MainWindow", "6"))
item = self.tableWidget.verticalHeaderItem(6)
item.setText(_translate("MainWindow", "7"))
item = self.tableWidget.verticalHeaderItem(7)
item.setText(_translate("MainWindow", "8"))
item = self.tableWidget.verticalHeaderItem(8)
item.setText(_translate("MainWindow", "9"))
item = self.tableWidget.verticalHeaderItem(9)
item.setText(_translate("MainWindow", "10"))
item = self.tableWidget.verticalHeaderItem(10)
item.setText(_translate("MainWindow", "11"))
item = self.tableWidget.verticalHeaderItem(11)
item.setText(_translate("MainWindow", "12"))
item = self.tableWidget.verticalHeaderItem(12)
item.setText(_translate("MainWindow", "13"))
item = self.tableWidget.verticalHeaderItem(13)
item.setText(_translate("MainWindow", "14"))
item = self.tableWidget.verticalHeaderItem(14)
item.setText(_translate("MainWindow", "15"))
item = self.tableWidget.verticalHeaderItem(15)
item.setText(_translate("MainWindow", "16"))
item = self.tableWidget.verticalHeaderItem(16)
item.setText(_translate("MainWindow", "17"))
item = self.tableWidget.verticalHeaderItem(17)
item.setText(_translate("MainWindow", "18"))
item = self.tableWidget.verticalHeaderItem(18)
item.setText(_translate("MainWindow", "19"))
item = self.tableWidget.verticalHeaderItem(19)
item.setText(_translate("MainWindow", "20"))
item = self.tableWidget.horizontalHeaderItem(0)
item.setText(_translate("MainWindow", "序号"))
item = self.tableWidget.horizontalHeaderItem(1)
item.setText(_translate("MainWindow", "书名"))
self.toolButton.setText(_translate("MainWindow", "..."))
self.uploadData_Button.setText(_translate("MainWindow", "上传数据"))
self.showData_Button.setText(_translate("MainWindow", "显示数据"))
- 实现界面与逻辑的分离,逻辑文件命名为callExperiment_7.py(详细参考上篇文章)
from PyQt5 import QtWidgets
from PyQt5.QtWidgets import QApplication, QMainWindow, QFileDialog
import sys, xlrd, pymysql
from experiment_7 import Ui_MainWindow#导入相关数据库,还要将界面文件中的类导入进来(即可调用界面文件)
#类的继承
class MyFile (QMainWindow, Ui_MainWindow):
def __init__(self,parent = None):
super(MyFile, self).__init__(parent)
self.setupUi(self)
self.retranslateUi(self)
#界面显示代码,标准的五行,其中一二五行是固定的
if __name__ == '__main__':
app = QApplication(sys.argv)
ui = MyFile()#MyFile是此页面代码中的类名
ui.show()#调用python自带的显示函数show()
sys.exit(app.exec_())
- pyqt5实现界面获取选择电脑上文件路径的功能;
self.toolButton.clicked.connect(self.getFile)#赋予按钮选择电脑上某一个具体文件的功能
def getFile(self):
folder_directory = QFileDialog.getExistingDirectory(self, "选取文件", "/")#获取文件夹的路径
file_directory = QFileDialog.getOpenFileNames(self, "请选择要添加的文件", folder_directory, "Text Files (*.xls);;All Files(*)")#获取某个具体文件的路径
self.lineEdit.setText(str(file_directory))#将文件的路径显示再lineEdit中
- 将Exel表中的数据上传到MySQL数据库中;
self.uploadData_Button.clicked.connect(self.uploadDatabase)
——先在数据库中建立与自己Exel表格式一样的表头,名称命名与Exel表名称相同
#1.先在数据库中建表
conn = pymysql.Connect(
host='localhost', # 连接IP地址
user='root', # 数据库用户名
password='123456', # 数据库密码
database='books' # 需要查询的数据库名
)
cursor = conn.cursor()
sql_createTb ="CREATE TABLE BOOKS (Number INT NOT NULL AUTO_INCREMENT, Name CHAR(225), PRIMARY KEY(Number))"
cursor.execute(sql_createTb)
conn.commit()
conn.close()
cursor.close()
——读取Exel表中的数据
#2.读取Exel表格中的数据
file = xlrd.open_workbook("C:\\Users\\2020\\Desktop\\Books.xls")
sheet_1 = file.sheet_by_index(0)
# row_content = sheet_1.row_values(2)
row_number = sheet_1.nrows
# col_number = sheet_1.ncols
conn = pymysql.Connect(
host='localhost', # 连接IP地址
user='root', # 数据库用户名
password='123456', # 数据库密码
database='books' # 需要查询的数据库名
)
cursor = conn.cursor()
for row in range(1, row_number):
Number = sheet_1.cell(row,0).value
Name = sheet_1.cell(row,1).value
print(Number,Name)
——将Exel表中的数据写进数据库中
#3.将Exel表格中的数据插入到数据库中
sql = "insert into books (Number , Name ) VALUES ('%d', '%s')" %(Number,Name)
try:
cursor.execute(sql)
conn.commit()
except:
print('Insert Failed')
print("Done!")
cursor.close()
conn.close()
- 在界面中将写进数据库中的数据显示出来;
#读取数据库中的内容显示到界面上(已完成)
def showData(self):
conn = pymysql.Connect(
host='localhost', # 连接IP地址
user='root', # 数据库用户名
password='123456', # 数据库密码
database='books' # 需要查询的数据库名
)
cursor = conn.cursor()
cursor.execute('select * from books')
data = cursor.fetchall()
print(data)
x = 0
for i in data:
y = 0
for j in i:
self.tableWidget.setItem(x,y,QtWidgets.QTableWidgetItem(str(data[x][y])))
y = y + 1
x = x + 1
cursor.close()
conn.close()