MySQL存储 pymysql模块

目录

• pymysql模块
  • 基本使用 cursor=pymysql.cursors.DictCursor
  • 获取数据 fetchall
  • 移动光标 scroll
  • 增删改二次确认 commit autocommit=True
  • 补充方法 rowcount rollback
  • 确保数据的一致性
  • 引入动态字典生成SQL语句
  • 更新数据时的主键配置 ON DUPLICATE KEY UPDATA
  • while循环 + fetchone

pymysql模块

基本使用 cursor=pymysql.cursors.DictCursor

pymysql模块
	pip3 install pymysql
 
import pymysql

# 1.连接MySQL服务端
db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    db='db4_03',
    charset='utf8mb4'
)
# 2.产生游标对象
# cursor = db.cursor()  # 括号内不填写额外参数 数据是元组 指定性不强  [(),()]
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]
# 3.编写SQL语句
# sql = 'select * from teacher;'
sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql)  # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)

获取数据 fetchall

fetchall()  获取所有的结果
fetchone()  一次读一个数据，每次都基于上次的位置往后面读。
fetchmany() 获取指定数量的结果集数据
ps:注意三者都有类似于文件光标移动的特性
读取结果集可以类比读取文件 光标读取到末尾了 再继续读就没有内容了 所以多次使用fetchall 第二次及以后都获取的是空。

fetchall:

fetchone:

移动光标 scroll

cursor.scroll(1,'relative')  # 基于当前位置往后移动1位
cursor.scroll(1,'absolute')  # 基于数据的开头往后移动一位
注意这个输入的数字 是要≥0的。

≥0的

增删改二次确认 commit autocommit=True

 在pymysql.connect里添加autocommit=True
 # 可以针对增 删 改的操作 进行自动确认(无需写代码二次确认)
 db.commit()  # 针对 增 删 改 需要二次确认(代码确认)

补充方法 rowcount rollback

对于光标对象：
cursor.rowcount # 用于获取查询结果的条数
对于connect方法产生的db对象：
rollback # 数据回滚 用于增删改出错的情况 将数据库状态回复到commit之前 相当于什么事都没发生

确保数据的一致性

插入一条数据，要么全部插入要么都不插入，不会出现插入一半的情况，这称之为事务的原子性。
增删改都是对数据库进行更改的操作，而更改操作都必须是一个事务，所以这些操作的标注写法是：

try：
	cursor.execute(sql)
	db.commit()
except:
	db.rollback()

引入动态字典生成SQL语句

添加字段的时候需要修改我们构建的SQL语句，这不是我们想要的。所以引入字典动态生成SQL:

import pymysql

db = pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     passwd='123',
                     db='db1',  # can be changed
                     charset='utf8mb4',  # default
                     autocommit=True,  # set to True
                     )
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)

# dict
data = {
    'id': '2200320',
    'name': 'John Doe',
    'age': '12',
}
table = 'students'
keys = ','.join(data.keys())  # create a str like 'id,name,age'
values = ','.join(['%s'] * len(data)) # make format symbols like '%s,%s,%s'
sql = 'INSERT INTO {table} ({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
print(sql) # INSERT INTO students (id,name,age) VALUES (%s,%s,%s)

try:
    if cursor.execute(sql,tuples=(data.values())):
        print('Success')
        db.commit()
except Exception:
    print('Error')
    db.rollback()
db.close()

更新数据时的主键配置 ON DUPLICATE KEY UPDATA

在某些应用情境下，我们关心表中会不会出现重复数据，如果出现了，我们希望更新数据而不是重复保存一次。我们需要实现：如果数据存在，就更新数据；如果数据不存在，则插入数据。
可以给插入语句添加约束条件：ON DUPLICATE KEY UPDATA 意思是如果主键已经存在，就执行更新操作。
完整的SQL写法：INSERT INTO stundent(id,name,age) VALUES(%s,%s,%s) ON DUPLICATE KEY UPDATA id = %s,name = %s, age = %s 注意这里%s是占位符，可以通过execute第二个参数给他传进去。这样写即可实现主键不存在便插入数据，主键存在则更新数据。

while循环 + fetchone

fetchall方法全部获取数据，如果数据量很大，那么占用的开销也会非常高
所以可以使用：

sql = 'SELECT * FROM students WHERE age >= 20'
try:
	cursor.execute(sql)
	print('count:',cursor.rowcount)
	row = cursor.fetchone()
	while row:
		print('row:',row)
		row = cursor.fetchone()
excpet:
	print('error')