面向对象实战 数据库面向过程代码封装成面向对象代码

import pymysql,xlwt
import traceback,hashlib
MYSQL_INFO = {
    'host':'xx.xx.x.xx',
    'user':'jxz',
    'password':'123456',
    'db':'jxz',
    'charset':'utf8',
    'autocommit':True
}

def execute_sql(sql):
    conn = pymysql.connect(**MYSQL_INFO) #xx=xxx,xx=xx,
    cur = conn.cursor(pymysql.cursors.DictCursor)
    try:
        cur.execute(sql)
    except:
        print('sql不正确')
        traceback.print_exc()#具体定位到报错的信息
    else:
        return cur.fetchall() #None  []
    finally:
        conn.close()
        cur.close()

def write_excel(name,data):
    book = xlwt.Workbook()
    sheet = book.add_sheet('sheet1')
    for index, key in enumerate(data[0]):  # 写表头
        sheet.write(0, index, key)
    for row, item in enumerate(data, 1):  # 写数据
        for col, value in enumerate(item.values()):
            sheet.write(row, col, value)

    book.save(name + '.xls')


def my_md5(s):#md5加密
    s = str(s)
    s = s.encode()
    m = hashlib.md5(s)  # bytes,不可逆
    result = m.hexdigest()
    return result


封装成面向对象的代码 

import pymysql  #把数据库写成面向对象
MYSQL_INFO = {
    'host':'xxx.xx.x.xx',
    'user':'jxz',
    'password':'123456',
    'db':'jxz',
    'charset':'utf8',
    'autocommit':True#commit 自动提交
}
class Mysql():
    def __init__(self,host,user,password,db,autocommit=True,charset='utf8'):
        self.coon=pymysql.connect(host=host,user=user,password=password,db=db,charset=charset,autocommit=autocommit)
        self.cursor=self.coon.cursor()
    def fetchall(self,sql):
        self.cursor.execute(sql)#执行sql
        return self.cursor.fetchall()
    def fetone(self,sql):
        self.cursor.execute(sql)  # 执行sql
        return self.cursor.fetchone()
    def close(self):
        self.coon.close()
if __name__=='__main__':
    ces=Mysql(**MYSQL_INFO)
    ces.fetchall('select * from app_myuser;')
加一些异常的代码

import pymysql
from loguru import logger
import traceback

MYSQL_INFO = {
'host':'xxx.xxx.3.40',
'user':'jxz',
'password':'123456',
'db':'jxz',
'charset':'utf8',
'autocommit':True
}
class MySQL:
def __init__(self,host,user,password,db,charset='utf8',autocommit=True):
self.conn = pymysql.connect(user=user,host=host,password=password,db=db,charset=charset,autocommit=autocommit)
self.cursor = self.conn.cursor()

def __del__(self):
self.__close()

def execute(self,sql):
try:#加一些异常的代码
self.cursor.execute(sql)
except Exception:
logger.error('sql执行出错，sql语句是{}',sql)
logger.error(traceback.format_exc())

def fetchall(self,sql):
self.execute(sql)
return self.cursor.fetchall()

def fetchone(self,sql):
self.execute(sql)
return self.cursor.fetchone()

def bak_db(self):
pass

def __close(self):
self.cursor.close()
self.conn.close()

if __name__ == '__main__':
my = MySQL(**MYSQL_INFO)
print(my.fetchall('select * from app_myuser;'))
my.fetchone('select * from app_myuser where id=1;')
my.fetchone('select * from app_myuser where id=1;')
my.fetchone('select * from app_myuser where id=1;')

使用面向对象写出来的效果更加清晰了，以后用MySQ操作就可以了，比如备份数据库