# -*- coding:utf-8 -*-
import pymysql
import contextlib
#定义上下文管理器，连接后自动关闭连接
@contextlib.contextmanagerdef 
mysql(host='localhost', port=3306, user='root', passwd='', db='testdb',charset='utf8'):
    # 创建连接
    conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
    # 创建游标
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    try:
        yield cursor
    except Exception as e:
        #事务失败时回滚
        conn.rollback()
        print('事务处理失败:', e)
    else:
        # 提交，不然无法保存新建或者修改的数据
        conn.commit()
        print('事务处理成功')
    finally:
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        print('关闭所有连接')

#查
with mysql() as cursor:
    sql = "select * from tb1"
    cursor.execute(sql)
    print(cursor.fetchall())

#增
with mysql() as cursor:
    sql = "INSERT INTO tb1 (name,pwd) VALUES ('%s','%s')"%('ZHANG','123456')
    # sql = "INSERT INTO tb1 (name,pwd) VALUES ('ZHANG','123456')"
    cursor.execute(sql)

#改
with mysql() as cursor:
    sql = "UPDATE TB1 SET NAME='ZHANG1' WHERE UID=1"
    cursor.execute(sql)

#删
with mysql() as cursor:
    sql = "DELETE FROM TB1 WHERE UID=1"
    cursor.execute(sql)