Python 数据库操作 - PyMySQL

Python 数据库操作 - PyMySQL

数据库说明

数据库（Database，DB）是存放数据的仓库，按照数据结构来组织、存储和管理数据的仓库。按照数据库组织类型可以分为关系型数据库和非关系型数据库。常见关系型数据库有 MySQL、SQL Server、Oracle、DB2 等；常见非关系数据库有Redis（键值对存储）、Hbase（列存储）、MongoDB（文档型数据库）、InfoGrid（图数据库）等。

数据库管理系统（Database Management System，DBMS）是一种操纵和管理数据库的软件，用于建立、使用和维护数据库。数据库管理系统能够提供数据录入、修改、删除、查询操作；具有数据定义、数据操作、数据存储与管理、数据维护、通信等功能，且能够允许多用户使用。常见的数据库管理系统有 MySQL、SQL Server、Oracle、DB2 等；常见非关系数据库有Redis（键值对存储）、Hbase（列存储）、MongoDB（文档型数据库）、InfoGrid（图数据库）等。

PyMySQL

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中则使用 mysqldb。PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库，本身为开源软件，可以从 github 取得最新版本以及操作说明。

要安装 PyMySQL 之前先确认一下目前的开发环境，下图中展示了整个数据库的运营环境，MySQL 代表著数据库管理系统，而我们所撰写的 Python 程序则为应用系统，当应用系统要存取 MySQL 时，需要有特定的服务器接口，这个接口库就是 PyMySQL，因此要进行数据库操作时，需要事先完成下列事项：

1. 安装 MySQL 数据库服务器。
2. 创建数据库 TESTDB.
3. 创建数据库 TESTDB 管理人员帐号。
4. 安装 PyMySQL 模块。

数据库运作图

MySQL 数据库服务器的安装与设定需开发者请自行完成操作。下图显示在命令列安装 PyMySQL 模块，并进入交互模式，导入 PyMySQL 模块并显示版本。

pip3 install pymysql

安装并检验 PyMySQL 模块

创建数据库表

透过 connect() 方法连接数据库，需要提供数据库服务器主机，数据库管理者帐号、密码以及数据库名称；使用 execute() 方法来输入标准的 SQL语句来为数据库创建表，如下所示创建表 students，包含三个字段：学号 (xh)、姓名 (name)、年龄 (age)。

import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost',
        user='testuser',
        password='test123',
        database='TESTDB')
    
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
    
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS students")
    
# 使用预处理语句创建表
sql = """CREATE TABLE students (
    xh INT NOT NULL,
    name  VARCHAR(20),
    age INT)"""
    
cursor.execute(sql)
    
# 关闭数据库连接
db.close()

事务机制 (TRANSACTION)

在 Python DB API 2.0 提供了事务机制 (transaction) 来确保数据一致性。事务具有四个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特性。而实践事务主要是以下两个方法 commit() 和 rollback()。

• 原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
• 一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
• 隔离性（isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（durability）：持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

数据库插入操作 INSERT

使用执行 SQL INSERT 语句向表 students 插入记录，使用变量向 SQL INSERT 语句中的 xh, name, age 字段传递参数，使用 executemany() 方法来做为一次新增多笔数据，要事先定义好数据的内容。

import pymysql
 
# 打开数据库连接
conn = pymysql.connect(host='localhost',
    user='testuser',
    password='test123',
    database='TESTDB')
 
# 使用cursor()方法获取操作游标 
cursor = conn.cursor()
vals = [[100321,"Mohan", 20], [100322,"lisi", 20]] 

try:
   # 执行 sql 语句
   cursor.executemany("INSERT INTO students(xh, name, age) VALUES (%s, %s,  %s)", vals)
   # 执行sql语句
   conn.commit()
except:
   # 发生错误时回滚
   conn.rollback()
 
# 关闭数据库连接
conn.close()

数据库查询操作 SELECT

Python 查询 Mysql 使用 fetchone() 方法获取单条数据, 使用 fetchall() 方法获取多条数据。
将数据库数据转换成 DataFrame 数据框，好方便后续的操作，转换的方式有两种：
用 Pandas 的 read_sql() 方法；使用 list() 方法转换成列表类型，在透过 Pandas 转成数据框。

import pymysql

# 打开数据库连接
conn = pymysql.connect(host='localhost',
    user='testuser',
    password='test123',
    database='TESTDB')
    
# 使用cursor()方法获取操作游标 
cursor = conn.cursor()

# SQL 查询语句
query = "SELECT * FROM students"
try:
# 方法一
	df1 = pd.read_sql(query, conn)
	print("方法一\n", df1)
# 方法二
	cursor.execute(query)
	results = cursor.fetchall()
	print(type(results))
	df2 = pd.DataFrame(list(results),columns =['学号', '姓名', '年龄'])
	print("方法二\n", df2)
except:
   print ("Error: unable to fetch data")

# 关闭数据库连接
conn.close()

读取学生数据表的结果画面

数据库更新操作 UPDATE

更新操作用于更新数据表的数据，以下实例将 students 表中 的 age 字段递增 1。

import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost',
    user='testuser',
    password='test123',
    database='TESTDB')
    
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
    
# SQL 更新语句
sql = "UPDATE students SET age = age + 1'
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交到数据库执行
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()
    
# 关闭数据库连接
db.close()    

数据库删除操作 DELETE

删除操作用于删除数据表中的数据，以下实例演示了删除数据表 students 中 name 为 Mohan 的数据。

import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost',
    user='testuser',
    password='test123',
    database='TESTDB')
    
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
    
# SQL 删除语句
sql = "DELETE FROM students WHERE name = '%s'" % ('Mohan')
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交修改
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()
    
# 关闭连接
db.close()   

参考来源

• Welcome to PyMySQL’s documentation!, https://pymysql.readthedocs.io/en/latest/
• PyMySQL/PyMySQL, https://github.com/PyMySQL/PyMySQL
• Python3 MySQL 数据库连接 - PyMySQL 驱动, https://www.runoob.com/python3/python3-mysql.html
• MySQL添加新用户、为新用户分配权限, https://blog.csdn.net/ljxfblog/article/details/80197277
• Inserting a list holding multiple values in MySQL using pymysql, https://stackoverflow.com/questions/42039794/inserting-a-list-holding-multiple-values-in-mysql-using-pymysql