代码收藏家 Python连接MySQL数据库详解
Python 可以通过多个库与 MySQL 数据库进行交互。最常用的 MySQL 连接库是 mysql-connector-python 和 PyMySQL，这两个库提供了与 MySQL 数据库进行通信的功能。这里将介绍如何使用这两个库来进行基本的数据库操作。
1. 安装 MySQL 连接库
在使用 MySQL 之前，首先需要安装一个连接库。可以使用 pip 来安装。
使用 mysql-connector-python 安装：
pip install mysql-connector-python
使用 PyMySQL 安装：
pip install pymysql
2. 连接 MySQL 数据库
连接到 MySQL 数据库时，你需要提供 MySQL 服务器的地址、用户名、密码和数据库名称。
2.1 使用 mysql-connector-python
import mysql.connector
# 连接到数据库
conn = mysql.connector.connect(
host="localhost", # MySQL 服务器地址
user="root", # 用户名
password="password", # 密码
database="testdb" # 数据库名称
)
# 创建一个游标对象
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM users")
# 获取结果
result = cursor.fetchall()
for row in result:
print(row)
# 关闭连接
cursor.close()
conn.close()
2.2 使用 PyMySQL
import pymysql
# 连接到数据库
conn = pymysql.connect(
host="localhost", # MySQL 服务器地址
user="root", # 用户名
password="password", # 密码
database="testdb" # 数据库名称
)
# 创建一个游标对象
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM users")
# 获取结果
result = cursor.fetchall()
for row in result:
print(row)
# 关闭连接
cursor.close()
conn.close()
3. 执行 SQL 查询
一旦成功连接到数据库，你可以执行 SQL 查询（如 SELECT, INSERT, UPDATE, DELETE 等）。以下是一些常见的查询操作。
3.1 执行 SELECT 查询
# SELECT 查询
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall() # 获取所有记录
for row in rows:
print(row)
3.2 执行 INSERT 查询
# INSERT 插入数据
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("John", 25))
conn.commit() # 提交事务
print("Data inserted")
3.3 执行 UPDATE 查询
# UPDATE 更新数据
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (30, "John"))
conn.commit() # 提交事务
print("Data updated")
3.4 执行 DELETE 查询
# DELETE 删除数据
cursor.execute("DELETE FROM users WHERE name = %s", ("John",))
conn.commit() # 提交事务
print("Data deleted")
4. 处理数据库错误和异常
在执行 SQL 操作时，我们需要处理异常，以确保数据库操作的稳定性。try 和 except 语句用于捕获异常并进行处理。
import mysql.connector
from mysql.connector import Error
try:
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="testdb"
)
if conn.is_connected():
print("Connected to MySQL database")
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
except Error as e:
print("Error while connecting to MySQL", e)
finally:
if conn.is_connected():
cursor.close()
conn.close()
print("MySQL connection is closed")
5. 使用事务（Transaction）
事务是由一组 SQL 语句组成的操作，这些操作要么全部成功，要么全部失败。事务通常用于确保数据的完整性。
try:
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="testdb"
)
cursor = conn.cursor()
# 开始事务
conn.start_transaction()
# 执行多个操作
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Alice", 28))
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (29, "Bob"))
# 提交事务
conn.commit()
except mysql.connector.Error as err:
print(f"Error: {err}")
# 出现错误时回滚事务
conn.rollback()
finally:
cursor.close()
conn.close()
6. SQL 注入防范
SQL 注入是通过操纵 SQL 查询来非法访问数据库的一种攻击方式。为了防止 SQL 注入，必须使用参数化查询，而不是将用户输入直接拼接到 SQL 语句中。
# 不建议的做法（容易遭受 SQL 注入攻击）
# cursor.execute("SELECT * FROM users WHERE name = '" + user_input + "'")
# 推荐的做法（使用参数化查询）
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))
7. 获取数据库元数据
你可以使用 cursor.description 获取查询结果的列信息。
cursor.execute("SELECT * FROM users")
columns = [column[0] for column in cursor.description]
print("Columns:", columns)
8. 使用连接池
对于生产环境，使用连接池来管理数据库连接是一种更高效的方式，避免了频繁创建和销毁连接的开销。mysql-connector-python 提供了连接池功能。
from mysql.connector import pooling
# 创建连接池
dbconfig = {
"host": "localhost",
"user": "root",
"password": "password",
"database": "testdb"
}
pool = pooling.MySQLConnectionPool(pool_name="mypool", pool_size=5, **dbconfig)
# 从连接池获取连接
conn = pool.get_connection()
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
9. 常用 SQL 语句示例
查询所有数据：
SELECT * FROM table_name;
条件查询：
SELECT * FROM users WHERE age > 20;
排序查询：
SELECT * FROM users ORDER BY age DESC;
分页查询：
SELECT * FROM users LIMIT 10 OFFSET 20;
连接查询（JOIN）：
SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;
10. 总结
作者：泥鳅炖土豆
