python mysql操作封装库_Python 封装一个操作mysql的类

import pymysql

class MysqlOperation():

def __init__(self, host, user, pwd, port, db):

self.host = host

self.user = user

self.password = pwd

self.port = port

self.dbname = db

self.conn = None # 连接

self.cur = None # 游标

def open(self):

# 创建连接

self.conn = pymysql.connect(host=self.host, user=self.user, password=self.password, port=self.port,

db=self.dbname, charset='utf8') # 创建连接

self.cur = self.conn.cursor() # 创建游标

def select(self, sql):

'''查询数据'''

self.cur.execute(sql) # 查询数据

return self.cur.fetchall() # 获取结果

def execute(self, sql):

'''执行sql'''

try:

# 执行SQL语句

self.cur.execute(sql)

# 提交事务到数据库执行

self.conn.commit() # 事务是访问和更新数据库的一个程序执行单元

except BaseException as f:

print(f)

self.conn.rollback()

# 返回受影响行数

return self.cur.rowcount

def executemany(self, sql, params):

'''

批量插入数据

:param sql: 插入数据模版, 需要指定列和可替换字符串个数

:param params: 插入所需数据，列表嵌套元组[(1, '张三', '男'),(2, '李四', '女'),]

:return: 影响行数

'''

try:

# sql = "INSERT INTO USER VALUES (%s,%s,%s,%s)" # insert 模版

# params = [(2, 'fighter01', 'admin', 'sanpang'),

# (3, 'fighter02', 'admin', 'sanpang')] # insert数据，

self.cur.executemany(sql, params)

except BaseException as f:

print(f)

self.conn.rollback()

return self.cur.rowcount

def __enter__(self):

self.open()

return self

def __exit__(self, exc_type, exc_val, exc_tb):

'''退出时关闭游标关闭连接'''

self.cur.close()

self.conn.close()

obj = MysqlOperation(host='127.0.0.1', user='root', pwd='123456', port=3306, db='wlj_db') # 对象

with obj as obj:

result = obj.executemany('''INSERT INTO student(gender, class_id, 分) VALUES ('男', '2', '王五');''')

print(result)