核心代码
# 导入第三方包
impor pymysql
# 创建连接
# 设置连接属性 --IP、port、指定库名、账号、密码、指定编码集
# PS:编码集使用 utf8非utf-8
qiao = pymysql.Connect(
host="127.0.0.1",
port=3306,
database="books",
user="root",
password="你的密码",
charset="utf8",
)
# 创建载体-游标
lv = qiao.cursor()
# 数据传输 核心,具体看下面
# 释放资源
lv.close()
qiao.close()
数据传输
查
import pymysql
conn = pymysql.Connect(
host="127.0.0.1",
port=3306,
database="books",
user="root",
password="你的密码",
charset="utf8"
)
cursor = conn.cursor()
sql = "select * from t_book"
cursor.execute(sql)
# 解析1:获取响应的结果行数
rc = cursor.rowcount
print("查询到的结果行数:", rc)
# 解析2:一次获取一条数据
# row1 = cursor.fetchone()
# row2 = cursor.fetchone()
# row3 = cursor.fetchone()
# row4 = cursor.fetchone()
# print("第一条数据:", row1)
# print("第2条数据:", row2)
# print("第3条数据:", row3)
# print("第4条数据:", row4)
# 解析2.1:一次获取任意数据()内填要去的数量
# row5 = cursor.fetchmany(2)
# 解析3:一次获取所有数据
rows = cursor.fetchall()
print('所有数据:', rows)
for row in rows:
print(row)
print("id:",row[0])
print("书名:",row[1])
print("发布时间:",row[2])
print("阅读数:",row[3])
print("评论数:",row[4])
print("是否删除:",row[5])
# 释放资源
cursor.close()
conn.close()
增删改
import pymysql
conn = pymysql.Connect(
host="127.0.0.1",
port=3306,
database="books",
user="root",
password="你的密码",
charset="utf8"
)
cursor = conn.cursor()
# u_sql = "insert into t_book values(4,'神雕侠侣','1988-10-11',100,80,0)"
# cursor.execute(u_sql)
# rc = cursor.rowcount
# print('受影响的行数:', rc)
# sql = "update t_book set pub_date='1990-11-06' where id=3"
# cursor.execute(sql)
# conn.commit()
# rc = cursor.rowcount
# print('受影响的行数:', rc)
# 4-1. 编写 SQL 语句
sql = "delete from t_book where id=3"
# 4-2. 执行 SQL 语句
cursor.execute(sql)
conn.commit()
# 4-3. 解析响应结果
rc = cursor.rowcount
print("受影响的行数:", rc)
# 修改数据库操作(增删改),执行完SQL语句后必须做提交动作,否则执行失败
# 提交方式1:手动conn.commit()
# 提交方式2:自动提交 创建连接对象时,设置参数:autocommit=True