pymysql 简单封装使用

import pymysql


class MysqlTools:
    def __init__(self, host, user, passwd, port, database):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.port = port
        self.database = database

        self.db = None
        self.cursor = None

    def db_init(self):
        try:
            self.db = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd, port=self.port,
                                      database=self.database)
            self.cursor = self.db.cursor()
            return True, 'OK'
        except Exception as e:
            return False, e

    def create_tb(self, sql):
        try:
            self.cursor.execute(sql)
            return True
        except Exception as e:
            print(e)
            return False

    def insert(self, sql, value):
        # sql = "insert into EMPLOYEE values (%s,%s,%s,%s,%s);"
        # value = ("zhang", "si", 28, 0, 1000.00)
        try:
            self.cursor.execute(sql, value)
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            return False

    def insert_many(self, sql, value):
        # sql = "insert into EMPLOYEE values (%s,%s,%s,%s,%s);"
        # value = [("ax", "si", 28, 0, 1000.00),("asdf", "asdf", 12, 0, 1000.00)]
        try:
            self.cursor.executemany(sql, value)
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            return False

    def query(self, sql):
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
            return True, data
        except Exception as e:
            return False, None

    def delete(self, sql):
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            return False

    def update(self, sql):
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            return False


if __name__ == '__main__':

    sql_client = MysqlTools('localhost', 'root', 'root', 3306, 'user')
    ret, info = sql_client.db_init()
    if not ret:
        exit(-1)

    # 创建数据库
    # sql = """CREATE TABLE testTB (
    #          FIRST_NAME  CHAR(20) NOT NULL,
    #          LAST_NAME  CHAR(20),
    #          AGE INT,  
    #          SEX CHAR(1),
    #          INCOME FLOAT )"""
    # ret = sql_client.create_tb(sql)
    # print(ret)

    # 插入单条数据
    # sql = "insert into EMPLOYEE values (%s,%s,%s,%s,%s);"
    # value = ("zhg", "si", 28, 0, 1000.00)
    # ret = sql_client.insert(sql, value)
    # print(ret)

    # 批量插入
    # sql = "insert into EMPLOYEE values (%s,%s,%s,%s,%s);"
    # value = [("ax", "si", 28, 0, 1000.00), ("asdf", "asdf", 12, 0, 1000.00)]
    # ret = sql_client.insert_many(sql, value)
    # print(ret)

    # 查询
    # sql = "select * from EMPLOYEE;"
    # ret = sql_client.query(sql)
    # print(ret)

    # 删除
    # sql = "delete from employee where age=12;"
    # ret = sql_client.delete(sql)
    # print(ret)

    # 更新
    # sql = "update employee set sex=0 where age=28;"
    # ret = sql_client.update(sql)
    # print(ret)