一、连接

# 导包
import pymysql

# 创建连接
conn = pymysql.Connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
# cursor.execute("select version()")
# cursor.execute("select * from t_book;")
cursor.execute("select * from t_hero;")
result = cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

二、游标位置

# 导包
import pymysql
# 建立连接
conn = pymysql.Connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books")
# 建立游标
corser = conn.cursor()
# 执行查询
sql = "select id 图书id, title 图书名称,`read` 阅读量,`comment` 评论量 from t_book;"
corser.execute(sql)
# result = corser.fetchone()
# result = corser.fetchall()
# result = corser.fetchmany(2)

# print(result)
print(corser.rowcount)
print(corser.fetchone())
print(corser.rownumber)
# 重置游标位置 rownumber
corser.rownumber = 0
print(corser.fetchall())
# 关闭游标
corser.close()
# 关闭连接
conn.close()

三、数据表的增删改

# 导包
import pymysql

# 建立连接
conn = pymysql.Connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books",
                       autocommit=True)

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

# 执行sql
# sql = "insert into t_book(id, title, pub_date) values('6','西游记','1986-01-01');"
# sql = "update  t_book set title = '东游记' where id = 6;"
sql = "delete from t_book where id = 4;"
cursor.execute(sql)

print("受影响的结果数", cursor.rowcount)
# 提交事务
# conn.commit()

# 回滚事务
# conn.rollback()

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

四、主动抛出异常

# 导包
import pymysql
# 创建连接
conn = pymysql.Connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books",
                       autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "insert into t_book(id, title, pub_date) values('4','西游记','1986-01-01');"
cursor.execute(sql)
print(cursor.rowcount)
print("*"*20)
raise Exception("程序出错啦。。。")
sql = "insert into t_hero(name, gender, book_id) values('孙悟空',1,4);"
cursor.execute(sql)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

五、对异常的处理

# 导包
import pymysql
try:
    # 创建连接
    conn = pymysql.Connect(host="localhost",
                           port=3306,
                           user="root",
                           password="root",
                           database="books",
                           autocommit=False)
    # 获取游标
    cursor = conn.cursor()
    # 执行sql

    sql = "insert into t_hero(name, gender, book_id) values('孙悟空',1,4);"
    cursor.execute(sql)
    print("*"*20)
    sql = "insert into t_book(id, title, pub_date) values('4','西游记','1986-01-01');"
    cursor.execute(sql)
    print(cursor.rowcount)
    conn.commit()
except Exception as e:
    conn.rollback()
    print(e)
finally:
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()