Python读取MySQL数据：从入门到精通

# 1. Python连接MySQL数据库

在Python中，连接MySQL数据库需要使用第三方库，如pymysql或MySQLdb。下面以pymysql为例，介绍如何连接MySQL数据库。

import pymysql

# 连接数据库
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='password',
    database='test'
)

其中，`host`为MySQL服务器地址，`port`为MySQL服务器端口，`user`为MySQL用户名，`password`为MySQL密码，`database`为要连接的数据库名。

# 2. Python MySQL数据查询

在掌握了连接MySQL数据库的基本知识后，接下来我们将深入探讨Python中MySQL数据查询的操作。通过本章节的学习，你将了解如何使用Python执行基本和高级查询，包括条件查询、排序、分页和聚合函数的使用。

### 2.1 基本查询操作

#### 2.1.1 执行查询语句

执行查询语句是数据查询中最基本的操作。在Python中，可以使用`execute()`方法来执行查询语句。`execute()`方法接收一个字符串参数，该参数包含要执行的SQL查询语句。

import mysql.connector

# 连接数据库
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test_db"
)

# 创建游标
cursor = connection.cursor()

# 执行查询语句
cursor.execute("SELECT * FROM users")

#### 2.1.2 获取查询结果

执行查询语句后，可以使用`fetchall()`方法获取查询结果。`fetchall()`方法返回一个元组列表，其中每个元组代表一条查询结果。

# 获取查询结果
results = cursor.fetchall()

# 遍历查询结果
for result in results:
    print(result)

### 2.2 高级查询操作

#### 2.2.1 条件查询

条件查询允许你根据特定条件过滤查询结果。在Python中，可以使用`WHERE`子句来指定查询条件。

# 执行条件查询
cursor.execute("SELECT * FROM users WHERE age > 18")

#### 2.2.2 排序和分页

排序和分页可以帮助你控制查询结果的顺序和数量。

**排序**

使用`ORDER BY`子句对查询结果进行排序。

# 执行排序查询
cursor.execute("SELECT * FROM users ORDER BY age DESC")

**分页**

使用`LIMIT`和`OFFSET`子句对查询结果进行分页。

# 执行分页查询
cursor.execute("SELECT * FROM users LIMIT 10 OFFSET 20")

#### 2.2.3 聚合函数

聚合函数可以对查询结果进行聚合计算，例如求和、求平均值和求最大值。

# 执行聚合查询
cursor.execute("SELECT COUNT(*) FROM users")

# 3.1 单条数据插入

#### 3.1.1 使用execute()方法

`execute()`方法用于执行SQL语句，并可以插入单条数据。其语法格式如下：

cursor.execute(sql, args=None)

其中：

* `sql`：要执行的SQL语句。
* `args`：可选，要插入的数据，可以是元组或字典。

**示例：**

import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test')

# 获取游标
cursor = conn.cursor()

# 执行插入语句
sql = "INSERT INTO user (name, age) VALUES (%s, %s)"
args = ('张三', 20)
cursor.execute(sql, args)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

**逻辑分析：**

* 首先，建立与数据库的连接，并获取游标对象。
* 然后，使用`execute()`方法执行插入语句，并传入SQL语句和要插入的数据。
* 最后，提交事务，并关闭游标和连接。

#### 3.1.2 使用executemany()方法

`executemany()`方法用于执行SQL语句，并可以批量插入多条数据。其语法格式如下：

cursor.executemany(sql, args)

其中：

* `sql`：要执行的SQL语句。
* `args`：要插入的数据，是一个元组列表，每个元组代表一条数据。

**示例：**

import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test')

# 获取游标
cursor = conn.cursor()

# 执行插入语句
sql = "INSERT INTO user (name, age) VALUES (%s, %s)"
args = [('李四', 21), ('王五', 22)]
cursor.executemany(sql, args)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

**逻辑分析：**

* 首先，建立与数据库的连接，并获取游标对象。
* 然后，使用`executemany()`方法执行插入语句，并传入SQL语句和要插入的数据。
* 最后，提交事务，并关闭游标和连接。

# 4. Python MySQL数据更新

### 4.1 单条数据更新

#### 4.1.1 使用execute()方法

import mysql.connector

# 建立数据库连接
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="database_name"
)

# 创建游标
cursor = connection.cursor()

# 准备SQL语句
sql = "UPDATE table_name SET column_name = 'new_value' WHERE id = 1"

# 执行SQL语句
cursor.execute(sql)

# 提交事务
connection.commit()

# 关闭游标和数据库连接
cursor.close()
connection.close()

**逻辑分析：**

* `cursor.execute(sql)`：执行SQL语句，更新数据库中指定ID的数据。
* `connection.commit()`：提交事务，将更新操作永久保存到数据库中。

