execute(query, args=None)
# query為字符串類型的sql語句
# args：可選的序列或映射，用于query的參數(shù)值。
# 如果args為序列，query中必須使用%s做占位符；
# 如果args為映射，query中必須使用%(key)s做占位符

import pymysql
db = pymysql.connect('localhost', 'root', "password")  # 打開數(shù)據(jù)庫連接，password替換為本機數(shù)據(jù)庫密碼
cursor = db.cursor()
cursor.execute('drop database learning;')
cursor.execute('create database learning;')
cursor.execute('use learning')
sql_create = """create table houses (name VARCHAR(100) NOT NULL, house_location VARCHAR(100) NOT NULL, purchasing_year VARCHAR(100) NOT NULL);"""
cursor.execute(sql_create)

# 插入
sql_insert = """insert into houses values(%s,%s,%s);"""
cursor.execute(sql_insert,('夢璃','南天門',1995))  # 插入單條數(shù)據(jù)
cursor.executemany(sql_insert,[('紫英','蜀山',1996),('天河','石沉',1997),('菱紗','溪洞',1998)])  # 插入多條數(shù)據(jù)

sql_select = """select * from houses"""
# 單條查詢
cursor.execute(sql_select)
while 1:
 result = cursor.fetchone()
 if result is None:
  # 取完所有結(jié)果
  break
 print(result)

# 多條查詢，取3條數(shù)據(jù)
cursor.execute(sql_select)
Result = cursor.fetchmany(3)
for res in Result:
 print(res)

# 多條查詢，取所有數(shù)據(jù)
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)

# 更新一條數(shù)據(jù)
sql_update = """update houses set purchasing_year=2000 where name='菱紗';"""
cursor.execute(sql_update)
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)
# 更新多條數(shù)據(jù)
sql_update = """update houses set purchasing_year=%s where name=%s;"""
cursor.executemany(sql_update,[(2018,'夢璃'),(2019,'紫英')])
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)
# 回滾事務(wù)
db.rollback()
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)

# 刪除1條數(shù)據(jù)
sql_delete = """delete from houses where name='夢璃';"""
cursor.execute(sql_delete)
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)
# 刪除多條數(shù)據(jù)
sql_delete = """delete from houses where name=%s;"""
cursor.executemany(sql_delete,[('天河'),('紫英')])
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)

cursor.close()           # 關(guān)閉游標
db.commit()
db.close()            # 關(guān)閉數(shù)據(jù)庫連接
print('sql執(zhí)行成功')