python3 写的MySQL 工具类

1 数据库配置文件

mysql_config = {
    "host": "localhost",
    "port": 3306,
    "userName": "root",
    "password": "123456",
    "dbName": "demo",
    "charsets": "UTF8"
}

2 核心工具类

#!/usr/bin/python
# -*- coding:utf-8 -*-

import pymysql
import json
from DB import config


class DBUtil:
    """mysql util"""
    db = None
    cursor = None

    def __init__(self):
        self.host = config.mysql_config['host']
        self.port = config.mysql_config['port']
        self.userName = config.mysql_config['userName']
        self.password = config.mysql_config['password']
        self.dbName = config.mysql_config['dbName']
        self.charsets = config.mysql_config['charsets']
        print("配置文件：" + json.dumps(config.mysql_config))

    # 链接数据库
    def get_con(self):
        """ 获取conn """
        self.db = pymysql.Connect(
            host=self.host,
            port=self.port,
            user=self.userName,
            passwd=self.password,
            db=self.dbName,
            charset=self.charsets
        )
        self.cursor = self.db.cursor()

    # 关闭链接
    def close(self):
        self.cursor.close()
        self.db.close()

    # 主键查询数据
    def get_one(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 查询列表数据
    def get_all(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 插入数据
    def __insert(self, sql):
        count = 0
        try:
            self.get_con()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except Exception as e:
            print("操作失败！" + str(e))
            self.db.rollback()
        return count

    # 保存数据
    def save(self, sql):
        return self.__insert(sql)

    # 更新数据
    def update(self, sql):
        return self.__insert(sql)

    # 删除数据
    def delete(self, sql):
        return self.__insert(sql)

3 主程序入口

#!/usr/bin/python

from DB import MySQLUtils
import json
if __name__ == '__main__':
    """主程序入口"""
    dbUtil = MySQLUtils.DBUtil()
    print("对象实例后的属性："+json.dumps(dbUtil.__dict__))

    # 主键查询
    sql = "select * from user where id = 1 "
    print("get_one执行结果：" + str(MySQLUtils.DBUtil.get_one(dbUtil, sql)))

    # 列表查询
    sql = "select * from user where 1 = 1 "
    print("get_all执行结果：" + str(MySQLUtils.DBUtil.get_all(dbUtil, sql)))

    # 插入
    sql = "INSERT INTO user(`id`, `name`) VALUES (8, 'admin');"
    print("save执行结果：" + str(MySQLUtils.DBUtil.save(dbUtil, sql)))

    # 更新
    sql = "update user set name= 'admin1' where id =6 "
    print("update执行结果：" + str(MySQLUtils.DBUtil.update(dbUtil, sql)))

    # 删除
    sql = "delete from user where id = 5"
    print("delete执行结果：" + str(MySQLUtils.DBUtil.delete(dbUtil, sql)))

5 执行结果

/usr/local/bin/python3.8 /Users/zhangheng/Downloads/projects/python_projects/demo.py
配置文件：{"host": "localhost", "port": 3306, "userName": "root", "password": "123456", "dbName": "demo", "charsets": "UTF8"}
对象实例后的属性：{"host": "localhost", "port": 3306, "userName": "root", "password": "123456", "dbName": "demo", "charsets": "UTF8"}
get_one执行结果：(1, 'admin')
get_all执行结果：((1, 'admin'), (2, 'admin'), (3, 'admin'), (4, 'admin'), (6, 'admin1'), (7, 'admin'))
save执行结果：1
update执行结果：0
delete执行结果：0

Process finished with exit code 0

6 贴一张项目结构图

7

纯手写 欢迎大家使用