import pymysql.cursors
# 連接數(shù)據(jù)庫
connect = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123456',
    db='test_1',
    charset='utf8'
)

# 獲取游標
cursor = connect.cursor()
#創(chuàng)建表格
sql = "CREATE TABLE test_1(id INTEGER PRIMARY KEY,name TEXT)"
try:
    cursor.execute(sql)
    connect.commit()
except:
    print("表已存在")
print('成功創(chuàng)建表格')

# 連接數(shù)據(jù)庫
connect = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123456',
    db='note',
    charset='utf8'
)
 
# 獲取游標
cursor = connect.cursor()
 
#刪除表
sql = 'DROP TABLE IF EXISTS student'
cursor.execute(sql)
connect.commit()
print('如果存在表就刪除表格')
 
#創(chuàng)建表格
sql = "CREATE TABLE student(id INTEGER PRIMARY KEY,name TEXT)"
try:
    cursor.execute(sql)
    connect.commit()
except:
    print("表已存在")
print('成功創(chuàng)建表格')
 
# 插入數(shù)據(jù)
sql = "INSERT INTO student VALUES(%d,'%s')"
data = (1, 'student1')
cursor.execute(sql % data)
connect.commit()
print('成功插入', cursor.rowcount, '條數(shù)據(jù)')
 
# 修改數(shù)據(jù)
sql = "UPDATE student SET name = '%s' WHERE id = %d "
data = ('student2', 1)
cursor.execute(sql % data)
connect.commit()
print('成功修改', cursor.rowcount, '條數(shù)據(jù)')
 
# 查詢數(shù)據(jù)
sql = "SELECT * FROM student WHERE id=%d"
data = (1,)
cursor.execute(sql % data)
for row in cursor.fetchall():
    print("%s" % str(row))
print('共查找出', cursor.rowcount, '條數(shù)據(jù)')
 
# 刪除數(shù)據(jù)
sql = "DELETE FROM student WHERE id = %d LIMIT %d"
data = (1, 1)
cursor.execute(sql % data)
connect.commit()
print('成功刪除', cursor.rowcount, '條數(shù)據(jù)')
 
# 事務(wù)處理
sql_1 = "UPDATE student SET name = name + '1' WHERE id = 1 "
 
try:
    cursor.execute(sql_1)
except Exception as e:
    connect.rollback()  # 事務(wù)回滾
    print('事務(wù)處理失敗', e)
else:
    connect.commit()  # 事務(wù)提交
    print('事務(wù)處理成功', cursor.rowcount)
 
# 關(guān)閉連接
cursor.close()
connect.close()