**参数说明：**

* `host`：数据库服务器地址。
* `user`：数据库用户名。
* `password`：数据库密码。
* `database_name`：要连接的数据库名称。
* `table_name`：要更新数据的表名。
* `column_name`：要更新的列名。
* `new_value`：要更新的新值。
* `id`：要更新数据的ID。

#### 4.1.2 使用executemany()方法

import mysql.connector

# 建立数据库连接
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="database_name"
)

# 创建游标
cursor = connection.cursor()

# 准备SQL语句
sql = "UPDATE table_name SET column_name = %s WHERE id = %s"

# 准备更新数据
data = [(new_value1, id1), (new_value2, id2), ...]

# 执行SQL语句
cursor.executemany(sql, data)

# 提交事务
connection.commit()

# 关闭游标和数据库连接
cursor.close()
connection.close()

**逻辑分析：**

* `cursor.executemany(sql, data)`：执行SQL语句，批量更新数据库中指定ID的数据。
* `connection.commit()`：提交事务，将更新操作永久保存到数据库中。

**参数说明：**

* `host`：数据库服务器地址。
* `user`：数据库用户名。
* `password`：数据库密码。
* `database_name`：要连接的数据库名称。
* `table_name`：要更新数据的表名。
* `column_name`：要更新的列名。
* `new_value`：要更新的新值。
* `id`：要更新数据的ID。

### 4.2 批量数据更新

#### 4.2.1 使用executemany()方法

import mysql.connector

# 建立数据库连接
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="database_name"
)

# 创建游标
cursor = connection.cursor()

# 准备SQL语句
sql = "UPDATE table_name SET column_name = %s WHERE id IN %s"

# 准备更新数据
data = [(new_value1, id1), (new_value2, id2), ...]
ids = (id1, id2, ...)

# 执行SQL语句
cursor.executemany(sql, zip(data, ids))

# 提交事务
connection.commit()

# 关闭游标和数据库连接
cursor.close()
connection.close()

**逻辑分析：**

* `cursor.executemany(sql, zip(data, ids))`：执行SQL语句，批量更新数据库中指定ID的数据。
* `connection.commit()`：提交事务，将更新操作永久保存到数据库中。

**参数说明：**

* `host`：数据库服务器地址。
* `user`：数据库用户名。
* `password`：数据库密码。
* `database_name`：要连接的数据库名称。
* `table_name`：要更新数据的表名。
* `column_name`：要更新的列名。
* `new_value`：要更新的新值。
* `id`：要更新数据的ID。

#### 4.2.2 使用load_data()方法

import mysql.connector

# 建立数据库连接
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="database_name"
)

# 创建游标
cursor = connection.cursor()

# 准备SQL语句
sql = "LOAD DATA INFILE 'data.csv' INTO TABLE table_name"

# 执行SQL语句
cursor.execute(sql)

# 提交事务
connection.commit()

# 关闭游标和数据库连接
cursor.close()
connection.close()

**逻辑分析：**

* `cursor.execute(sql)`：执行SQL语句，从CSV文件中批量更新数据库数据。
* `connection.commit()`：提交事务，将更新操作永久保存到数据库中。

**参数说明：**

* `host`：数据库服务器地址。
* `user`：数据库用户名。
* `password`：数据库密码。
* `database_name`：要连接的数据库名称。
* `table_name`：要更新数据的表名。
* `data.csv`：包含更新数据的CSV文件路径。

# 5. Python MySQL数据删除

### 5.1 单条数据删除

#### 5.1.1 使用execute()方法

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test"
)

# 创建游标
cursor = conn.cursor()

# 执行删除语句
sql = "DELETE FROM users WHERE id = 1"
cursor.execute(sql)

# 提交修改
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

**逻辑分析：**

1. `connect()` 方法用于连接到 MySQL 数据库，并返回一个连接对象。
2. `cursor()` 方法创建游标对象，用于执行 SQL 查询和操作。
3. `execute()` 方法执行指定的 SQL 语句，并返回受影响的行数。
4. `commit()` 方法提交对数据库所做的更改。
5. `close()` 方法关闭游标和连接。

**参数说明：**

* `host`：MySQL 数据库的主机地址。
* `user`：连接数据库的用户名。
* `password`：连接数据库的密码。
* `database`：要连接的数据库名称。
* `sql`：要执行的 SQL 删除语句。

#### 5.1.2 使用executemany()方法

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test"
)

# 创建游标
cursor = conn.cursor()

# 准备删除语句
sql = "DELETE FROM users WHERE id IN (%s, %s, %s)"

# 准备参数
ids = (1, 2, 3)

# 执行删除语句
cursor.executemany(sql, ids)

