最全总结 | 聊聊 Python 数据处理全家桶（Mysql 篇）干货

1. 前言

在爬虫、自动化、数据分析、软件测试、Web 等日常操作中，除 JSON、YAML、XML 外，还有一些数据经常会用到，比如：Mysql、Sqlite、Redis、MongoDB、Memchache 等

一般情况下，我们都会使用特定的客户端或命令行工具去操作；但是如果涉及到工程项目，将这部分数据操作集成到代码中使用才是王道

接下来，我将分几篇文章，和大家一起聊聊 Python 操作这些数据的 最优 方案

本篇从使用最为广泛的关系型数据库 - Mysql 开始讲起

2. 准备

首先，我们通过 Mysql 客户端或命令行创建一个数据库 xh

然后，在这个数据库下建一张简单的表 people

为了便于演示，这里只创建了三个字段：id、name、age，其中 id 为主键

Python 操作 Mysql 主要包含下面 3 种方式：

• Python-MySql
• PyMysql
• SQLAlchemy

其中，

Python-MySql 由 C 语法打造，接口精炼，性能最棒；但是由于环境依赖多，安装复杂，已停止更新，仅支持 Python2

PyMysql 为替代 Python-Mysql 而生，纯 Python 语言编写的 Mysql 操作客户端，安装方便，支持 Python3

SQLAlchemy 是一个非常强大的 ORM 框架，不提供底层的数据库操作，主要是通过定义模型对应数据表结构，在 Python Web 编程领域应用广泛

由于 Python-MySql 不支持 Python3，所以本文只谈后 2 种操作方式

3. PyMysql

首先，使用 pip 安装依赖

# 安装依赖
pip3 install pymysql

连接数据库，获取数据库连接对象及游标对象

使用 pymysql 中的 connect() 方法，传入数据库的 HOST 地址、端口号、用户名、密码、待操作数据库的名称，即可以获取 数据库的连接对象

然后，再通过数据库连接对象，获取执行数据库具体操作的 游标对象

import pymysql

# 数据库连接
self.db = pymysql.connect(host='localhost',
                          port=3306,
                          user='root',
                          password='**',
                          database='xh')

# 获取游标
self.cursor = self.db.cursor()

接着，我们来实现增删改查操作

1、新增

新增包含新增单条数据和多条数据

对于单条数据的插入，只需要编写一条插入的 SQL 语句，然后作为参数执行上面游标对象的 execute(sql) 方法，最后使用数据库连接对象的 commit() 方法将数据提交到数据库中

# 插入一条数据
SQL_INSERT_A_ITEM = "INSERT INTO PEOPLE(name,age) VALUES('xag',23);"

def insert_a_item(self):
    """
    插入一条数据
    :return:
    """
    try:
        self.cursor.execute(SQL_INSERT_A_ITEM)
        self.db.commit()
    except Exception as e:
        print('插入数据失败')
        print(e)
        self.db.rollback()

使用执行游标对象的 executemany() 方法，传入插入的 SQL 语句及 位置变量列表，可以实现一次插入多条数据

# 插入多条数据SQL，name和age是变量，对应列表
SQL_INSERT_MANY_ITEMS = "INSERT INTO PEOPLE (name, age) VALUES(%s, %s)"

# 待插入的数据
self.datas = [("张三", 23), ("李四", 24), ("王五", 25)]

def insert_items(self):
    """
    插入多条记录
    :return:
    """
    try:
        self.cursor.executemany(SQL_INSERT_MANY_ITEMS, self.datas)
        self.db.commit()
    except Exception as e:
        print("插入数据异常")
        self.db.rollback()

需要注意的是，PyMysql 会将 SQL 语句中的所有字段当做字符串进行处理，所以这里的 age 字段在 SQL 中被当做字符串处理

2、查询

查询分为三步，分别是：

• 通过游标对象执行具体的 SQL 语句
• 通过游标对象，获取到元组数据
• 遍历元组数据，查看结果

比如：查看数据表中所有的记录

# 查询所有记录
SQL_QUERY_ALL = "SELECT * FROM PEOPLE;"

def query(self):
    """查询数据"""
    # 查询所有数据
    self.cursor.execute(SQL_QUERY_ALL)

    # 元组数据
    rows = self.cursor.fetchall()

    # 打印结果
    for row in rows:
        id = row[0]
        name = row[1]
        age = row[2]
        print('id:', id, ',name:', name, 'age:', age)

如果需要按条件查询某一条记录，只需要修改 SQL 语句即可实现

# 按id查询
SQL_QUERY_WITH_CONDITION = "SELECT * FROM PEOPLE WHERE id={};"

# 查询id为5的记录
self.cursor.execute(SQL_QUERY_WITH_CONDITION.format(5))

3、更新

和 新增操作 类似，更新操作也是通过游标对象去执行更新的 SQL 语句，最后利用数据库连接对象将数据真实更新到数据库中

# 更新(通过id去更新)
SQL_UPDATE = "UPDATE PEOPLE SET name='%s',age=%s WHERE id=%s"

def update(self):
    """
    更新数据
    :return:
    """
    sql_update = SQL_UPDATE % ("王五五", 30, 5)
    print(sql_update)

    try:
        self.cursor.execute(sql_update)
        self.db.commit()
    except Exception as e:
        self.db.rollback()
        print('更新数据异常')
        print(e)

4、删除

删除操作同查询、新增操作类似，只需要变更 SQL 语句即可

# 删除（通过id去删除数据）
SQL_DELETE = "DELETE FROM PEOPLE WHERE id=%d"
​
def delete(self):
    """
    删除记录
    :return:
    """
    try:
        # 删除的完整sql
        sql_del = SQL_DELETE % (5)
        self.cursor.execute(sql_del)
        self.db.commit()
    except Exception as e:
        # 发生错误时回滚
        self.db.rollback()
        print(e)

最后，我们需要将游标对象和数据库连接对象资源释放掉

def teardown(self):
    # 释放资源
    self.cursor.close()
    self.db.close()

4. SQLAlchemy 首先，使用 SQLAlchemy 操作 Mysql 数据库同样先需要安装依赖库

# 安装依赖包
pip3 install sqlalchemy

通过 SQLAlchemy 的内置方法 declarative_base() 创建一个基础类 Base

然后，自定义一个 Base 类的子类，内部定义静态变量，和上面数据表 people 中的字段一一对应

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base

# 基础类
Base = declarative_base()


# 自定义的表
class People(Base):
    # 表名
    __tablename__ = 'people'

    # 定义字段
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    age = Column(Integer)

    def __repr__(self):
        """
        便于打印结果
        :return:
        """
        return "