有的时候博客内容会有变动，首发博客是最新的，其他博客地址可能会未同步, 认准 https://blog.zysicyj.top
可点击链接 https://blog-1253652709.cos.ap-guangzhou.myqcloud.com//picgo/202401180921373.png 解答疑问
mysql.connector 是 MySQL 官方提供的用于 Python 连接 MySQL 数据库的库。本文将详细介绍如何使用 mysql.connector 进行各种数据库操作，包括连接数据库、执行查询、插入数据、更新数据、删除数据和事务处理等。
1. 安装 mysql-connector-python
在使用 mysql.connector 之前，首先需要安装 mysql-connector-python 包。可以使用以下命令安装：
1
| pip install mysql-connector-python
|
2. 连接数据库
连接 MySQL 数据库的基本步骤如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| import mysql.connector
from mysql.connector import Error
try:
connection = mysql.connector.connect(
host='localhost',
database='test_db',
user='root',
password='password'
)
if connection.is_connected():
print("Successfully connected to the database")
except Error as e:
print(f"Error: {e}")
finally:
if connection.is_connected():
connection.close()
print("Connection closed")
|
上述代码展示了如何连接到一个名为 test_db 的数据库，并在连接成功后关闭连接。
3. 执行查询
执行查询操作包括查询数据和获取结果集。可以使用 cursor 对象来执行 SQL 语句。
查询数据：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| try:
connection = mysql.connector.connect(
host='localhost',
database='test_db',
user='root',
password='password'
)
if connection.is_connected():
cursor = connection.cursor()
cursor.execute("SELECT * FROM employees")
result = cursor.fetchall()
for row in result:
print(row)
except Error as e:
print(f"Error: {e}")
finally:
if connection.is_connected():
cursor.close()
connection.close()
|
获取单行结果：
1
2
3
| cursor.execute("SELECT * FROM employees WHERE employee_id = 1")
row = cursor.fetchone()
print(row)
|
4. 插入数据
插入数据使用 INSERT INTO 语句，并通过 execute 方法执行。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| try:
connection = mysql.connector.connect(
host='localhost',
database='test_db',
user='root',
password='password'
)
if connection.is_connected():
cursor = connection.cursor()
insert_query = """
INSERT INTO employees (name, department, salary)
VALUES (%s, %s, %s)
"""
record = ("John Doe", "HR", 7000)
cursor.execute(insert_query, record)
connection.commit()
print("Record inserted successfully")
except Error as e:
print(f"Error: {e}")
finally:
if connection.is_connected():
cursor.close()
connection.close()
|
5. 更新数据
更新数据使用 UPDATE 语句。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| try:
connection = mysql.connector.connect(
host='localhost',
database='test_db',
user='root',
password='password'
)
if connection.is_connected():
cursor = connection.cursor()
update_query = """
UPDATE employees
SET salary = %s
WHERE employee_id = %s
"""
data = (8000, 1)
cursor.execute(update_query, data)
connection.commit()
print("Record updated successfully")
except Error as e:
print(f"Error: {e}")
finally:
if connection.is_connected():
cursor.close()
connection.close()
|
6. 删除数据
删除数据使用 DELETE FROM 语句。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| try:
connection = mysql.connector.connect(
host='localhost',
database='test_db',
user='root',
password='password'
)
if connection.is_connected():
cursor = connection.cursor()
delete_query = """
DELETE FROM employees
WHERE employee_id = %s
"""
cursor.execute(delete_query, (1,))
connection.commit()
print("Record deleted successfully")
except Error as e:
print(f"Error: {e}")
finally:
if connection.is_connected():
cursor.close()
connection.close()
|
7. 事务处理
事务处理可以确保一组 SQL 语句要么全部执行，要么全部不执行。通过 commit 和 rollback 方法来管理事务。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| try:
connection = mysql.connector.connect(
host='localhost',
database='test_db',
user='root',
password='password'
)
if connection.is_connected():
cursor = connection.cursor()
cursor.execute("START TRANSACTION")
cursor.execute("UPDATE employees SET salary = salary + 1000 WHERE department = 'HR'")
cursor.execute("DELETE FROM employees WHERE employee_id = 2")
connection.commit()
print("Transaction committed successfully")
except Error as e:
connection.rollback()
print(f"Transaction failed and rolled back. Error: {e}")
finally:
if connection.is_connected():
cursor.close()
connection.close()
|
8. 使用连接池
使用连接池可以提高数据库连接的性能和可伸缩性。可以使用 mysql.connector.pooling 模块来实现连接池。
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
| from mysql.connector import pooling
connection_pool = pooling.MySQLConnectionPool(
pool_name="mypool",
pool_size=5,
host='localhost',
database='test_db',
user='root',
password='password'
)
try:
connection = connection_pool.get_connection()
if connection.is_connected():
cursor = connection.cursor()
cursor.execute("SELECT * FROM employees")
result = cursor.fetchall()
for row in result:
print(row)
except Error as e:
print(f"Error: {e}")
finally:
if connection.is_connected():
cursor.close()
connection.close()
|
9. 处理大数据集
对于大数据集，使用 fetchmany 方法可以有效控制每次获取的数据量，避免内存问题。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| try:
connection = mysql.connector.connect(
host='localhost',
database='test_db',
user='root',
password='password'
)
if connection.is_connected():
cursor = connection.cursor()
cursor.execute("SELECT * FROM large_table")
while True:
rows = cursor.fetchmany(size=1000)
if not rows:
break
for row in rows:
print(row)
except Error as e:
print(f"Error: {e}")
finally:
if connection.is_connected():
cursor.close()
connection.close()
|
总结
本文详细介绍了如何使用 mysql.connector 操作 MySQL 数据库。通过对连接数据库、执行查询、插入数据、更新数据、删除数据、事务处理、使用连接池和处理大数据集等各个方面的介绍，相信你已经掌握了使用 mysql.connector 进行数据库操作的各种技巧。希望这些内容能对你的工作有所帮助。
