python pyqt5 pymysql连接mysql数据库(简单便捷)

本文中，使用到的工具有：Pycharm，MySQL 8.0.14

什么是 PyMySQL？
PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中则使用 mysqldb。

一、环境准备
1、安装pymysql：
进行Python连接mysql数据库之前，需要先安装一下pymysql。

直接在终端执行下面的命令即可。(在此处我将指定1.0.3版本)

pip install pymysql==1.0.3

2、查询安装：

下载完成后，在终端输入 pip list 即可看到下图：

pip list

可以看到我们的PyMySQL是1.0.2版本的。

二、代码编写，连接数据库

创建文件DBManager.py

#!/usr/bin/python3
from PyQt5.QtSql import QSqlQuery, QSqlDatabase, QSqlQueryModel
import pymysql
from pymysql import cursors


class DBManager():
    def __init__(self, conname='default', hostname='127.0.0.1', port='3306', user='root', password='123456'):
        self.conname = conname
        self.hostname = hostname
        if port == '':
            port = 3306
        self.port = int(port)
        self.user = user
        self.password = password

        if (QSqlDatabase.contains(self.conname)):
            self.db = QSqlDatabase.database(self.conname)
        else:
            self.db = QSqlDatabase.addDatabase("QMYSQL", self.conname)
        self.query = QSqlQuery(self.db)

    # 测试数据库链接是否成功
    # if error == 'QSqlDatabase: QMYSQL driver not loaded QSqlDatabase: available drivers: QSQLITE QMYSQL QMYSQL3 QPSQL QPSQL7':
    # ldd /home/ji/.local/lib/python3.6/site-packages/PyQt5/Qt/plugins/sqldrivers/libqsqlmysql.so
    #     sudo apt-get install libmysqlclient18
    def testConnect(self):
        if (self.openDB()):
            re = "Success"
        else:
            re = "Failed !\n"
            re += self.db.lastError().text()
        return re

    def openDB(self):
        try:
            self.db = pymysql.connect(
                host=self.hostname,
                user=self.user,
                password=self.password,
                # database=self.database,
                port=self.port,
                charset='utf8',
                autocommit=True
            )
            return True
        except Exception as e:
            print("Error:", e)

    # 使用此数据库连接返回json格式数据
    def openDB_json(self):
        self.db = pymysql.connect(
            host=self.hostname,
            user=self.user,
            password=self.password,
            # database=self.database,
            port=self.port,
            charset='utf8',
            cursorclass=cursors.DictCursor,
            autocommit=True
        )
        return True

    def closeDB(self):
        if (self.db.isOpen()):
            self.db.close()

    def showDBs(self):
        databases_list = []
        self.openDB()
        cur = self.db.cursor()
        cur.execute("show databases")
        re = cur.fetchall()
        for i in range(len(re)):
            databases_list.append(re[i][0])

        return databases_list

    def getDBList(self, table, header, where='1=1', page=1, pagesize=100):
        self.openDB_json()
        cur = self.db.cursor(pymysql.cursors.DictCursor)
        cur.execute('select * from information_schema.%s where %s limit %s , %s' % (
        table, where, (page - 1) * pagesize, pagesize))
        # cur.execute('select * from information_schema.%s where %s limit 3' % (table,where))
        re = cur.fetchall()

        print(re)
        return re

    def get_Table_Data(self, database, table):
        self.openDB_json()
        cur = self.db.cursor(pymysql.cursors.DictCursor)
        sql = '''
select * 
from `''' + database + '`.`' + table + '''`
limit 100
        '''
        print(sql)
        cur.execute(sql)
        re = cur.fetchall()

        return re


if __name__ == "__main__":
    my_db = DBManager();
    my_db.testConnect()
    re = my_db.showDBs()
    print(re)
    my_db.closeDB()

1.可直接在Pycharm编辑器上点运行获得以下结果

2.cmd下执行 python d:\python\DBManager.py

执行结果：