Python自动化之封装mysql

封装读取config类

[logging]
name = zangai
level = DEBUG
filename = testlog.log
sh_level = DEBUG
fh_level = INFO

[env]
base_url = http://api.keyou.site:8000

[test_data]
username =
password =
[invest_user]
mobile=
pwd=
[test_admin]
mobile=
pwd=
[mysql]
host = api.lll.com
port = 3306
username =
password =
[mysql_1]
host = 172.16.18.111
port = 3306
username = zangai
password = kFyr1IHDcEmoygcr9OCB
charsets = utf-8

封装读config

import configparser
import os

class Read_Config:
    #定义一个读取配置的类
    def __init__(self, filepath="config.ini"):
        self.cf = configparser.ConfigParser()
        self.cf.read(filepath)

    def get_config_str(self, section, option):
        return self.cf.get(section, option)

    def get_config_boolean(self, section, option):
        return self.cf.getboolean(section, option)

    def get_config_int(self, section, option):
        return self.cf.getint(section, option)

    def get_config_float(self, section, option):
        return self.cf.getfloat(section, option)

if __name__ == '__main__':
    test = Read_Config().get_config_int("mysql_1", "port")
    print(test)
    print(type(test))

封装mysql类

①代码一：

import pymysql
from read_config import Read_Config

sql1 = "SELECT verify_code FROM test_log.cc_sms_log WHERE phone='1713547811';"
data = Read_Config()
host1 = data.get_config_str("mysql_1", "host")
port1 = data.get_config_int("mysql_1", "port")
username1 = data.get_config_str("mysql_1", "username")
password1 = data.get_config_str("mysql_1", "password")

class DBUtil():
    __conn = None
    __cursor = None

    #创建链接
    @classmethod
    def __get_conn(cls):
        if cls.__conn is None:
            cls.__conn = pymysql.connect(host=host1,
                                         port=port1,
                                         user=username1,
                                         password=password1)
        return cls.__conn

    #获取游标
    @classmethod
    def __get_cursor(cls):
        if cls.__cursor is None:
            cls.__cursor = cls.__get_conn().cursor(cursor=pymysql.cursors.DictCursor)
        return cls.__cursor

    #关闭游标
    @classmethod
    def __close_cursor(cls):
        if cls.__cursor:
            cls.__cursor.close()
            cls.__cursor = None

    #关闭链接
    @classmethod
    def __close_conn(cls):
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None

    #执行sql
    @classmethod
    def exe_sql(cls, sql):
        try:
            #获取游标对象
            cursor = cls.__get_cursor()
            #调用游标对象的execute方法，执行sql
            cursor.execute(sql)
            #如果是查询
            if sql.split()[0].lower() =='select':
                #返回所有数据
                return cursor.fetchall()
            else:
                #提交事务
                cls.__conn.commit()
                return cursor.rowcount
        except Exception as e:
            #事务回滚
            cls.__conn.rollback()
            #打印异常信息
            print(e)
        finally:
            #关闭游标
            cls.__close_cursor()
            #关闭链接
            cls.__close_conn()

if __name__ == '__main__':
    res = DBUtil().exe_sql(sql=sql1)
    print(res)

②代码二：

import pymysql
from read_config import Read_Config

sql1 = "SELECT verify_code FROM test_log.cc_sms_log WHERE phone='1713547811';"

class DbSchool:
    def __init__(self):
        self.conn = self.get_conn()  # 连接对象
        self.cursor = self.get_cursor()  # 游标对象

    def get_conn(self):
        """ 获取连接对象 """
        data = Read_Config()
        conn = pymysql.connect(
            host=data.get_config_str("mysql_1", "host"),
            port=data.get_config_int("mysql_1", "port"),
            user=data.get_config_str("mysql_1", "username"),
            passwd=data.get_config_str("mysql_1", "password"),
            # db="python2019",
            # charset="utf8"
        )
        return conn

    def get_cursor(self):
        """获取游标对象"""
        # cursor = None
        # 设置返回的数据类型为字典
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        return cursor

    def close_cursor(self):
        """关闭游标"""
        self.cursor.close()

    def close_conn(self):
        """关闭连接"""
        self.conn.close()

    def exe_sql(self, sql):
        """执行sql"""
        try:
            # 获取游标对象
            cursor = DbSchool().get_cursor()
            # 调用游标对象的execute方法，执行sql
            cursor.execute(sql)
            # 如果是查询
            if sql.split()[0].lower() == 'select':
                # 返回所有数据
                return cursor.fetchall()
            else:
                # 提交事务
                DbSchool().get_conn().commit()
                return cursor.rowcount()
        except Exception as e:
            # 事务回滚
            DbSchool().get_conn().rollback()
            # 打印异常信息
            print(e)
        finally:
            # 关闭游标
            DbSchool().close_cursor()
            # 关闭链接
            DbSchool().close_conn()


if __name__ == '__main__':
    db = DbSchool().exe_sql(sql1)
    print(db)

③代码三：

import pymysql
from common.handle_config import conf


class HandleDB:

    def __init__(self, host, port, username, password):
        self.host = host
        self.port = port
        self.username = username
        self.password = password

    def connect(self):
        # 连接数据库
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.username,
            password=self.password
        )


    def get_cursor(self):
        # 获取游标
        self.cursor = self.conn.cursor()
        return self.cursor

    def close(self):
        # 关闭连接
        self.cursor.close()
        self.conn.close()

    def select_one(self, sql, **value):
        # 返回一条数据
        result = None
        try:
            self.connect()
            cursor = self.get_cursor()
            cursor.execute(sql, **value)
            result = cursor.fetchone()
            self.close()
        except Exception as e:
            print(e)
        return result

    def select_all(self, sql, **value):
        # 返回多条数据
        list_data = ()
        try:
            self.connect()
            cursor = self.get_cursor()
            cursor.execute(sql, *value)
            list_data = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print(e)
        return list_data

    def update(self, sql, **value):
        # 修改数据
        try:
            self.connect()
            cursor = self.get_cursor()
            cursor.execute(sql, *value)
            self.conn.commit()
            # self.close()
        except Exception as e:
            self.conn.rollback()
            print(e)
        finally:
            '''关闭游标、连接'''
            self.cursor.close()
            self.close()

    def delete(self, sql, **value):
        # 删除数据
        try:
            self.connect()
            cursor = self.get_cursor()
            cursor.execute(sql, *value)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            print(e)
        finally:
            '''关闭游标、连接'''
            self.cursor.close()
            self.close()


db = HandleDB(host=conf.read_str('mysql', 'host'),
              port=conf.read_int('mysql', 'port'),
              username=conf.read_str('mysql', 'username'),
              password=conf.read_str('mysql', 'password')
              )

if __name__ == '__main__':
    sql = "SELECT * FROM test;"
    res = db.select_one(sql)
    print(res, type(res))