Python MySQL数据库交互

阳光穿透心脏的1/2处 2022-11-07 05:30 148阅读 0赞

## 引言 ##

> 本文介绍在 `Python` 中如何与 `MySQL` 数据库交互
> 
> 利用 `PyMySQL` 数据库驱动，实现 `MySQL` 数据库的增删改查及事务处理

## MySQL 简介 ##

> MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，后来被Sun公司收购，Sun公司后来又被Oracle公司收购，目前属于Oracle旗下产品。

**特点**

> 开源 免费 不要钱 使用范围广,跨平台支持性好,提供了多种语言调用的 API。
> 
> 是学习数据库开发的首选。

## 环境 ##

<table> 
 <thead> 
  <tr> 
   <th>环境名称</th> 
   <th>版本</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Python</td> 
   <td>3.7.9</td> 
  </tr> 
  <tr> 
   <td>PyMySQL</td> 
   <td>1.0.2</td> 
  </tr> 
  <tr> 
   <td>MySql-Server</td> 
   <td>5.7.32</td> 
  </tr> 
 </tbody> 
</table>

首先我们要安装 `PyMySQL` 数据库驱动

pip install PyMySQL

如要指定版本

pip install PyMySQL==1.0.2

可能默认的源安装第三库会有点慢，可以配置一下其他的镜像源。[Pip安装第三方库网速慢(解决方案)][Pip]

如果只想临时安装第三库快一点，可以临时使用其他镜像源。

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple PyMySQL==1.0.2

**PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中则使用 mysqldb。**

## 准备数据 ##

### 创建数据库准备数据 ###

# 创建 testdb 数据库
    create databases testdb charset=utf8;
    
    use testdb;
    
    # 创建 employee 表
    create table employee (
    	id int unsigned primary key auto_increment not null,
        emp_num varchar(10) not null,
        name varchar(10) not null,
        age int not null,
        sex varchar(6) not null,
        salary float not null
    );
    
    # 插入员工数据
    insert into employee (emp_num, name, age, sex, salary) values 
    	('M001', '张三', 56, '男', 10000),
        ('F002', '李四', 50, '女', 9000),
        ('M003', '王五', 47, '男', 8000), 
        ('M004', '赵六', 46, '男', 7000),
        ('F005', '孙七', 36, '女', 6000),
        ('M006', '周八', 28, '男', 5000),
        ('M007', '吴九', 26, '男', 4000),
        ('M008', '郑十', 22, '男', 3000);

## Python 访问数据库流程 ##

![Python DB API访问数据库流程][Python DB API]

### 引入模块 ###

*  在 **py文件** 中引入 `pymysql` 模块

from pymysql import *

### Connection 对象 ###

*  用于建立与数据库的连接
 *  创建对象：调用connect()方法

conn = connect(参数列表)

*  参数 **host**：连接的 `mysql` 主机，如果本机就是 `localhost`
 *  参数 **port**：连接的 `mysql` 主机的端口，默认是 `3306`
 *  参数 **database**：数据库的名称
 *  参数 **user**：连接的用户名
 *  参数 **password**：连接的密码
 *  参数 **charset**：通信采用的编码方式，推荐使用 `utf8`

**对象的方法**

*  **close()** 关闭连接
 *  **commit()** 提交
 *  **cursor()** 返回 Cursor 对象，用于执行 sql 语句并获得结果

### Cursor对象 ###

*  用于执行sql语句，使用频度最高的语句为select、insert、update、delete
 *  获取Cursor对象：调用Connection对象的cursor()方法

cursor = conn.cursor()

**对象的方法**

*  **close()** 关闭
 *  **execute(operation \[, parameters \])** 执行sql语句，返回受影响的行数，主要用于执行 `insert、update、delete` 语句，也可以执行 `create、alter、drop` 等语句
 *  **fetchone()** 执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
 *  **fetchall()** 执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

**对象的属性**

*  `rowcount` 只读属性，表示最近一次 **execute()** 执行后受影响的行数
 *  `connection` 获得当前连接对象

## Python操作MySQL数据库 ##

### 查询 `MySQL` 服务版本 ###

