mysql会话上下文_通过上下文管理器写一个mysql的连接，通过with管理

需求: 通过上下文管理器写一个mysql的连接，通过with管理

import pymysql

class MySQL:

def __init__(self, *args, **kwargs):

self.conn = pymysql.connect(*args, **kwargs)

self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

def __enter__(self):

return self

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

self.cursor.close()

self.conn.close()

if exc_type:

print(exc_type)

print(exc_val)

print(exc_tb)

return True

with MySQL(

host='127.0.0.1',

port=3306,

user='root',

password='123',

database='db1',

charset='utf8',

# autocommit=True

) as self:

# 查

'''

sql = 'select username from user where password=123'

affected_rows = self.cursor.execute(sql)

print('affected_rows:', affected_rows)

fetchall = self.cursor.fetchall()

print(fetchall) # [{'username': 'yang'}, {'username': 'egon'}]

self.cursor.scroll(-2, 'relative')

fetchone = self.cursor.fetchone()

print(fetchone) # {'username': 'yang'}

# self.cursor.scroll(-1)

self.cursor.scroll(0, 'absolute')

fetchmany = self.cursor.fetchmany(2)

print(fetchmany) # [{'username': 'yang'}, {'username': 'egon'}]

'''

# 增

"""

user_info = [

('lqz', '222'),

('wxx', '333'),

]

sql = 'insert into user(username, password) values(%s, %s)'

try:

affected_rows = self.cursor.executemany(sql, user_info)

print('affected_rows:', affected_rows)

self.conn.commit() # 提交

except Exception as e:

print(e)

self.conn.rollback()

self.cursor.execute('select * from user')

fetchall = self.cursor.fetchall()

print(fetchall)

'''

[{'id': 1, 'username': 'yang', 'password': '123'},

{'id': 2, 'username': 'egon', 'password': '123'},

{'id': 17, 'username': 'lqz', 'password': '222'},

{'id': 18, 'username': 'wxx', 'password': '333'}]

'''

"""

# 删

"""

sql = 'delete from user where id=18'

try:

self.cursor.execute(sql)

self.conn.commit() # 提交

except Exception as e:

print(e)

self.conn.rollback()

self.cursor.execute('select * from user')

fetchall = self.cursor.fetchall()

print(fetchall)

'''

[{'id': 1, 'username': 'yang', 'password': '123'},

{'id': 2, 'username': 'egon', 'password': '123'},

{'id': 17, 'username': 'lqz', 'password': '222'}]

'''

"""

# 改

"""

sql = 'update user set username="egonDSB" where id=2'

try:

self.cursor.execute(sql)

self.conn.commit() # 提交

except Exception as e:

print(e)

self.conn.rollback()

self.cursor.execute('select * from user')

fetchall = self.cursor.fetchall()

print(fetchall)

'''

[{'id': 1, 'username': 'yang', 'password': '123'},

{'id': 2, 'username': 'egonDSB', 'password': '123'},

{'id': 17, 'username': 'lqz', 'password': '222'}]

'''

"""