使用 Python 操作 MySQL 数据库

MySQL 简介

MySQL 是最流行的 RDBMS，即关系数据库管理系统，在 Web 方面应用广泛。数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。对于用户来说，我们可以把数据库当作一个黑盒子，使用 RDBMS 听得懂的语言（也就是 SQL）进行交流，即可对数据库进行增删改查（CRUD）操作。

同时，作为一款广泛应用的数据库产品，除了直接使用 SQL 进行操作之外，MySQL 还为广大用户提供了 C、C++、Python、Java、php、C# 等等多种编程语言的操作接口。下面我们就来看看如何通过 Python3 对 MySQL 数据库进行操作。

安装 PyMySQL

大家可能在 Python2.x 中用的是 mysqldb，但是在 Python3.x 中已经不支持这个组件了，取而代之的是 pymysql。

使用 pip 安装

$ pip install PyMySQL

查看安装情况：

$ pip freeze | grep PyMySQL
PyMySQL==0.9.2

手动下载安装

PyMySQL下载地址：https://github.com/PyMySQL/PyMySQL

$ git clone https://github.com/PyMySQL/PyMySQL
$ cd PyMySQL/
$ python3 setup.py install

连接 MySQL 数据库

安装完 PyMySQL 包，我们就可以使用 pymysql.connect() 方法连接 MySQL 数据库了。比如我在本地创建了一个名为 test 的数据库，可以通过如下代码进行连接操作：（在 Python3.5 和 Python3.6 中测试通过）

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost",user="root",password="",db="test",charset="utf8")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据
data = cursor.fetchone()

print ("Database version : %s " % data)

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

保存为 connect.py，并执行 python3 connect.py，输出：

Database version : 5.7.24-0ubuntu0.16.04.1

在这里我们使用了 fetchone() 方法获取单条数据，也可以使用 fetchall() 方法获取多条数据。

增删改查

创建数据库表

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost",user="root",password="lu1010",db="test",charset="utf8")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 如果数据表已经存在使用 execute() 方法删除表
cursor.execute("DROP TABLE IF EXISTS USER")

# 创建数据表SQL语句
sql = """CREATE TABLE IF NOT EXISTS USER (
         id    int         unsigned not null auto_increment PRIMARY KEY,
         name  varchar(32) not null unique,
         email varchar(100),
         cdate datetime );"""

cursor.execute(sql)

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

查看是否创建成功

mysql> desc USER;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32)      | NO   | UNI | NULL    |                |
| email | varchar(100)     | YES  |     | NULL    |                |
| cdate | datetime         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

插入操作

import pymysql
import datetime

# 打开数据库连接
db = pymysql.connect(host="localhost",user="root",password="lu1010",db="test",charset="utf8")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 定义添加 sql 语句
data = (20190001, "Allen", '[email protected]', \
        '{0}'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
sql = "INSERT INTO USER(id, name, email, cdate) value('%d','%s','%s','%s')"%(data)

try:
    # 使用 execute() 方法执行 SQL 
    m = cursor.execute(sql)
    # 事务提交
    db.commit()
    print("成功操作条数：",m)
    #print("成功操作条数：",cursor.rowcount)
except Exception as err:
    # 事务回滚
    db.rollback()
    print("SQL执行错误，原因：",err)

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

我们这里插入了两条数据，插入成功如下：

mysql> SELECT * FROM USER;
+----------+-------+-----------------+---------------------+
| id       | name  | email           | cdate               |
+----------+-------+-----------------+---------------------+
| 20190001 | Allen | [email protected] | 2019-01-14 21:10:54 |
| 20190002 | Bob   | [email protected]   | 2019-01-14 21:17:00 |
+----------+-------+-----------------+---------------------+
2 rows in set (0.00 sec)

查询操作

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost",user="root",password="lu1010",db="test",charset="utf8")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 定义查询 sql 语句
sql = "SELECT * FROM USER"

try:
    # 使用 execute() 方法执行 SQL 查询 
    cursor.execute(sql)
    print("本次查询条数：", cursor.rowcount)
    # 获取所有结果
    res = cursor.fetchall()
    for item in res:
        print(item)
except Exception as err:
    print("SQL执行错误，原因：",err)

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

输出：

本次查询条数： 2
(20190001, 'Allen', '[email protected]', datetime.datetime(2019, 1, 14, 21, 10, 54))
(20190002, 'Bob', '[email protected]', datetime.datetime(2019, 1, 14, 21, 17))

更新操作

假设我们要更新 Allen 预留的 email 地址

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost",user="root",password="lu1010",db="test",charset="utf8")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 定义更新 sql 语句
sql = "UPDATE USER SET email = '[email protected]' WHERE id = %d"%(20190001)

try:
    cursor.execute(sql)
    print("本次更新条数：", cursor.rowcount)
    db.commit()
except Exception as err:
    db.rollback()
    print("SQL执行错误，原因：",err)

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

检查是否更新成功：

mysql> SELECT * FROM USER;
+----------+-------+---------------+---------------------+
| id       | name  | email         | cdate               |
+----------+-------+---------------+---------------------+
| 20190001 | Allen | [email protected] | 2019-01-14 21:10:54 |
| 20190002 | Bob   | [email protected] | 2019-01-14 21:17:00 |
+----------+-------+---------------+---------------------+
2 rows in set (0.00 sec)

删除操作

假如我们要删除 Bob 的信息

import pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost",user="root",password="lu1010",db="test",charset="utf8")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 定义删除 sql 语句
sql = "DELETE FROM USER WHERE id = %d"%(20190002)

try:
    cursor.execute(sql)
    print("成功删除条数：", cursor.rowcount)
    db.commit()
except Exception as err:
    db.rollback()
    print("SQL执行错误，原因：",err)

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

阿基米东