基于MySQL的音乐器
- 带有用户登录功能
- 验证用户身份，用户注册等操作
- 还有用户音乐列表，以及增删查改操作
INSERT into users(username,passwd,phone_number,created_time,role) VALUES(‘张三’,‘123456’,‘123’,‘2025-3-11’,‘1’)
三张表，users表，存放用户信息
musiclibrary表存放音乐信息
user_favorites表放用户id和音乐id，就是用户的个人喜好。所有用户的个人喜好都在这个表里面
关于音乐下载的，可以看我以前的爬虫文章，复制源代码，运行就是一个音乐下载器了。再配合这个音乐播放器，非常好用，安利一下。链接：
https://blog.csdn.net/FZ51111/article/details/143540349?spm=1011.2415.3001.5331
- 第一次，手搓这个音乐播放器，有很多可以优化的地方
"""
基于MySQL的音乐器
有用户登录功能
还要用户注册
用户个人的音乐列表，还有对应的增删查
"""
import re
import threadingimport pygame
from PyQt5.QtWidgets import QApplication, QWidget, QMessageBox, QStackedWidget, QVBoxLayout, QFileDialog
import sys
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtGui import QIcon
import pymysql
from datetime import datetime
from playsound import playsoundclass Ui_denglu(QWidget):def setupUi(self, denglu):denglu.setObjectName("denglu")denglu.resize(420, 281)font = QtGui.QFont()font.setBold(True)font.setWeight(75)denglu.setFont(font)self.verticalLayout = QtWidgets.QVBoxLayout(denglu)self.verticalLayout.setObjectName("verticalLayout")self.horizontalLayout = QtWidgets.QHBoxLayout()self.horizontalLayout.setObjectName("horizontalLayout")self.label = QtWidgets.QLabel(denglu)self.label.setObjectName("label")self.horizontalLayout.addWidget(self.label)self.lineEdit = QtWidgets.QLineEdit(denglu)self.lineEdit.setObjectName("lineEdit")self.horizontalLayout.addWidget(self.lineEdit)self.verticalLayout.addLayout(self.horizontalLayout)self.horizontalLayout_2 = QtWidgets.QHBoxLayout()self.horizontalLayout_2.setObjectName("horizontalLayout_2")self.label_2 = QtWidgets.QLabel(denglu)self.label_2.setObjectName("label_2")self.horizontalLayout_2.addWidget(self.label_2)self.lineEdit_2 = QtWidgets.QLineEdit(denglu)self.lineEdit_2.setObjectName("lineEdit_2")self.lineEdit_2.setEchoMode(QtWidgets.QLineEdit.Password)self.horizontalLayout_2.addWidget(self.lineEdit_2)self.verticalLayout.addLayout(self.horizontalLayout_2)self.horizontalLayout_3 = QtWidgets.QHBoxLayout()self.horizontalLayout_3.setObjectName("horizontalLayout_3")spacerItem = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout_3.addItem(spacerItem)self.pushButton = QtWidgets.QPushButton(denglu)self.pushButton.setObjectName("pushButton")self.horizontalLayout_3.addWidget(self.pushButton)spacerItem1 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout_3.addItem(spacerItem1)self.verticalLayout.addLayout(self.horizontalLayout_3)self.horizontalLayout_4 = QtWidgets.QHBoxLayout()self.horizontalLayout_4.setObjectName("horizontalLayout_4")self.label_3 = QtWidgets.QLabel(denglu)self.label_3.setStyleSheet("color:\'#EEB422\'")self.label_3.setObjectName("label_3")self.horizontalLayout_4.addWidget(self.label_3)spacerItem2 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout_4.addItem(spacerItem2)self.pushButton_2 = QtWidgets.QPushButton(denglu)self.pushButton_2.setObjectName("pushButton_2")self.horizontalLayout_4.addWidget(self.pushButton_2)self.verticalLayout.addLayout(self.horizontalLayout_4)self.retranslateUi(denglu)def retranslateUi(self, denglu):_translate = QtCore.QCoreApplication.translatedenglu.setWindowTitle(_translate("denglu", "音乐播放器"))self.label.setText(_translate("denglu", "账号"))self.label_2.setText(_translate("denglu", "密码"))self.pushButton.setText(_translate("denglu", "登录"))self.label_3.setText(_translate("denglu", "没有账号请先注册>>>>>"))self.pushButton_2.setText(_translate("denglu", "注册"))class Ui_zhuce(QWidget):def setupUi(self, zhuce):zhuce.setObjectName("zhuce")zhuce.resize(378, 230)font = QtGui.QFont()font.setBold(True)font.setWeight(75)zhuce.setFont(font)self.verticalLayout = QtWidgets.QVBoxLayout(zhuce)self.verticalLayout.setObjectName("verticalLayout")self.horizontalLayout = QtWidgets.QHBoxLayout()self.horizontalLayout.setObjectName("horizontalLayout")self.label = QtWidgets.QLabel(zhuce)self.label.setObjectName("label")self.horizontalLayout.addWidget(self.label)self.lineEdit = QtWidgets.QLineEdit(zhuce)self.lineEdit.setObjectName("lineEdit")self.horizontalLayout.addWidget(self.lineEdit)self.verticalLayout.addLayout(self.horizontalLayout)self.horizontalLayout_2 = QtWidgets.QHBoxLayout()self.horizontalLayout_2.setObjectName("horizontalLayout_2")self.label_2 = QtWidgets.QLabel(zhuce)self.label_2.setObjectName("label_2")self.horizontalLayout_2.addWidget(self.label_2)self.lineEdit_2 = QtWidgets.QLineEdit(zhuce)self.lineEdit_2.setObjectName("lineEdit_2")self.lineEdit_2.setEchoMode(QtWidgets.QLineEdit.Password)self.horizontalLayout_2.addWidget(self.lineEdit_2)self.verticalLayout.addLayout(self.horizontalLayout_2)self.horizontalLayout_3 = QtWidgets.QHBoxLayout()self.horizontalLayout_3.setObjectName("horizontalLayout_3")self.label_4 = QtWidgets.QLabel(zhuce)self.label_4.setObjectName("label_4")self.horizontalLayout_3.addWidget(self.label_4)self.lineEdit_4 = QtWidgets.QLineEdit(zhuce)self.lineEdit_4.setObjectName("lineEdit_4")self.lineEdit_4.setEchoMode(QtWidgets.QLineEdit.Password)self.horizontalLayout_3.addWidget(self.lineEdit_4)self.verticalLayout.addLayout(self.horizontalLayout_3)self.horizontalLayout_4 = QtWidgets.QHBoxLayout()self.horizontalLayout_4.setObjectName("horizontalLayout_4")self.label_3 = QtWidgets.QLabel(zhuce)self.label_3.setObjectName("label_3")self.horizontalLayout_4.addWidget(self.label_3)self.lineEdit_3 = QtWidgets.QLineEdit(zhuce)self.lineEdit_3.setObjectName("lineEdit_3")self.horizontalLayout_4.addWidget(self.lineEdit_3)self.verticalLayout.addLayout(self.horizontalLayout_4)self.horizontalLayout_5 = QtWidgets.QHBoxLayout()self.horizontalLayout_5.setObjectName("horizontalLayout_5")spacerItem = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout_5.addItem(spacerItem)self.pushButton = QtWidgets.QPushButton(zhuce)self.pushButton.setObjectName("pushButton")self.horizontalLayout_5.addWidget(self.pushButton)spacerItem1 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout_5.addItem(spacerItem1)self.pushButton_2 = QtWidgets.QPushButton(zhuce)self.pushButton_2.setObjectName("pushButton_2")self.horizontalLayout_5.addWidget(self.pushButton_2)spacerItem2 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout_5.addItem(spacerItem2)self.verticalLayout.addLayout(self.horizontalLayout_5)self.retranslateUi(zhuce)QtCore.QMetaObject.connectSlotsByName(zhuce)def retranslateUi(self, zhuce):_translate = QtCore.QCoreApplication.translatezhuce.setWindowTitle(_translate("zhuce", "注册"))self.label.setText(_translate("zhuce", "账号 "))self.label_2.setText(_translate("zhuce", "密码 "))self.label_4.setText(_translate("zhuce", "确认密码"))self.label_3.setText(_translate("zhuce", "手机号 "))self.pushButton.setText(_translate("zhuce", "确定"))self.pushButton_2.setText(_translate("zhuce", "取消"))class Ui_management(QWidget):def setupUi(self, management):management.setObjectName("management")management.resize(666, 555)font = QtGui.QFont()font.setBold(True)font.setWeight(75)management.setFont(font)self.horizontalLayout_3 = QtWidgets.QHBoxLayout(management)self.horizontalLayout_3.setObjectName("horizontalLayout_3")self.verticalLayout = QtWidgets.QVBoxLayout()self.verticalLayout.setObjectName("verticalLayout")self.label = QtWidgets.QLabel(management)self.label.setAlignment(QtCore.Qt.AlignCenter)self.label.setObjectName("label")self.verticalLayout.addWidget(self.label)self.listWidget = QtWidgets.QListWidget(management)self.listWidget.setObjectName("listWidget")self.verticalLayout.addWidget(self.listWidget)self.horizontalLayout = QtWidgets.QHBoxLayout()self.horizontalLayout.setObjectName("horizontalLayout")self.pushButton = QtWidgets.QPushButton(management)self.pushButton.setObjectName("pushButton")self.horizontalLayout.addWidget(self.pushButton)self.pushButton_2 = QtWidgets.QPushButton(management)self.pushButton_2.setObjectName("pushButton_2")self.horizontalLayout.addWidget(self.pushButton_2)self.verticalLayout.addLayout(self.horizontalLayout)self.horizontalLayout_3.addLayout(self.verticalLayout)self.frame = QtWidgets.QFrame(management)self.frame.setFrameShape(QtWidgets.QFrame.VLine)self.frame.setFrameShadow(QtWidgets.QFrame.Raised)self.frame.setObjectName("frame")self.horizontalLayout_3.addWidget(self.frame)self.verticalLayout_2 = QtWidgets.QVBoxLayout()self.verticalLayout_2.setObjectName("verticalLayout_2")self.label_2 = QtWidgets.QLabel(management)self.label_2.setAlignment(QtCore.Qt.AlignCenter)self.label_2.setObjectName("label_2")self.verticalLayout_2.addWidget(self.label_2)self.listWidget_2 = QtWidgets.QListWidget(management)self.listWidget_2.setObjectName("listWidget_2")self.verticalLayout_2.addWidget(self.listWidget_2)self.horizontalLayout_2 = QtWidgets.QHBoxLayout()self.horizontalLayout_2.setObjectName("horizontalLayout_2")self.pushButton_4 = QtWidgets.QPushButton(management)self.pushButton_4.setObjectName("pushButton_4")self.horizontalLayout_2.addWidget(self.pushButton_4)self.pushButton_5 = QtWidgets.QPushButton(management)self.pushButton_5.setObjectName("pushButton_5")self.horizontalLayout_2.addWidget(self.pushButton_5)self.pushButton_6 = QtWidgets.QPushButton(management)self.pushButton_6.setObjectName("pushButton_6")self.horizontalLayout_2.addWidget(self.pushButton_6)self.verticalLayout_2.addLayout(self.horizontalLayout_2)self.horizontalLayout_3.addLayout(self.verticalLayout_2)self.retranslateUi(management)QtCore.QMetaObject.connectSlotsByName(management)def retranslateUi(self, management):_translate = QtCore.QCoreApplication.translatemanagement.setWindowTitle(_translate("management", "数据库管理"))self.label.setText(_translate("management", "用户管理"))self.pushButton.setText(_translate("management", "增加用户"))self.pushButton_2.setText(_translate("management", "删除用户"))self.label_2.setText(_translate("management", "音乐管理"))self.pushButton_4.setText(_translate("management", "增加音乐"))self.pushButton_5.setText(_translate("management", "删除音乐"))self.pushButton_6.setText(_translate("management", "刷新页面"))class Ui_MusicLibrary(QWidget):def setupUi(self, MusicLibrary):MusicLibrary.setObjectName("MusicLibrary")MusicLibrary.resize(651, 481)self.verticalLayout_2 = QtWidgets.QVBoxLayout(MusicLibrary)self.verticalLayout_2.setObjectName("verticalLayout_2")self.horizontalLayout_2 = QtWidgets.QHBoxLayout()self.horizontalLayout_2.setObjectName("horizontalLayout_2")self.verticalLayout = QtWidgets.QVBoxLayout()self.verticalLayout.setObjectName("verticalLayout")self.label = QtWidgets.QLabel(MusicLibrary)self.label.setObjectName("label")self.verticalLayout.addWidget(self.label)self.listWidget = QtWidgets.QListWidget(MusicLibrary)self.listWidget.setObjectName("listWidget")self.verticalLayout.addWidget(self.listWidget)self.horizontalLayout_2.addLayout(self.verticalLayout)self.frame = QtWidgets.QFrame(MusicLibrary)self.frame.setFrameShape(QtWidgets.QFrame.VLine)self.frame.setFrameShadow(QtWidgets.QFrame.Raised)self.frame.setObjectName("frame")self.horizontalLayout_2.addWidget(self.frame)self.verticalLayout_3 = QtWidgets.QVBoxLayout()self.verticalLayout_3.setObjectName("verticalLayout_3")self.label_3 = QtWidgets.QLabel(MusicLibrary)self.label_3.setObjectName("label_3")self.verticalLayout_3.addWidget(self.label_3)self.listWidget_2 = QtWidgets.QListWidget(MusicLibrary)self.listWidget_2.setObjectName("listWidget_2")self.verticalLayout_3.addWidget(self.listWidget_2)self.horizontalLayout_2.addLayout(self.verticalLayout_3)self.verticalLayout_2.addLayout(self.horizontalLayout_2)self.frame_2 = QtWidgets.QFrame(MusicLibrary)self.frame_2.setFrameShape(QtWidgets.QFrame.HLine)self.frame_2.setFrameShadow(QtWidgets.QFrame.Raised)self.frame_2.setObjectName("frame_2")self.verticalLayout_2.addWidget(self.frame_2)self.horizontalLayout = QtWidgets.QHBoxLayout()self.horizontalLayout.setObjectName("horizontalLayout")spacerItem = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout.addItem(spacerItem)self.pushButton_3 = QtWidgets.QPushButton(MusicLibrary)self.pushButton_3.setObjectName("pushButton_3")self.horizontalLayout.addWidget(self.pushButton_3)spacerItem1 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout.addItem(spacerItem1)self.pushButton = QtWidgets.QPushButton(MusicLibrary)self.pushButton.setObjectName("pushButton")self.horizontalLayout.addWidget(self.pushButton)spacerItem2 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout.addItem(spacerItem2)self.pushButton_2 = QtWidgets.QPushButton(MusicLibrary)self.pushButton_2.setObjectName("pushButton_2")self.horizontalLayout.addWidget(self.pushButton_2)spacerItem3 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout.addItem(spacerItem3)self.pushButton_4 = QtWidgets.QPushButton(MusicLibrary)self.pushButton_4.setObjectName("pushButton_4")self.horizontalLayout.addWidget(self.pushButton_4)spacerItem4 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout.addItem(spacerItem4)self.verticalLayout_2.addLayout(self.horizontalLayout)self.retranslateUi(MusicLibrary)QtCore.QMetaObject.connectSlotsByName(MusicLibrary)def retranslateUi(self, MusicLibrary):_translate = QtCore.QCoreApplication.translateMusicLibrary.setWindowTitle(_translate("MusicLibrary", "音乐播放"))self.label.setText(_translate("MusicLibrary", "音乐库"))self.label_3.setText(_translate("MusicLibrary", "个人喜欢"))self.pushButton_3.setText(_translate("MusicLibrary", "播放"))self.pushButton.setText(_translate("MusicLibrary", "添加"))self.pushButton_2.setText(_translate("MusicLibrary", "移除"))self.pushButton_4.setText(_translate("MusicLibrary", "刷新"))class Ui_Form(QWidget):def setupUi(self, Form):Form.setObjectName("Form")Form.resize(254, 285)font = QtGui.QFont()font.setBold(True)font.setWeight(75)Form.setFont(font)self.verticalLayout = QtWidgets.QVBoxLayout(Form)self.verticalLayout.setObjectName("verticalLayout")self.horizontalLayout = QtWidgets.QHBoxLayout()self.horizontalLayout.setObjectName("horizontalLayout")self.label = QtWidgets.QLabel(Form)self.label.setObjectName("label")self.horizontalLayout.addWidget(self.label)self.lineEdit = QtWidgets.QLineEdit(Form)self.lineEdit.setObjectName("lineEdit")self.horizontalLayout.addWidget(self.lineEdit)self.verticalLayout.addLayout(self.horizontalLayout)self.horizontalLayout_3 = QtWidgets.QHBoxLayout()self.horizontalLayout_3.setObjectName("horizontalLayout_3")self.label_3 = QtWidgets.QLabel(Form)self.label_3.setObjectName("label_3")self.horizontalLayout_3.addWidget(self.label_3)self.lineEdit_3 = QtWidgets.QLineEdit(Form)self.lineEdit_3.setObjectName("lineEdit_3")self.horizontalLayout_3.addWidget(self.lineEdit_3)self.verticalLayout.addLayout(self.horizontalLayout_3)self.horizontalLayout_4 = QtWidgets.QHBoxLayout()self.horizontalLayout_4.setObjectName("horizontalLayout_4")self.label_4 = QtWidgets.QLabel(Form)self.label_4.setObjectName("label_4")self.horizontalLayout_4.addWidget(self.label_4)self.lineEdit_4 = QtWidgets.QLineEdit(Form)self.lineEdit_4.setObjectName("lineEdit_4")self.horizontalLayout_4.addWidget(self.lineEdit_4)self.verticalLayout.addLayout(self.horizontalLayout_4)self.horizontalLayout_5 = QtWidgets.QHBoxLayout()self.horizontalLayout_5.setObjectName("horizontalLayout_5")self.label_5 = QtWidgets.QLabel(Form)self.label_5.setObjectName("label_5")self.horizontalLayout_5.addWidget(self.label_5)self.lineEdit_5 = QtWidgets.QLineEdit(Form)self.lineEdit_5.setObjectName("lineEdit_5")self.horizontalLayout_5.addWidget(self.lineEdit_5)self.verticalLayout.addLayout(self.horizontalLayout_5)self.horizontalLayout_7 = QtWidgets.QHBoxLayout()self.horizontalLayout_7.setObjectName("horizontalLayout_7")spacerItem = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout_7.addItem(spacerItem)self.pushButton = QtWidgets.QPushButton(Form)font = QtGui.QFont()font.setBold(True)font.setWeight(75)self.pushButton.setFont(font)self.pushButton.setObjectName("pushButton")self.horizontalLayout_7.addWidget(self.pushButton)spacerItem1 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)self.horizontalLayout_7.addItem(spacerItem1)self.verticalLayout.addLayout(self.horizontalLayout_7)self.retranslateUi(Form)QtCore.QMetaObject.connectSlotsByName(Form)def retranslateUi(self, Form):_translate = QtCore.QCoreApplication.translateForm.setWindowTitle(_translate("Form", "管理员增加用户"))self.label.setText(_translate("Form", "用户名"))self.label_3.setText(_translate("Form", "密码 "))self.label_4.setText(_translate("Form", "手机号"))self.label_5.setText(_translate("Form", "权限 "))self.pushButton.setText(_translate("Form", "确定"))class AddPeopleWindow(QWidget):def __init__(self):super().__init__()self.ui = Ui_Form()self.ui.setupUi(self)self.ui.pushButton.clicked.connect(self.queding)def queding(self):username0 = self.ui.lineEdit.text()passwd0 = self.ui.lineEdit_3.text()phone0 = self.ui.lineEdit_4.text()role0 = self.ui.lineEdit_5.text()now = datetime.now()formatted_now = now.strftime("%Y-%m-%d %H:%M:%S")if all([username0, passwd0, phone0]):connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root',db='music_project', charset='utf8')cursor = connection.cursor()sql0 = "SELECT * FROM users WHERE username = %s AND passwd = %s AND phone_number = %s"cursor.execute(sql0, (username0, passwd0, phone0))records = cursor.fetchall()if not records:sql1 = "INSERT INTO users(username, passwd, phone_number, created_time, role) VALUES(%s, %s, %s, %s, %s)"cursor.execute(sql1, (username0, passwd0, phone0, formatted_now, '1'))connection.commit()QMessageBox.information(None, '注册成功', f'用户 {username0} 注册成功', QMessageBox.Ok)cursor.close()connection.close()returnelse:text1 = '用户已存在'else:text1 = '有空选项'QMessageBox.information(self, '提示', text1, QMessageBox.Ok)class MyWindow(QStackedWidget):def __init__(self):super().__init__()# 创建页面self.denglu_page = Ui_denglu()self.zhuce_page = Ui_zhuce()self.musiclibrary_page=Ui_MusicLibrary()self.managerment_page=Ui_management()# 设置页面的 UIself.denglu_widget = QWidget()self.denglu_page.setupUi(self.denglu_widget)self.zhuce_widget = QWidget()self.zhuce_page.setupUi(self.zhuce_widget)self.musiclibrary_widget = QWidget()self.musiclibrary_page.setupUi(self.musiclibrary_widget)self.managerment_widget = QWidget()self.managerment_page.setupUi(self.managerment_widget)# 添加页面到 QStackedWidgetself.addWidget(self.denglu_widget)self.addWidget(self.zhuce_widget)self.addWidget(self.musiclibrary_widget)self.addWidget(self.managerment_widget)# 绑定按钮的点击事件self.denglu_page.pushButton_2.clicked.connect(self.show_registration_page)self.zhuce_page.pushButton.clicked.connect(self.show_login_page)self.zhuce_page.pushButton_2.clicked.connect(self.zhuce_cancel)self.denglu_page.pushButton.clicked.connect(self.login)self.managerment_page.pushButton.clicked.connect(self.add_people)self.managerment_page.pushButton_2.clicked.connect(self.rm_people)self.managerment_page.pushButton_4.clicked.connect(self.add_music)self.managerment_page.pushButton_5.clicked.connect(self.rm_music)self.managerment_page.pushButton_6.clicked.connect(self.set_music)self.musiclibrary_page.pushButton_3.clicked.connect(self.play_music)self.musiclibrary_page.pushButton.clicked.connect(self.add_music_to_people)self.musiclibrary_page.pushButton_2.clicked.connect(self.rm_music_from_peole)self.musiclibrary_page.pushButton_4.clicked.connect(self.flush_list)# 初始页面self.setCurrentIndex(0)self.data={'username8':'','passwd8':'','id':''}def flush_list1(self):connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='music_project',charset='utf8')cursor = connection.cursor()sql = f"select * from user_favorites where uid = '{self.data['id']}' order by mid"cursor.execute(sql)list1 = cursor.fetchall()self.musiclibrary_page.listWidget_2.clear()row_str = '歌曲\t歌手\tid'self.musiclibrary_page.listWidget_2.addItem(row_str)for row in list1:sql1 = f"select * from music_library where id = '{row[1]}'"cursor.execute(sql1)list0 = cursor.fetchall()for row0 in list0:row_str = f'{row0[1]}\t{row0[2]}\t{row0[0]}'self.musiclibrary_page.listWidget_2.addItem(row_str)cursor.close()connection.close()def flush_list(self):self.flush_list1()connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='music_project',charset='utf8')cursor = connection.cursor()sql = 'select * from music_library order by id'cursor.execute(sql)list1 = cursor.fetchall()self.musiclibrary_page.listWidget.clear()row_str = '歌曲\t歌手\tid'self.musiclibrary_page.listWidget.addItem(row_str)for row in list1:row_str = f'{row[1]}\t{row[2]}\t{row[0]}'self.musiclibrary_page.listWidget.addItem(row_str)cursor.close()connection.close()def play_music(self):self.music_item0 = self.musiclibrary_page.listWidget_2.selectedItems()self.music_item1 = self.musiclibrary_page.listWidget.selectedItems()if not self.music_item0 and not self.music_item1:returnself.music_item = self.music_item0 if self.music_item0 else self.music_item1connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='music_project',charset='utf8')cursor = connection.cursor()for item in self.music_item:text = item.text()parts = text.split()mid = parts[-1] if parts else Nonesql = f"select * from music_library where id = '{mid}'"cursor.execute(sql)connection.commit()result_list = cursor.fetchall()print(result_list[0][-1])path = result_list[0][-1]print(path)music_thread = threading.Thread(target=self.music_start, args=(path,))music_thread.daemon = Truemusic_thread.start()cursor.close()connection.close()def music_start(self,path):pygame.mixer.init()pygame.mixer.music.load(path)pygame.mixer.music.play()while pygame.mixer.music.get_busy(): # 等待音频播放完成passdef add_music_to_people(self):self.music_item = self.musiclibrary_page.listWidget.selectedItems()if not self.music_item:returnconnection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='music_project',charset='utf8')cursor = connection.cursor()for item in self.music_item:text = item.text()parts = text.split()mid = parts[-1]sql1 = f"select * from user_favorites where mid = '{mid}' and uid = '{self.data['id']}'"cursor.execute(sql1)result_list = cursor.fetchall()if not result_list:sql = f"insert into user_favorites(mid,uid) values('{mid}','{self.data['id']}')"cursor.execute(sql)connection.commit()cursor.close()connection.close()def rm_music_from_peole(self):self.music_item = self.musiclibrary_page.listWidget_2.selectedItems()if not self.music_item:returnconnection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='music_project',charset='utf8')cursor = connection.cursor()for item in self.music_item:text = item.text()parts = text.split()mid = parts[-1]print(id)sql = f"DELETE FROM user_favorites WHERE mid = '{mid}' and uid = '{self.data['id']}'"cursor.execute(sql)connection.commit()cursor.close()connection.close()def add_people(self):self.second_window = AddPeopleWindow()self.second_window.show()def rm_people(self):self.music_item = self.managerment_page.listWidget.selectedItems()for item in self.music_item:template1 = item.text()parts = template1.split()id = parts[2]connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='music_project',charset='utf8')cursor = connection.cursor()sql = f"delete from users where id='{id}'"cursor.execute(sql)connection.commit()cursor.close()connection.close()def add_music(self):options = QFileDialog.Options()file_path, _ = QFileDialog.getOpenFileName(self, "选择音乐文件", "", "音乐 (*.mp3)", options=options)if file_path:match = re.search(r'([^\\/]+)-([^\\/]+)-\d+\.mp3$', file_path)if match:singer = match.group(1)song_name = match.group(2)connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root',db='music_project', charset='utf8')cursor = connection.cursor()sql0 = f"SELECT * FROM music_library WHERE artist='{singer}' AND title='{song_name}' AND file_path='{file_path}'"cursor.execute(sql0)result0 = cursor.fetchall()if not result0:sql = f"INSERT INTO music_library(artist, title, file_path) VALUES('{singer}', '{song_name}', '{file_path}')"cursor.execute(sql)connection.commit()cursor.close()connection.close()returnelse:text3='歌曲已存在'else:text3 = "没有匹配到歌手和歌名"else:text3 = '没有选中歌曲'QMessageBox.information(self, '提示', text3, QMessageBox.Ok)def rm_music(self):self.music_item = self.managerment_page.listWidget_2.selectedItems()if not self.music_item:returnconnection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='music_project',charset='utf8')cursor = connection.cursor()for item in self.music_item:text = item.text()parts = text.split()id = parts[-1]print(id)sql = "DELETE FROM music_library WHERE id = %s"cursor.execute(sql, (id,))connection.commit()cursor.close()connection.close()def set_list1(self):connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='music_project',charset='utf8')cursor = connection.cursor()sql = 'select * from users order by id'cursor.execute(sql)list1 = cursor.fetchall()self.managerment_page.listWidget.clear()row_str = '用户名\t密码\tid\t手机号\t创建时间\t\t\t最近登录\t\t\t权限'self.managerment_page.listWidget.addItem(row_str)for row in list1:row_str = f'{row[0]}\t{row[1]}\t{row[2]}\t{row[3]}\t{row[4]}\t{row[5]}\t{row[6]}'self.managerment_page.listWidget.addItem(row_str)cursor.close()connection.close()def set_music(self):self.set_list1()connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='music_project',charset='utf8')cursor = connection.cursor()sql = 'select * from music_library order by id'cursor.execute(sql)list1 = cursor.fetchall()self.managerment_page.listWidget_2.clear()row_str = '歌曲\t歌手\tid'self.managerment_page.listWidget_2.addItem(row_str)for row in list1:row_str = f'{row[1]}\t{row[2]}\t{row[0]}'self.managerment_page.listWidget_2.addItem(row_str)cursor.close()connection.close()# 登录后跳转用户界面/管理员界面def login(self):username2 = self.denglu_page.lineEdit.text()password2 = self.denglu_page.lineEdit_2.text()if username2:if password2:connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root',db='music_project', charset='utf8')cursor = connection.cursor()sql = f"select * from users where username='{username2}' and passwd='{password2}'"cursor.execute(sql)count = cursor.fetchall()self.data['username8'] = username2self.data['passwd8'] = password2self.data['id'] = count[0][2]if count:if count[0][6] == 1:self.setCurrentIndex(2)self.resize(666, 555)else:self.setCurrentIndex(3)self.resize(555, 444)returnelse:text2='账户或密码错误'else:text2 = '密码为空'else:text2 = '用户名为空'QMessageBox.information(None, '登录失败', text2, QMessageBox.Ok)return# 注册取消，返回登录页面def zhuce_cancel(self):self.setCurrentIndex(0)self.resize(500, 300)# 登录跳转注册页面def show_registration_page(self):self.setCurrentIndex(1)self.resize(600, 400)# 注册完，跳转登录页面def show_login_page(self):# 获取注册信息usernames=self.zhuce_page.lineEdit.text()passwds=self.zhuce_page.lineEdit_2.text()passwds2=self.zhuce_page.lineEdit_4.text()sign=self.zhuce_page.lineEdit_3.text()now = datetime.now()formatted_now = now.strftime("%Y-%m-%d %H:%M:%S")# 验证注册信息if usernames:if passwds:if passwds2:if passwds2==passwds:if sign:# 数据库信息验证connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root',db='music_project', charset='utf8')cursor = connection.cursor()sql = f"select * from users where phone_number='{sign}'"cursor.execute(sql)count = cursor.fetchall()if not count:sql1 = f"INSERT into users(username,passwd,phone_number,created_time,role) VALUES('{usernames}','{passwds}','{sign}','{formatted_now}','1')"cursor.execute(sql1)list=cursor.fetchall()print(list)connection.commit()QMessageBox.information(None, '注册成功', '注册成功，请返回登录', QMessageBox.Ok)self.setCurrentIndex(0)self.resize(500, 300)returnelse:text1='用户已存在，不可重复创建,注册失败'else:text1 ='手机号不能为空'else:text1 ='两次密码不一致，请重新输入'else:text1 ='确认密码不能为空'else:text1 ='密码不能为空'else:text1 ='用户名为空'QMessageBox.information(None, '注册失败', text1, QMessageBox.Ok)self.setCurrentIndex(1)self.resize(600, 400)if __name__ == '__main__':app = QApplication(sys.argv)window = MyWindow()window.setWindowIcon(QIcon('E:\杂物\音乐图标.ico'))window.setWindowTitle("音乐播放器")window.resize(500, 300)window.show()sys.exit(app.exec_())
解读代码
首先要掌握MySQL数据库的操作方式，最基本的增删查改
这是存储数据的地方，需要使用SQL语言与数据库交互
然后是pyqt5的知识，知道如何设计界面，还有信号与槽函数
本身并不是很复杂，大部分的逻辑代码都在处理信息方面，比如，注册用户的时候，用户名不能为空，密码不能为空，还需要手机号不同。我设计的是通过手机号排除用户，这样就避免了用户取名的烦恼。在当今这个时代，一个身份证下面手机号的数量是有限的，通过手机号作为唯一标识就跟大数据时代使用身份证作为人的唯一依据。
一开始还想给用户也设计增删查改操作，后来发现改操作，需要再设计一个页面，还不能直接使用注册的页面。
页面的数量又增加一个。
目前的页面有：登录界面，注册界面，用户听歌界面，管理员界面，管理员添加用户界面。
我知道，有很多可以优化的地方，但是目前没必要优化，我只是联系MySQL+pyqt5仅此而已。
如果，你们想要让我优化一下，可以在评论区评论，或者私信，我会尝试一下。
难点
思路很简单，但是遇到的问题一个比一个难解决。
- qt界面跳转功能
一开始想的是一次只展示一个界面，但是还想跳转一下界面，比如在登录界面，我们可以点击注册按钮，跳转到注册界面。注册结束之后，我们点击确定，后台进行数据库比对，返回对应的信息。然后再去登录界面。我不知道如何展示不同的界面，之前我设计的软件都是一个界面用到结束。比如，音乐下载器，打开就是一个音乐下载界面，输入信息，找歌。然后双击下载，还有翻页什么的。都是在一个界面进行的。
用的比较高级的操作就是页面刷新，就是多线程操作。还有动态控件设置。基本上都是基于一个界面实现的。
第一次写多界面的软件。才知道要使用QStackedWidget方法，把页面缓存到堆栈，通过setCurrentIndex(1)这个方法可以跳转到对应的界面。比如这里的5个界面，前四个都是使用页面缓存的方式实现的。下标从0开始，对应不同的页面。
在创建多界面的时候，还有很多要注意的地方：
- 主窗口需要继承类QStackedWidget
- 子界面类需要继承类QWidget
- 跳转界面的时候，要使用setCurrentIndex
- 界面需要先实例化界面，再给每一个界面创建一个QWidget对象，界面类里面还要接收这个QWidget对象。
- 然后添加页面到 QStackedWidget，把刚刚的QWidget对象在添加到QStackedWidget，使用addwidget方法。就像多行文本框一样。也就是后续使用下标跳转页面的缘由。
如果你本来就会界面跳转，那就没什么难点了
写到后面，又发现可以使用多窗口，也就是创建多个主窗口的方式，创建多个界面，也就是后来的管理员添加用户界面
- 界面设计
在设计界面的时候，一开始就想设计三个界面，一个登录，一个注册，一个音乐器本体。
后来没想起来如何动态添加组件。之前使用uniapp的时候，有一个动态更新组件的方法，记混了，设计了好久，没实现。。。
其实是能实现，但是特别麻烦。我们根据用户的权限role，判断是管理员还是普通用户。然后在设计界面的逻辑部分，判断权限。权限不同就显示不同界面。
可能是我设计的界面不够华丽，我感觉与其进行逻辑判断动态更新界面，不如就直接设计两个界面，在登录的时候，从数据库验证身份后，直接就打开对应的界面。也不用逻辑判断了。
也许，如果管理员和用户的界面功能设计的很复杂，又很接近的情况，我会重新考虑这个部分。
- SQL语言设计
因为涉及大量的SQL语言，也是第一次实操，避免不了遗漏或语句错误
为了逻辑代码的严谨，每一个可能出错的地方都要预先模拟一下。其实完全没必要，目前的需求不在界面设计，我听的课也就设计了一个播放界面，只有三个按钮，对应，播放，添加，删除功能，甚至用户登录都是在python的控制台输入的。我是实在看不下去，但也浪费了好多时间。
废话不多说，MySQL板块，到此为止。
下一章NoSQL数据库—Redis数据库。