PyQt5连接MySQL

ゝ一纸荒年。 2022-05-29 12:55 97阅读 0赞

> 首先声明，以下的程序是在前辈的基础上稍作修改，适应PyQt5以及做一些解释

## 先展示先PyQt5连接MySql的效果 ##

![这里写图片描述][70]

> 这里连的是我本机的mysql当然是可以连接远程的服务器的

## 1 ，布局 ##

> 布局我们就不多做解释了，底部连接有源码，大家可以自己看

--------------------

## 2 ，与MySql建立连接 ##

![这里写图片描述][70 1]

> 想必这里不用我多做解释，连接mysql的地址，端口之类的信息就可以连接，如果你不是本地mysql你可以将localhost换成你远程服务器的IP地址，完成后调用 **cursor()**生成一个游标，如果你不理解什么叫游标可以理解他为一个集合，但是我强调一点，MySQLdb这个模块不支持python3以上了，建议开发者使用pymysql，效果是一样，使用方法也基本上都是类似的  
> ![这里写图片描述][70 2]

## 3 ，信号和槽建立连接 ##

![这里写图片描述][70 3]

> pyqt1\_clicked1 是退出按钮的信号  
> pyqt2\_clicked2 是查询按钮的信号

## 4，查询按钮的内容 ##

def buttonTest(self):
            temp_sqlstring = self.sqlstring
            is_first = True
            if self.check_Sid.isChecked():
                mystr = self.Sid.text()
                if is_first:
                    is_first = False
                    if mystr.find("%") == -1:
                        temp_sqlstring += "Sid = '" + self.Sid.text() + "'"
                    else:
                        temp_sqlstring += "Sid like '" + self.Sid.text() + "'"
                else:
                    if mystr.find("%") == -1:
                        temp_sqlstring += " and Sid = '" + self.Sid.text() + "'"
                    else:
                        temp_sqlstring += " and Sid like '" + self.Sid.text() + "'"
    
            if self.check_Sname.isChecked():
                if is_first:
                    mystr = self.Sname.text()
                    is_first = False
                    if mystr.find("%") == -1:
                        temp_sqlstring += "Sname = '" + self.Sname.text() + "'"
                    else:
                        temp_sqlstring += "Sname like '" + self.Sname.text() + "'"
                else:
                    if mystr.find("%") == -1:
                        temp_sqlstring += " and Sname = '" + self.Sname.text() + "'"
                    else:
                        temp_sqlstring += " and Sname like '" + self.Sname.text() + "'"
    
            if self.check_Sage.isChecked():
                if is_first:
                    is_first = False
                    temp_sqlstring += "Sage >= " + self.first_Sage.text() + \
                                      " and Sage <= " + self.last_Sage.text()
                else:
                    temp_sqlstring += " and Sage >= " + self.first_Sage.text() + \
                                      " and Sage <= " + self.last_Sage.text()
    
            if self.check_Ssex.isChecked():
                if is_first:
                    is_first = False
                    temp_sqlstring += "Ssex = '" + self.Ssex.text() + "'"
                else:
                    temp_sqlstring += " and Ssex = '" + self.Ssex.text() + "'"
    
            if self.check_Sclass.isChecked():
                if is_first:
                    mystr = self.Sclass.text()
                    is_first = False
                    if mystr.find("%") == -1:
                        temp_sqlstring += "Sclass = '" + self.Sclass.text() + "'"
                    else:
                        temp_sqlstring += "Sclass like '" + self.Sclass.text() + "'"
                else:
                    if mystr.find("%") == -1:
                        temp_sqlstring += " and Sclass = '" + self.Sclass.text() + "'"
                    else:
                        temp_sqlstring += " and Sclass like '" + self.Sclass.text() + "'"
    
            if self.check_Sdept.isChecked():
                if is_first:
                    mystr = self.Sdept.text()
                    is_first = False
                    if mystr.find("%") == -1:
                        temp_sqlstring += "Sdept = '" + self.Sdept.text() + "'"
                    else:
                        temp_sqlstring += "Sdept like '" + self.Sdept.text() + "'"
                else:
                    if mystr.find("%") == -1:
                        temp_sqlstring += " and Sdept = '" + self.Sdept.text() + "'"
                    else:
                        temp_sqlstring += " and Sdept like '" + self.Sdept.text() + "'"
    
            if self.check_Saddr.isChecked():
                if is_first:
                    mystr = self.Saddr.text()
                    is_first = False
                    if mystr.find("%") == -1:
                        temp_sqlstring += "Saddr = '" + self.Saddr.text() + "'"
                    else:
                        temp_sqlstring += " and Saddr like '" + self.Saddr.text() + "'"
                else:
                    if mystr.find("%") == -1:
                        temp_sqlstring += " and Saddr = '" + self.Saddr.text() + "'"
                    else:
                        temp_sqlstring += " and Saddr like '" + self.Saddr.text() + "'"
    
            self.result_out.clearContents()  # 每一次查询时清除表格中信息
            if not (is_first):
                print(temp_sqlstring)
                self.cur.execute(temp_sqlstring)
                k = 0
                for i in self.cur:
                    print("----------",i)
                    w = 0
                    for j in i:
                        # 这里是将int类型转成string类型，方便后面文本设置
                        if type(j) == int:
                            newItem =  QTableWidgetItem(str(j))
    
                        else:
                            newItem =  QTableWidgetItem(j)
                        # 根据循环标签一次对table中的格子进行设置
                        self.result_out.setItem(k, w, newItem)
                        w += 1
                    k += 1
    
            self.sql_out.setText("")
            self.sql_out.append(temp_sqlstring)
            print("find button pressed")

> 这是点击查询按钮后里面需要执行的代码，简单一点就是根据选择拼接sql语句最后进行执行，但是这里我们并不需要去仔细了解，可以把代码下载下来仔细研究

## 5，往表格里面填数据 ##

![这里写图片描述][70 4]

### github源码地址 ###

[https://github.com/jeekMic/py\_mysql.git][https_github.com_jeekMic_py_mysql.git]

[70]: /images/20220529/f9c73e5f86724406aeb29bd523b71012.png
[70 1]: /images/20220529/b84081361809485db0fa8e694bb4775a.png
[70 2]: /images/20220529/4617a2341a034fb198d4984d00e27cb8.png
[70 3]: /images/20220529/9c8630d7a6474f00b47a40da13d21d9d.png
[70 4]: /images/20220529/f902dd946a3a4a4fbe3918a209419a69.png
[https_github.com_jeekMic_py_mysql.git]: https://github.com/jeekMic/py_mysql.git