# demo03_pymysql.py
import pymysql
conn = pymysql.connect(  # 1. 与数据库服务器建立连接
    host="127.0.0.1",    # 主机地址
    port=3306,           # 数字类型
    user="root",
    db="tedu1",          # 操作的数据库名称
    charset="utf8"       # 字符集
)
cur = conn.cursor()  # 2. 创建游标
# 3. 数据库操作
# ====================3.1 创建表=======================
# create_bm="CREATE TABLE bm(bm_id int,bm_name varchar(30));"
# cur.execute(create_bm)
# ====================3.2 插入数据======================
# insert_bm = "INSERT INTO bm VALUES(%s, %s);"
# cur.execute(insert_bm, (1, "DEV"))  # 单条处理
# cur.executemany(insert_bm, [  # 批量处理
#     (2, "OPS"), (3, "TEST"), (4, "MARKET"), (5, "HR")
# ])
# ====================3.3 查询数据======================
# select_bm = "SELECT * FROM bm;"
# cur.execute(select_bm)
# print(cur.fetchone())  # 抓取结果集中的第一条数据 1
# print(cur.fetchmany(2))  # 接着抓取两条数据 2 3
# print(cur.fetchall())  # 抓取剩余的所有数据 4 5
# ====================3.4 更新数据======================
# update_bm = "UPDATE bm SET bm_name=%s WHERE bm_id=%s;"
# cur.execute(update_bm, ("UI", 3))  # 单条
# cur.executemany(update_bm, [("CW", 1), ("TEST", 2)]) # 批量
# ====================3.5 删除数据======================
delete_bm = "DELETE FROM bm WHERE bm_id=%s;"
cur.execute(delete_bm, 5)  # 单条
cur.executemany(delete_bm, [2, 3])  # 批量



# 4. 提交事务以及关闭资源
conn.commit()  # 提交事务(保证写操作的成功)
cur.close()
conn.close()