Python对接Mysql的DBHelper

今天分享给大家本人常用的python对接Mysql的

DBHelper

import pymysql
from pymysql.cursors import DictCursor


class NoDataBaseException(Exception):
    pass


class DBHelper:
    def __init__(self, database=None, host="localhost", port=3306, username="root", password="test123456"):
        if database:
            self.conn = pymysql.connect(
                host=host,
                port=port,
                user=username,
                password=password,
                database=database
            )
        else:
            raise NoDataBaseException("没有提供正确的数据库")

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        return self.conn.close()

    def _change(self, sql,  *args, isInsert=False):
        cursor = self.conn.cursor()
        try:
            rownum = cursor.execute(sql, args)
            self.conn.commit()
            if isInsert:
                return cursor.lastrowid
            else:
                return rownum
        except Exception as e:
            print("报错了", e)
            self.conn.rollback()
        finally:
            cursor.close()

    def insert(self, sql, *args):
        return self._change(sql, *args, isInsert=True)

    def update(self, sql, *args):
        return self._change(sql, *args)

    def delete(self, sql, *args):
        return self._change(sql, *args)

    def query_list(self, sql, *args):
        cursor = self.conn.cursor()
        try:
            cursor.execute(sql, args)
            result = cursor.fetchall()
            return result
        finally:
            cursor.close()

    def query_one(self, sql, *args):
        cursor = self.conn.cursor()
        try:
            cursor.execute(sql, args)
            result = cursor.fetchone()
            return result
        finally:
            cursor.close()


if __name__ == '__main__':
    w = DBHelper()
    w.quer("")
    db = DBHelper()
    with DBHelper("spider_back") as db:
        # result = db.query_list("select * from stu where gender=%s and address like %s", 1, "%北京%")
        # print(result)
        result = db.delete("delete from stu where sid = %s", 10)
        print(result)
    print("")