# 提交修改
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

**逻辑分析：**

1. `executemany()` 方法执行指定的 SQL 语句多次，每次使用一个不同的参数元组。
2. `prepare()` 方法准备要执行的 SQL 语句，并返回一个游标对象。
3. `execute()` 方法执行准备好的 SQL 语句，并返回受影响的行数。
4. `commit()` 方法提交对数据库所做的更改。
5. `close()` 方法关闭游标和连接。

**参数说明：**

* `host`：MySQL 数据库的主机地址。
* `user`：连接数据库的用户名。
* `password`：连接数据库的密码。
* `database`：要连接的数据库名称。
* `sql`：要执行的 SQL 删除语句。
* `ids`：要删除的记录的 ID 元组。

### 5.2 批量数据删除

#### 5.2.1 使用executemany()方法

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test"
)

# 创建游标
cursor = conn.cursor()

# 准备删除语句
sql = "DELETE FROM users WHERE id IN (%s, %s, %s)"

# 准备参数
ids = [(1, ), (2, ), (3, )]

# 执行删除语句
cursor.executemany(sql, ids)

# 提交修改
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

**逻辑分析：**

1. `executemany()` 方法执行指定的 SQL 语句多次，每次使用一个不同的参数元组。
2. `prepare()` 方法准备要执行的 SQL 语句，并返回一个游标对象。
3. `execute()` 方法执行准备好的 SQL 语句，并返回受影响的行数。
4. `commit()` 方法提交对数据库所做的更改。
5. `close()` 方法关闭游标和连接。

**参数说明：**

* `host`：MySQL 数据库的主机地址。
* `user`：连接数据库的用户名。
* `password`：连接数据库的密码。
* `database`：要连接的数据库名称。
* `sql`：要执行的 SQL 删除语句。
* `ids`：要删除的记录的 ID 元组。

#### 5.2.2 使用load_data()方法

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test"
)

# 创建游标
cursor = conn.cursor()

# 准备删除语句
sql = "LOAD DATA INFILE 'data.csv' INTO TABLE users FIELDS TERMINATED BY ',' IGNORE 1 LINES"

# 执行删除语句
cursor.execute(sql)

# 提交修改
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

**逻辑分析：**

1. `load_data()` 方法从 CSV 文件中加载数据到表中。
2. `execute()` 方法执行指定的 SQL 语句，并返回受影响的行数。
3. `commit()` 方法提交对数据库所做的更改。
4. `close()` 方法关闭游标和连接。

**参数说明：**

* `host`：MySQL 数据库的主机地址。
* `user`：连接数据库的用户名。
* `password`：连接数据库的密码。
* `database`：要连接的数据库名称。
* `sql`：要执行的 SQL 删除语句。

# 6. Python MySQL高级应用

### 6.1 存储过程和函数

#### 6.1.1 创建和调用存储过程

存储过程是一组预先编译的SQL语句，可以作为单个单元执行。它们通常用于执行复杂的操作或封装业务逻辑。

要创建存储过程，可以使用以下语法：

CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
    -- 存储过程代码
END

例如，创建一个名为 `get_customer_orders` 的存储过程，该存储过程获取给定客户的所有订单：

CREATE PROCEDURE get_customer_orders (IN customer_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = customer_id;
END

要调用存储过程，可以使用以下语法：

CALL procedure_name (parameter_list)

例如，调用 `get_customer_orders` 存储过程，并获取客户ID为1的所有订单：

CALL get_customer_orders(1)

#### 6.1.2 创建和调用函数

函数与存储过程类似，但它们返回一个值。要创建函数，可以使用以下语法：

CREATE FUNCTION function_name (parameter_list) RETURNS return_type
BEGIN
    -- 函数代码
    RETURN value;
END

例如，创建一个名为 `get_customer_name` 的函数，该函数返回给定客户的名称：

CREATE FUNCTION get_customer_name (IN customer_id INT) RETURNS VARCHAR(255)
BEGIN
    SELECT name FROM customers WHERE customer_id = customer_id;
END

要调用函数，可以使用以下语法：

SELECT function_name (parameter_list)

例如，调用 `get_customer_name` 函数，并获取客户ID为1的名称：

SELECT get_customer_name(1)

### 6.2 事务管理

事务是一组原子操作，要么全部成功，要么全部失败。在MySQL中，事务通过以下语句开始和提交：

START TRANSACTION;
COMMIT;

如果在事务期间发生错误，可以使用以下语句回滚事务：

ROLLBACK;

例如，使用事务将新订单插入数据库：

START TRANSACTION;
INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 2, 3);
COMMIT;

如果在插入过程中发生错误，可以使用 `ROLLBACK` 语句回滚事务，以确保数据库保持一致性。