python嵌入sql数据库

数据库实验三

环境

PyCharm + pymssql + PyQt5 + SQL Server
在PyCharm里安装配置好这两个包，一个用来连SQL数据库，一个是图形界面。

项目创建

这里要特别注意下在PyCharm里新建项目时，要勾选上Inherit global site-packages，要不然pyqt找不到某些路径，不能正常使用。

效果图

源码

import sys
import pymssql
from PyQt5.QtWidgets import QMainWindow, QPushButton, QTextEdit, QLabel, QApplication

class Link():
    def __init__(self):
        self.host = "localhost"
        self.user = "sa"
        self.pwd = "123456"
        self.db = "ST"
    #查询函数，每次查询时调用
    def CheckSQL(self, sql):
        # 与数据库建立连接
        if not self.db:
            raise (NameError, "找不到数据库")
        self.conn = pymssql.connect(host=self.host, user=self.user,
                                    password=self.pwd, database=self.db, charset="utf8")
        cur = self.conn.cursor()
        cur.execute(sql)
        resList = cur.fetchall()
        # 查询完毕后关闭连接
        self.conn.close()
        return resList

class SQLWindow(QMainWindow):

    def __init__(self):
        super().__init__()
        #建立连接
        self.ms = Link()

        self.resize(700, 400)
        self.setWindowTitle("实验3")

        self.text_browser1 = QTextEdit(self)
        self.text_browser2 = QTextEdit(self)
        self.text_browser1.setGeometry(50, 50, 400, 300)
        self.text_browser2.setGeometry(500, 100, 150, 40)

        self.btn1 = QPushButton("查询", self)
        self.btn1.move(500, 200)
        self.btn1.setFixedSize(150, 50)
        self.btn1.clicked.connect(self.Clicked)

        self.lbl1 = QLabel('结果显示列表', self)
        self.lbl1.adjustSize()
        self.lbl1.move(200, 25)
        self.lbl2 = QLabel('输入家庭地址', self)
        self.lbl2.adjustSize()
        self.lbl2.move(500, 50)
        self.setStyleSheet("QLabel{color:rgb(0,0,0,255);"
                           "font-size:18px;font-weight:front;font-family:Microsoft YaHei;}")

    def Clicked(self):
        str = self.text_browser2.toPlainText()
        self.text_browser2.setText('')
        str = "select * from STU where saddress like '%%%%%s%%%%'" % str
        results = self.ms.CheckSQL(str)
        result = []
        for i in results:
           re = []
           for j in i:
              if type(j) == type('55555'):
                 re.append(j.strip())
              else:
                 re.append(j)
           result.append(re)
        self.text_browser1.append(result.__str__())

if __name__ == '__main__':
    app = QApplication(sys.argv)
    exam3 = SQLWindow()
    exam3.show()
    sys.exit(app.exec_())