""" Python与MySQL数据库交互练习 """
    import pymysql
    
    
    def mysql_version():
    	""" 查询MySQL版本信息 """
    
    	# 获取数据库连接
    	conn = pymysql.connect(
    		host='localhost', # mysql服务主机，localhost代表本地
    		user='root', 
    		password='123456', 
    		database='testdb',
    		port=3306
    	)
    
    	# 创建游标对象 cursor
    	cursor = conn.cursor()
    
    	# 查询mysql版本的sql语句
    	sql = 'select version();'
    
    	# 执行sql语句
    	cursor.execute(sql)
    
    	data = cursor.fetchone()
     
    	print ("Database version : %s " % data)
     
    	# 关闭数据库连接
    	conn.close()
    
    
    def main():
    
    	mysql_version()
    
    
    if __name__ == '__main__':
    	main()

### employee 数据表的增删改 ###

为了方便操作数据库，我把获取数据库连接和游标对象提取到一个函数里了

import pymysql
    
    
    def get_conn(dbname):
    	""" 获取本地 dbname 的数据库连接及游标对象 """
    	conn = pymysql.connect(
    		host = 'localhost', 
    		user = 'root',
    		password = '123456',
    		database = dbname,
    		port = 3306
    	)
    	return conn, conn.cursor()

#### 新增员工信息到 employee 数据表 ####

def emp_insert():
    	""" 新增员工信息 """
    	conn, cursor = get_conn(dbname='testdb')
    
    	# 插入数据
    	insert_sql = """insert into employee values (NULL, 'M009', 'hui', 21, '男', 6000);"""
    
    	# 执行sql语句，返回受响应的行数
    	count = cursor.execute(insert_sql)
    	print(count)
    
    	# 使用占位符
    	emp_info = ('M010', 'wang', 22, '男', 7000)
    	insert_sql = """insert into employee values (NULL, %s, %s, %s, %s, %s);"""
    	count = cursor.execute(sql, emp_info)
    	print(count)
    
    	# 更新数据（给每一位员工涨10%工资）
    	update_sql = """update employee set salary=salary * 1.1;"""
    	cursor.execute(update_sql)
    
    	# 删除数据（删除名字为wang的员工）
    	delete_sql = """delete from employee where name='wang';"""
    	cursor.execute(delete_sql)
    
    	# 记得提交到数据库执行
    	conn.commit()
    
    	cursor.close()
    	conn.close()

#### 更新 employee 数据表信息 ####

def emp_update():
    	""" 更新employee数据库表数据 """
    	conn, cursor = get_conn(dbname='testdb')
    
    	# 更新数据（给每一位员工涨10%工资）
    	update_sql = """update employee set salary=salary * 1.1;"""
    	cursor.execute(update_sql)
    
    	# 记得提交到数据库执行
    	conn.commit()
    
    	cursor.close()
    	conn.close()

#### 删除 employee 数据表信息 ####

def emp_delete():
    	""" 删除employee数据库表数据 """
    	conn, cursor = get_conn(dbname='testdb')
    
    	# 删除数据（删除名字为wang的员工）
    	delete_sql = """delete from employee where name='wang';"""
    	cursor.execute(delete_sql)
    
    	# 记得提交到数据库执行
    	conn.commit()
    
    	cursor.close()
    	conn.close()

**原employee数据表**

mysql> select * from employee;
    +----+---------+------+-----+-----+--------+
    | id | emp_num | name | age | sex | salary |
    +----+---------+------+-----+-----+--------+
    |  1 | M001    | 张三 |  56 | 男  |  10000 |
    |  2 | F002    | 李四 |  50 | 女  |   9000 |
    |  3 | M003    | 王五 |  47 | 男  |   8000 |
    |  4 | M004    | 赵六 |  46 | 男  |   7000 |
    |  5 | F005    | 孙七 |  36 | 女  |   6000 |
    |  6 | M006    | 周八 |  28 | 男  |   5000 |
    |  7 | M007    | 吴九 |  26 | 男  |   4000 |
    |  8 | M008    | 郑十 |  22 | 男  |   3000 |
    +----+---------+------+-----+-----+--------+
    8 rows in set (0.00 sec)

**操作后的**

