第01篇 PyMySQL数据库单连接基本方法

PyMySQL库简单介绍

PyMySQL是在Python3.x版本中用于连接和操作MySQL服务器的一个第三方库
MySQLDB是在Python2.x版本中用于连接和操作MySQL服务器的一个第三方库
由于Python2.x将逐步被Python3.x取代，此处我们主要学习PyMySQL的使用

PyMySQL库下载安装

// 默认从官网下载安装PyMySQL库
pip3 install pymysql
// 从豆瓣源下载安装PyMySQL库
pip3 install pymysql -i https://pypi.douban.com/simple
// 从清华源下载安装PyMySQL库
pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

PyMySQL数据库操作

# 1、连接数据库
# -*- coding:utf-8 -*-
import pymysql

# 创建连接，返回一个指定配置的数据库连接对象
conn = pymysql.connect(
    host = "127.0.0.1",   # 数据库服务端IP地址        
    port = 3306,          # 数据库服务端端口
    user = "root",        # 用户
    password = "123456",  # 密码
    database = "ums",     # 数据库名
    charset = "utf8",     # 编码格式
    # autocommit = True     # 自动提交，但不建议
)
# 手动提交数据，另外创建连接时可以配置自动提交（不建议自动提交，因为多表操作事务）
conn.commit()
# 主动关闭连接，另外连接超时时也会被动关闭连接（不建议被动关闭，可能出现意外报错）
conn.close()

# 2、数据库游标
# 1.默认游标Cursor类型，执行查询操作时结果为元组格式数据
# 如：((1, '超级用户'),(2, '普通用户'))
cursor_tup = conn.cursor()
# 2.指定游标DictCursor类型，执行查询操作时结果为字典格式数据
# 如：({'id':1, 'name': '超级用户'},{'id':2, 'name': '普通用户'})
cursor_dic = conn.cursor(cursor=pymysql.cursors.DictCursor)

PyMySQL数据表操作

# 1、新增数据
import pymysql
# 连接数据库
conn = pymysql.connect(
    host='数据库地址', 
    user='用户',
    password='密码',
    database='数据库名',
    charset='utf8'
)
cursor = conn.cursor()  # 获取游标
# 1.新增单条数据
query = "insert into user (username, password, role_id) values ('user16', 'u016', 2)"
# 执行SQL，返回受影响的行数
count = cursor.execute(query=query)  
query = "insert into user (username, password, role_id) values (%s, %s, %s)"
# 执行SQL，格式化SQL参数，返回受影响的行数
count = cursor.execute(query=query, args=('user17', 'u017', 2))  
# 提交之后，获取刚插入受影响的行数
row_count = cursor.rowcount
# 提交之后，获取刚插入的数据的ID
last_id = cursor.lastrowid

# 2.新增多条数据
query = "insert into user (username, password, role_id) values ('user18', 'u018', 2),('user19', 'u019', 2)"
# 方式一：执行SQL，返回受影响的行数
count = cursor.execute(query=query) 
query = "insert into user (username, password, role_id) values (%s, %s, %s)"
# 方式二：执行SQL，格式化SQL参数，返回受影响的行数
count = cursor.executemany(query=query, args=[('user20', 'u020', 2), ('user21', 'u021', 2)])
# 方式一：提交之后，获取刚插入受影响的行数
row_count = cursor.rowcount 
# 方式二：提交之后，获取刚插入的数据的ID
last_id = cursor.lastrowid

# 3.提交数据，关闭游标，关闭连接
conn.commit()
cursor.close()
conn.close()

# 2、删除数据
import pymysql
# 连接数据库
conn = pymysql.connect(
    host='数据库地址', 
    user='用户',
    password='密码',
    database='数据库名',
    charset='utf8'
)
cursor = conn.cursor()  # 获取游标
# 方式一：执行SQL，返回受影响的行数
delete = "delete from user where id=9"
count = cursor.execute(query=delete)
# 方式二：执行SQL，格式化SQL参数，返回受影响的行数
delete = "delete from user where id=%s"
count = cursor.execute(query=delete, args=[10])
# 提交之后，获取刚删除受影响的行数
row_count = cursor.rowcount 

