PyQt6实战3--sql查询器

由于mysql的驱动，qt不是默认带的，所以要使用mysql的话可以自行解决（答案在《PyQt6快速开发与实战》 第384页）。为了让大家顺利跑起来，我使用了sqlite，免mysql的安装

功能：

1.sql查询器，输入sql，点击查询

运行：

1.sqlite 安装教程 : https://doc.yonyoucloud.com/doc/wiki/project/sqlite/sqlite-installation.html

最新的版的压缩包，解压之后可以直接使用，不需要make过程，解压之后就有sqlite3可执行文件了

2.创建一个db后缀的文件，替换掉我的那个文件地址即可跑起来

刚开始表是空的，你需要先创建表，然后写入几条语句接口

初始化数据，先运行SqlQuery的main方法

3.执行DBQuery的main函数即可

4.先点击登录，会自定连接刚才配置的db文件，然后表那会显示table列表

运行效果

from PyQt6.QtWidgets import *
from PyQt6.QtCore import *
from PyQt6.QtGui import *

import sys
import os

from SqlQuery import SqlQuery

import pathlib
path_top = pathlib.Path(__file__).parent.parent
if str(path_top) not in sys.path:
    sys.path.append(str(path_top))



class DBQuery(QWidget):
    def __init__(self):
        super().__init__()
        self.initUI()
        self.initAction()
        

    def initUI(self):

        self.setWindowTitle('DBQuery')
        self.setGeometry(200, 200, 800, 600)

        layout = QHBoxLayout()

        leftlayout = QVBoxLayout()
        self.login_btn = QPushButton("登录")
        leftlayout.addWidget(self.login_btn)
        sql_label = QLabel("数据库表:")
        self.table_list = QListWidget()
        self.table_list.addItems(["表1", "表2", "表3"])
        leftlayout.addWidget(sql_label)
        leftlayout.addWidget(self.table_list)
        layout.addLayout(leftlayout, stretch=1)

        
        rightTopLayout = QVBoxLayout()
        rightTopLayout.addWidget(QLabel("输入sql:"))
        self.sql = QTextEdit()
        rightTopLayout.addWidget(self.sql, stretch=2)
        self.queryLayout = QHBoxLayout()
        self.query_button = QPushButton("查询")
        self.queryLayout.addWidget(self.query_button)
        self.write_button = QPushButton("写入")
        self.queryLayout.addWidget(self.write_button)

        self.queryLayout.setAlignment(Qt.AlignmentFlag.AlignCenter)
        self.result_table = QTableView()
        rightTopLayout.addLayout(self.queryLayout)
        rightTopLayout.addWidget(self.result_table, stretch=4)

        layout.addLayout(rightTopLayout, stretch=3)
        self.setLayout(layout)

    def initAction(self):
        # 登录
        self.login_btn.clicked.connect(self.loginClicked)
        # 查询
        self.query_button.clicked.connect(self.queryClicked)
        # 写入
        self.write_button.clicked.connect(self.writeClicked)
        

    
    def loginClicked(self):
        self.dbquery = SqlQuery()
        self.dbquery.connect_db()
        self.table_list.clear()
        self.table_list.addItems(self.dbquery.get_tables())


    def queryClicked(self):
        sql = self.sql.toPlainText()
        if sql:
            if sql.startswith("select"):
                self.dbquery.query(sql)
                self.result_table.setModel(self.dbquery.queryModel)
                # self.result_table.show()
            else:
                QMessageBox.warning(self, "警告", "请输入select语句")
        else :
            QMessageBox.warning(self, "警告", "请输入sql")
        

    def writeClicked(self):
        sql = self.sql.toPlainText()
        if sql:
            if sql.startswith("select"):
                QMessageBox.warning(self, "警告", "select语句,请选择查询按钮")
            else:
                try:
                    self.dbquery.write(sql)
                    QMessageBox.information(self, "警告", "成功")
                except:
                    QMessageBox.warning(self, "警告", "写入失败")

        else :
            QMessageBox.warning(self, "警告", "请输入sql")





if __name__ == '__main__':
    app = QApplication(sys.argv)
    window = DBQuery()
    window.show()
    sys.exit(app.exec())

from PyQt6.QtWidgets import *
from PyQt6.QtCore import *
from PyQt6.QtGui import *
from PyQt6.QtSql import *

'''
1.sqlite 安装教程 : https://doc.yonyoucloud.com/doc/wiki/project/sqlite/sqlite-installation.html
最新的版的压缩包，解压之后可以直接使用，不需要make过程，解压之后就有sqlite3可执行文件了

2.创建一个db后缀的文件，替换掉我的那个文件地址即可跑起来
'''
class SqlQuery():
    def __init__(self):
        pass

    def connect_db(self):
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName('/Users/zhaojun/develop/sqlit/dbquery.db')
        isOpen = db.open()
        if(isOpen):
            print("数据库连接成功")
            self.db = db
            self.writeModel = QSqlQuery(db=self.db)
            self.queryModel = QSqlQueryModel()
        else:
            print("数据库连接失败")
        return db
    
    def query(self, sql):
        self.queryModel.setQuery(sql)
    
    def write(self, sql):
        self.writeModel.exec(sql)
    
    def get_tables(self):
        return self.db.tables()

    def close_db(self):
        self.db.close()


if __name__ == "__main__":
    sqlQuery = SqlQuery()
    db = sqlQuery.connect_db()
    queryModel = QSqlQuery()
    queryModel.exec("create table people(id int primary key, name varchar(20), age int)")
    queryModel.exec("insert into people(id, name, age) values(1, 'zhaojun', 20)")
    queryModel.exec("insert into people(id, name, age) values(2, 'zhaojun2', 20)")
    queryModel.exec("insert into people(id, name, age) values(3, 'zhaojun3', 20)")
    queryModel.exec("select * from people")
    while queryModel.next():
        print(queryModel.value(0), queryModel.value(1), queryModel.value(2))
    
    sqlQuery.close_db()
    

代码地址

GitHub - chunlaiqingke/Tiny-Tool

公众号：