# demo04_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(
#     bumen_id INT, bumen_name VARCHAR(30)
# );"""
# cur.execute(create_bm)
# ----------------------3.2 插入数据-----------------------
# insert_bm = "INSERT INTO bm VALUES(%s, %s);"
# cur.execute(insert_bm, (1, "OPS"))  # 单条数据插入
# cur.executemany(insert_bm, [  # 批量插入
#     (2, "DEV"), (3, "HR"), (4, "CW"), (5, "UI")
# ])
# ----------------------3.3 查询数据-----------------------
# select_bm = "SELECT * FROM bm;"
# cur.execute(select_bm)
# print(cur.fetchone())  # 抓取一条数据
# print(cur.fetchmany(2))  # 接着抓取2条数据
# print(cur.fetchall())  # 抓取剩余的所有数据
# ----------------------3.4 更新数据-----------------------
update_bm = "UPDATE bm SET bumen_name=%s WHERE bumen_id=%s;"
cur.execute(update_bm, ("TEST", 2))
# ----------------------3.5 删除数据------------------------
delete_bm = "DELETE FROM bm WHERE bumen_id=5;"
cur.execute(delete_bm)

# 4. 提交事务以及关闭资源
conn.commit()  # 提交事务
cur.close()
conn.close()