"""
# 作业：
1、code实现
2、mysqldb

# 笔记：
#1、连接MySQL
import pymysql

connection = pymysql.connect(host='127.0.0.1',
                             port=3307,
                             user='root',
                             password='123456',
                             db='wzw',
                             charset='utf8mb4')


print(connection)

#获取游标
cursor = connection.cursor()

#create tables
res = cursor.execute("create table if not exists tera(id int primary key,name varchar(100)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;")
print(f'create table 影响行数:{res}')

#DML
res1 = cursor.execute("delete from tera;")
res2 = cursor.execute("insert into tera (id,name) values(1,'wzw');")
print(f'delete影响行数:{res1}')
print(f'insert影响行数:{res2}')

#通过字典格式插入数据 insert dict
dict1 = {'id': 123, 'name': 'qwe'}
sql = "insert into tera (id,name) values(%(id)s, %(name)s);"
res = cursor.execute(sql, dict1)
#提交SQL
#connection.commit()
print(f'insert影响行数:{res}')

#批量插入数据
# sql = "insert into tera (id,name) values(%s, %s) on duplicate key update name=values(name)", [(1, 'qaz'), (123, 'wsx')]
# res = cursor.executemany(sql)
effect_row = cursor.executemany(
    "insert into tera (id,name) values(%s, %s) on duplicate key update id=values(id)", [
        (1, 'qaz'),
        (123, 'wsx')
    ])
print(f'insert影响行数:{effect_row}')
#
sql = "select * from tera"
res1 = cursor.execute(sql)  #执行查询SQL
res2 = cursor.fetchall()    #获取结果集，dict格式
print(res2)



2、事务处理
connection.begin()
connection.commit()
connection.rollback()




3、常用的封装操作
https://github.com/ayuliao/mysqldb
#补充调用方法：

#1）连接池（很多连接），每隔一段时间，检查一下连接池中连接是否活跃。
    1.1）mysql的连接会被server端主动断开；
    1.2）所以采用PyMySQL池，Thread =>while True => Ping MySQL Server
    1.3）如果因为server端异常断开，SQL执行失败，




"""