我们经常需要将大量数据保存起来以备后续使用,数据库是一个很好的解决方案。在众多数据库中,MySQL数据库算是入门比较简单、语法比较简单,同时也比较实用的一个。在这篇博客中,将以MySQL数据库为例,介绍一下如何使用Python操作数据库。

本科阶段曾学过使用Java对MySQL数据库进行操作,基本思路是先连接数据库,然后执行SQL语句对数据库进行操作,最后打印结果并断开连接。使用Python操作数据库的流程和以上过程基本一致,在对其进行介绍之前,先介绍一些基本的概念。

1 基本概念

1.1 使用python操作数据库的流程

以流程图的方式展示python操作MySQL数据库的流程:

这里写图片描述
对上图的解读:首先依次创建Connection对象(数据库连接对象)用于打开数据库连接,创建Cursor对象(游标对象)用于执行查询和获取结果;然后执行SQL语句对数据库进行增删改查等操作并提交事务,此过程如果出现异常则使用回滚技术使数据库恢复到执行SQL语句之前的状态;最后,依次销毁Cursor对象和Connection对象。

下面依次对Connection对象、Cursor对象和事务等概念进行介绍。

1.2 Connection对象

Connection对象即为数据库连接对象,在python中可以使用pymysql.connect()方法创建Connection对象,该方法的常用参数如下:

host:连接的数据库服务器主机名,默认为本地主机(localhost);字符串类型 user:用户名,默认为当前用户;字符串类型 passwd:密码,无默认值;字符串类 db:数据库名称,无默认值;字符串类型 port:指定数据库服务器的连接端口,默认为3306;整型

Connection对象常用的方法如下:

cursor():使用当前连接创建并返回游标 commit():提交当前事务 rollback():回滚当前事务 close():关闭当前连接

1.3 Cursor对象

Cursor对象即为游标对象,用于执行查询和获取结果,在python中可以使用conn.cursor()创建,conn为Connection对象。Cursor对象常用的方法和属性如下:

execute():执行数据库查询或命令,将结果从数据库获取到客户端 fetchone():获取结果集的下一行 fetchmany():获取结果集的下几行 fetchall():获取结果集中剩下的所有行 close():关闭当前游标对象 rowcount:最近一次的execute返回数据的行数或受影响的行数

execute()方法和fetch类方法的工作示原理如下图所示:

这里写图片描述这里写图片描述
1.4 事务

事务是数据库理论中一个比较重要的概念,指访问和更新数据库的一个程序执行单元,具有ACID特性:

原子性(Atomic):事务中的各项操作要么全都做,要么全都不做,任何一项操作的失败都会导致整个事务的失败 一致性(Consistent):事务必须使数据库从一个一致性状态变到另一个一致性状态 隔离性(Isolated):并发执行的事务彼此无法看到对方的中间状态,一个事务的执行不能被其他事务干扰 持久性(Durable):事务一旦提交,它对数据库的改变就是永久性的,可以通过日志和同步备份在故障发生后重建数据。

在开发时,我们以以下三种方式使用事务:

阿里云-推广AD

正常结束事务:conn.commit() 异常结束事务:conn.rollback() 关闭自动commit:设置 conn.autocommit(False)

2 使用python实现对MySQL数据库的增删改查等操作

在python中操作MySQL数据库时,要使用的模块是:

Python2中:mysqldb(pip2 install mysqldb) Python3中:pymysql(pip3 install pymysql)

本篇博客所使用的环境为:

python 3.6 win 10 pycharm 2018.1 pymysql 0.9.2 mysql 5.5 Navicat 11

下面将以具体代码的形式依次介绍python中如何实现对MySQL数据库的增删改查等操作。数据库截图如下:

这里写图片描述
2.1 连接数据库

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
29
#-*-coding:utf-8-*-
"""
@author:taoshouzheng
@time:2018/7/18 9:24
@email:tsz1216@sina.com
"""
import pymysql
# 打开数据库连接
conn = pymysql.connect('localhost', 'root', 'root', 'test_db')
# 使用cursor()方法创建一个游标对象
cursor = conn.cursor()
# 使用execute()方法执行SQL查询
cursor.execute('SELECT VERSION()')
# 使用fetchone()方法获取单条数据
data = cursor.fetchone()
# 打印
print('database version: %s' % data)
# 关闭数据库连接
conn.close()
# 结果
# database version: 5.5.20