# 3、修改数据
import pymysql
# 连接数据库
conn = pymysql.connect(
    host='数据库地址', 
    user='用户',
    password='密码',
    database='数据库名',
    charset='utf8'
)
cursor = conn.cursor()  # 获取游标
# 方式一：执行SQL，返回受影响的行数
update = "update user set username='user70' where id=7"
count = cursor.execute(query=update)
# 方式二：执行SQL，格式化SQL参数，返回受影响的行数
update = "update user set username='user80' where id=%s"
count = cursor.execute(query=update, args=[8])
# 提交之后，获取刚插入受影响的行数
row_count = cursor.rowcount 

# 4、查询数据
import pymysql
# 连接数据库
conn = pymysql.connect(
    host='数据库地址', 
    user='用户',
    password='密码',
    database='数据库名',
    charset='utf8'
)
cursor = conn.cursor()  # 获取游标
query = "select * from user limit %s"
# 参数1：SQL语句；参数2：SQL语句格式化参数
count = cursor.execute(query=query, args=(10,))
# 获取单条结果，注意每次取出一条数据，游标对象的rownumber属性值+1，下次在该基础上取下一条数据
data_one = cursor.fetchone()
# 获取刚查询结果的行数，获取刚查询数据后的标记行数
row_count = cursor.rowcount
row_number = cursor.rownumber

# 获取n条结果，注意每次取出n条数据，游标对象的rownumber属性值+n，下次在该基础上取下一条数据
data_n = cursor.fetchmany(3)
# 获取刚查询结果的行数，获取刚查询数据后的标记行数
row_count = cursor.rowcount
row_number = cursor.rownumber

# 获取剩余全部结果，注意游标对象的rownumber属性值达到最大，下次查询结果为空
data_rest_all = cursor.fetchall()
# 获取刚查询结果的行数，获取刚查询数据后的标记行数
row_count = cursor.rowcount
row_number = cursor.rownumber

#  获取查询结果的字段简介集，可以得到对应的字段列表
#  如：(('id', 3, None, 11, 11, 0, False), ('username', 253, None, 255, 255, 0, True)...)
cols_desc = cursor.description
columns = [desc[0] for desc in cols_desc]

cursor.close()
conn.close()

PyMySQL数据库事务

# 1、事务回滚
import pymysql
# 连接数据库
conn = pymysql.connect(
    host='数据库地址', 
    user='用户',
    password='密码',
    database='数据库名',
    charset='utf8'
)
cursor = conn.cursor()  # 获取游标
# 使用场景：A表和B表相互依赖，要么同时成功，要么同时失败 
try:
    count = cursor.execute(
        query="insert into role  (`name`, `desc`) values (%s, %s)", args=('管理用户', '拥有部分权限'))
    last_id = cursor.lastrowid
    count = cursor.execute(
        query="insert into user (username, password, role_id) values (%s, %s, %s)", args=('user22', 'u022',))
    last_id = cursor.lastrowid
    conn.commit()
except Exception as error:
    conn.rollback()  # 执行异常，事务回滚
cursor.close()
conn.close()

# 2、游标调节
import pymysql
# 连接数据库
conn = pymysql.connect(
    host='数据库地址', 
    user='用户',
    password='密码',
    database='数据库名',
    charset='utf8'
)
cursor = conn.cursor()  # 获取游标
# 注意，如果有多条获取查询的语句，游标会从上一次的位置开始查询
# 比如说如果有两条 cursor.fetchall()   那么第二次查询结果为空
# 可以用cursor.scroll()方法来控制游标
print(cursor.rownumber)  # 0
count = cursor.execute("select * from user")
data_all = cursor.fetchall()
print(cursor.rownumber)  # 15
cursor.scroll(0, mode='absolute')  # 移动到绝对位置0，即第一行的位置
print(cursor.rownumber)  # 0
data_n = cursor.fetchmany(10)
print(cursor.rownumber)  # 10
cursor.scroll(1, mode='relative')  # 移动到相对位置，当前行的下一行
print(cursor.rownumber)  # 11
cursor.scroll(-1, mode='relative')  # 移动到相对位置，当前行的上一行
print(cursor.rownumber)  # 10