library/util/db_helper.py

#!/usr/bin/python3.6
# -*- coding: utf-8 -*-
# @Author  : lijunliang
# @Email   : lijunliang@topnet.net.cn
# @File    : db_helper.py
# @Software: PyCharm

import pymysql
from loguru import logger


class DBHelper(object):
    def __init__(self, config):
        self.__host = config.get("host", "")
        self.__port = int(config.get("port", ""))
        self.__user = config.get("user", "")
        self.__password = config.get("password", "")
        self.__db = config.get("db", "")
        self.__charset = config.get("charset", "")
        self.link_db()

    def link_db(self):
        self.db = pymysql.connect(host=self.__host, user=self.__user,
                                  password=self.__password, db=self.__db,
                                  port=self.__port, charset=self.__charset)

    def execute_sql(self, sql: str):
        try:
            with self.db.cursor() as cursor:
                cursor.execute(sql)
                self.db.commit()
                return True
        except Exception as e:
            self.db.rollback()
            logger.warning("{}-->执行失败-->{}".format(sql, e))
            return False

    def search(self, sql: str):
        '''
        执行sql搜索
        :param sql:
        :return:
        '''
        with self.db.cursor() as cursor:
            cursor.execute(sql)
            s_ret = cursor.fetchall()
            return s_ret

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


def create_update_sql():
    update = r"UPDATE files SET category = 'dog' WHERE id=1"


def create_delete_sql():
    delte = """DELETE FROM files WHERE ID =1"""


if __name__ == '__main__':
    from module.read_config import read_ini
    import datetime

    config = read_ini("../data/config.ini")
    print(config)
    db = DBHelper(config["mysql_config"])
    sql = r'select * from files where id=1'
    update = r"UPDATE files SET category = 'dog' WHERE id=1"
    delte = """DELETE FROM files WHERE ID =1"""
    # db.search(sql)
    db.execute_sql(delte)