# -*- coding: utf-8 -*-

import MySQLdb

con_config = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "database": "demo",
    "autocommit": True  # 不提交修改不生效，3中方式任选一种
}

# autocommit = True     # 提交1：连接的时候就开启自动提交
# con.autocommit(True)  # 提交2：执行SQL语句之前设置自动提交
# con.commit()          # 提交3：执行SQL语句之后提交


# 连接数据库，获得游标
con = MySQLdb.connect(**con_config)
cursor = con.cursor()


# 1、插入单条数据
insert_sql = "insert into student(name, age) values(%s, %s)"
cursor.execute(insert_sql, ("Tom", 23))
print(cursor.rowcount)
# 1


# 2、插入多条数据
# 可用于改操作: insert, delete, update
cursor.executemany(insert_sql, [("Jack", 24), ("Jimi", 25)])
print(cursor.rowcount)  # 无论提交还是不提交，rowcount都有数据，不要被误导
# 2


# 3、删除数据
delete_sql = "delete from student where id=%s"
cursor.execute(delete_sql, (1,))  # 第二个参数为一个可迭代对象，只有一个参数要传元组
print(cursor.rowcount)
# 1


# 4、修改数据
update_sql = "update student set age=99 where id=%s"
cursor.execute(update_sql, (4,))
print(cursor.rowcount)
# 1


# 5、查询单条  查询不需要提交
select_sql = "select name, age from student limit %s"
cursor.execute(select_sql, (1,))
row = cursor.fetchone()
print(row)
# ('Tom', 99)


# 6、查询多条数据
cursor.execute(select_sql, (2,))
rows = cursor.fetchall()
print(rows)
# (('Tom', 23), ('Tom', 23))


# 关闭游标和数据库连接
cursor.close()
con.close()