新增后的
    mysql> select * from employee;
    +----+---------+------+-----+-----+--------+
    | id | emp_num | name | age | sex | salary |
    +----+---------+------+-----+-----+--------+
    |  1 | M001    | 张三 |  56 | 男  |  10000 |
    |  2 | F002    | 李四 |  50 | 女  |   9000 |
    |  3 | M003    | 王五 |  47 | 男  |   8000 |
    |  4 | M004    | 赵六 |  46 | 男  |   7000 |
    |  5 | F005    | 孙七 |  36 | 女  |   6000 |
    |  6 | M006    | 周八 |  28 | 男  |   5000 |
    |  7 | M007    | 吴九 |  26 | 男  |   4000 |
    |  8 | M008    | 郑十 |  22 | 男  |   3000 |
    | 16 | M009    | hui  |  21 | 男  |   6000 |
    | 17 | M010    | wang |  22 | 男  |   7000 |
    +----+---------+------+-----+-----+--------+
    10 rows in set (0.00 sec)
    
    更新、删除后
    mysql> select * from employee;
    +----+---------+------+-----+-----+--------+
    | id | emp_num | name | age | sex | salary |
    +----+---------+------+-----+-----+--------+
    |  1 | M001    | 张三 |  56 | 男  |  11000 |
    |  2 | F002    | 李四 |  50 | 女  |   9900 |
    |  3 | M003    | 王五 |  47 | 男  |   8800 |
    |  4 | M004    | 赵六 |  46 | 男  |   7700 |
    |  5 | F005    | 孙七 |  36 | 女  |   6600 |
    |  6 | M006    | 周八 |  28 | 男  |   5500 |
    |  7 | M007    | 吴九 |  26 | 男  |   4400 |
    |  8 | M008    | 郑十 |  22 | 男  |   3300 |
    | 16 | M009    | hui  |  21 | 男  |   6600 |
    +----+---------+------+-----+-----+--------+
    9 rows in set (0.00 sec)

### employee 数据表的查询 ###

*  **fetchone()** 获取查询结果集的第一个行数据，返回一个元组
 *  **fetchall()** 获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

#### 单行查询获取数据 ####

def emp_fetchone():
    	""" 单行查询employee数据表信息 """
    
    	# 单行查询
    	conn, cursor = get_conn('testdb')
    	sql = """select * from employee where id > 3;"""
    	count = cursor.execute(sql)
        print("查询到%d条数据:" % count)
    
    	for i in range(count):
    		# 一行一行获取查询结果
    		result = cursor.fetchone()
    		print(result)
    
    	cursor.close()
    	conn.close()

#### 多行查询获取数据 ####

def emp_fetchall():
    	""" 多行查询employee数据表信息 """
    
    	# 多行查询
    	conn, cursor = get_conn('testdb')
    	sql = """select * from employee;"""
    	count = cursor.execute(sql)
        print("查询到%d条数据:" % count)
        
    	results = cursor.fetchall()
    	for ret in results:
    		print(ret)
    
    	cursor.close()
    	conn.close()

## 事务处理 ##

### 为什么要有事务 ###

事务广泛的运用于订单系统、银行系统等多种场景

例如：

> A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：
> 
> 1.  检查A的账户余额>500元；
> 2.  A 账户中扣除500元;
> 3.  B 账户中增加500元;

正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。

那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。

以上的案例中，隐藏着一个前提条件：**A扣钱和B加钱，要么同时成功，要么同时失败**。事务的需求就在于此

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

**事务机制可以确保数据一致性**。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为 **ACID** 特性。

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

Python DB API 2.0 的事务提供了两个方法 `commit()` 或 `rollback()`。

### 小实例 ###

def transaction_test():
    	""" 事务演示 """
    	conn, cursor = get_conn('testdb')
    	sql = "delete from employee where age > %s" % (20)
    	try:
    	   # 执行SQL语句
    	   cursor.execute(sql)
    
    	   # 这里以除0异常，来演示现实场景
    	   n = 1 / 0
    
    	   # 向数据库提交
    	   conn.commit()
    
    	except:
    	   # 发生错误时回滚
    	   print('事务回滚')
    	   conn.rollback()
    
    	cursor.close()
    	conn.close()

`delete from employee where age > 20` 这条sql语句并没有把员工年龄20岁以上的给删掉，说明事务回滚。

## 公众号 ##

**新建文件夹X**

> 大自然用数百亿年创造出我们现实世界，而程序员用几百年创造出一个完全不同的虚拟世界。我们用键盘敲出一砖一瓦，用大脑构建一切。人们把1000视为权威，我们反其道行之，捍卫1024的地位。我们不是键盘侠，我们只是平凡世界中不凡的缔造者 。

[Pip]: https://blog.csdn.net/qq_43629857/article/details/105117129
[Python DB API]: /images/20221023/4736af4616f94ebebfa914c0ba84a9f8.png