Python与MySQL数据库的交互

1 安装pymysql

在Python中，如果想要将数据存储到MySQL中，就需要借助pymysql模块来操作。可直接通过pip或者conda命令安装

2 用Python连接数据库

注意下方是本地数据库连接操作，注意sql语句结尾要加分号。

import pymysql
db=pymysql.connect(host='localhost',port=3306,user='root',password='test123456',database='python',charset='utf8')
#host参数代表MYSQL服务器地址
#port代表所使用的端口
#user代表MYSQL数据库的用户名
#password代表数据库的登录密码
#database代表要连接的数据库名称（这里名称为Python的数据库是之前创建的）
#charset代表编码方式，这里指定的是中文编码方式utf8

cursor = db.cursor()  
#得到一个可以执行SQL语句并且将结果作为字典返回的游标
#cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 
# 定义要执行的SQL语句
sql = """
    CREATE TABLE USER1 (
    id INT auto_increment PRIMARY KEY ,
    name CHAR(10) NOT NULL UNIQUE,
    age TINYINT NOT NULL
    )ENGINE=innodb DEFAULT CHARSET=utf8;  
"""
#注意：charset='utf8' 不能写成utf-8
# 执行SQL语句
cursor.execute(sql)
# 关闭光标对象
cursor.close()
# 关闭数据库连接
db.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

执行上述语句，生成格名为user1的表，如下：

3 Sql语句

在MySQL中有多种数据类型可以有存放数值，不同类型存放的数值范围或者形式不同的。

但是初学者不需要过分关注这些数据类型，熟悉常见操作即可。

3.1创建表

创建数据库与数据表一般通过数据库管理平台phpmyadmin、Navicat等工具皆可实现，这种方式不需要输入复杂的SQL语句。

CRATE TABLE语句用于创建数据库中的表。表由行和列组成，每个表都必须有个表名。

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
#column_name 参数规定表中列的名称。
#data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。
#size 参数规定表中列的最大长度。
1
2
3
4
5
6
7
8
9
10

第二部分测试Python连接数据库的代码就是创建一张user数据表

3.2 插入数据

INSERT INTO 语句可以有两种编写形式。

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name
VALUES (value1,value2,value3,...);
1
2

第二种形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
1
2

示例：

# 插入操作
id='202208'
name='Bob'
age='25'
sql='INSERT INTO user1(id,name,age) VALUE(%s,%s,%s)'
cursor.execute(sql,(id,name,age))
db.commit()
#commit函数是更新数据表的固定写法（commit的中文意思就是提交）。这里增加了一行数据，已经改变了数据表的结构，所以必须用db.commit()来提交这个修改
cursor.close()
db.close()
1
2
3
4
5
6
7
8
9
10

重点看下最后一行代码execute()的函数，第一个参数就是我们刚才写的SQL语句，我们选择甩格式化符%s来实现；第二个参数用来把具体的内容传到各个%s的位置上。

3.3 更新数据

UPDATE 语句用于更新表中的记录。WHERE 子句用于提取那些满足指定条件的记录。

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
1
2
3

示例：将刚才插入行中的age改为66

cursor=db.cursor()
sql="""
	UPDATE user1
	SET age=%s
	WHERE name=%s
"""
cursor.execute(sql,('66','Bob'))
db.commit()
cursor.close()
1
2
3
4
5
6
7
8
9

3.4 删除数据

DELETE 语句用于删除表中的行。WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！

DELETE FROM table_name
WHERE some_column=some_value;
1
2

删除操作相对简单，直接使用delete语句即可，仍然需要使用db的commit()方法才能生效。

3.5 查询数据

学完插入、修改和删除操作，还剩下一个非常重要的操作，那就是查询。

SELECT 语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。

SELECT column_name,column_name
FROM table_name;
# 
SELECT * FROM table_name;
1
2
3
4

同样，我们可以在后面添加where子句来实现对数据的约束。

4 事务

4.1 概述

事务（transaction）是作为单个逻辑单元执行的一系列操作。多个操作作为一个整体向系统提交，要么都执行，要么都不执行。

通俗讲：在事务中的增删查改等操作,要么都成功，要么都失败。

4.2 特性

• 原子性

事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用，原子是最小且不可分割的单元）

• 一致性

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。

• 隔离性

由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。（并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的）事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。

这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

持久性
事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

4.3 事务实现示例

还是用之前的插入操作举例子，通过引入事务执行失败后，则调用rollback()执行数据回滚，相当于什么都没有发生过。

# 引入事务机制的插入操作
cursor=db.cursor()
id='202209'
name='Daniel'
age='30'
sql='INSERT INTO user1(id,name,age) VALUE(%s,%s,%s)'
try:
    cursor.execute(sql,(id,name,age))
    db.commit()
except:
    db.rollback()
cursor.close()
1
2
3
4
5
6
7
8
9
10
11
12

效果：