2.2 创建数据表

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
29
30
31
32
33
#-*-coding:utf-8-*-
"""
@author:taoshouzheng
@time:2018/7/18 9:24
@email:tsz1216@sina.com
"""
import pymysql
# 打开数据库连接
conn = pymysql.connect('localhost', 'root', 'root', 'test_db')
# 使用cursor()方法创建一个游标对象cursor
cursor = conn.cursor()      # 游标对象用于执行查询和获取结果
# 使用execute()方法执行SQL,如果表存在则将其删除
cursor.execute('DROP TABLE IF EXISTS EMPLOYEE')
# 使用预处理语句创建表
sql = """CREATE TABLE `employee` (
  `first_name` varchar(255) DEFAULT NULL COMMENT '姓',
  `last_name` varchar(255) DEFAULT NULL COMMENT '名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` varchar(255) DEFAULT NULL COMMENT '性别',
  `income` varchar(255) DEFAULT NULL COMMENT '收入'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
# 执行SQL语句
cursor.execute(sql)
# 关闭数据库连接
conn.close()

效果截图如下:

这里写图片描述这里写图片描述
2.3 数据库插入操作

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
29
30
31
32
#-*-coding:utf-8-*-
"""
@author:taoshouzheng
@time:2018/7/18 9:24
@email:tsz1216@sina.com
"""
import pymysql
# 打开数据库连接
conn = pymysql.connect('localhost', 'root', 'root', 'test_db')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# SQL语句:向数据表中插入数据
sql = """INSERT INTO employee(first_name,
         last_name, age, sex, income)
         VALUES ('赵', '丽颖', 38, '女', 15000)"""
# 异常处理
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交事务到数据库执行
    conn.commit()       # 事务是访问和更新数据库的一个程序执行单元
except:
    # 如果发生错误则执行回滚操作
    conn.rollback()
# 关闭数据库连接
conn.close()

效果截图如下:

这里写图片描述这里写图片描述
2.4 数据库查询操作

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#-*-coding:utf-8-*-
"""
@author:taoshouzheng
@time:2018/7/18 9:25
@email:tsz1216@sina.com
"""
import pymysql
# 打开数据库连接
conn = pymysql.connect('localhost', 'root', 'root', 'test_db')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# SQL语句:查询
sql = "SELECT * FROM employee WHERE income > 1000 "
# 异常处理
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有的记录列表
    results = cursor.fetchall()
    # 遍历列表
    for row in results:
        # 打印列表元素
        print(row)
        # 姓
        first_name = row[0]
        # 名
        last_name = row[1]
        # 年龄
        age = row[2]
        # 性别
        sex = row[3]
        # 收入
        income = row[4]
        # 打印列表元素
        print(first_name, last_name, age, sex, income)
except:
    print('Uable to fetch data!')
# 关闭数据库连接
conn.close()

效果截图如下:

这里写图片描述这里写图片描述
2.5 数据库更新操作

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
29
30
#-*-coding:utf-8-*-
"""
@author:taoshouzheng
@time:2018/7/18 10:18
@email:tsz1216@sina.com
"""
import pymysql
# 打开数据库连接
conn = pymysql.connect('localhost', 'root', 'root', 'test_db')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# SQL语句,执行更新操作
sql = 'UPDATE employee SET age = age + 5 WHERE sex = "男"'
# 异常处理
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交到数据库执行
    conn.commit()
except:
    # 发生错误时回滚
    conn.rollback()
# 关闭数据库连接
conn.close()

效果截图如下:

这里写图片描述这里写图片描述
2.6 数据库删除操作

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
29
30
#-*-coding:utf-8-*-
"""
@author:taoshouzheng
@time:2018/7/18 9:25
@email:tsz1216@sina.com
"""
import pymysql
# 打开数据库连接
conn = pymysql.connect('localhost', 'root', 'root', 'test_db')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# SQL语句,执行删除操作
sql = 'DELETE FROM employee WHERE income > 5000 AND age >35'
# 异常处理
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交到数据库执行
    conn.commit()
except:
    # 发生错误时回滚
    conn.rollback()
# 关闭数据库连接
conn.close()

这里写图片描述这里写图片描述