在Python开发中，数据库操作是核心技能之一。本文将详细介绍如何使用Python连接MySQL和MongoDB数据库，并实现数据的增删改查操作。通过完整的代码示例，帮助开发者快速掌握这两种主流数据库的Python操作方法。
一、MySQL数据库操作
1. 环境准备
首先需要安装PyMySQL库，这是Python操作MySQL最常用的驱动：
bash
1pip install pymysql
22. 数据库连接
python
1import pymysql
2
3def connect_mysql():
4 try:
5 # 数据库配置
6 config = {
7 "host": "localhost",
8 "port": 3306,
9 "user": "root",
10 "password": "your_password",
11 "database": "test_db",
12 "charset": "utf8mb4"
13 }
14
15 # 建立连接
16 conn = pymysql.connect(**config)
17 print("MySQL连接成功！")
18 return conn
19 except Exception as e:
20 print(f"MySQL连接失败：{e}")
21 return None
223. 创建表结构
python
1def create_table(conn):
2 try:
3 cursor = conn.cursor()
4 create_sql = """
5 CREATE TABLE IF NOT EXISTS users (
6 id INT AUTO_INCREMENT PRIMARY KEY,
7 name VARCHAR(50) NOT NULL,
8 age INT,
9 email VARCHAR(100)
10 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
11 """
12 cursor.execute(create_sql)
13 print("表创建成功！")
14 except Exception as e:
15 print(f"创建表失败：{e}")
16 finally:
17 cursor.close()
184. 数据操作
插入数据
python
1def insert_data(conn):
2 try:
3 cursor = conn.cursor()
4 insert_sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
5 data = ("张三", 25, "zhangsan@example.com")
6 cursor.execute(insert_sql, data)
7 conn.commit() # 提交事务
8 print(f"插入成功，影响行数：{cursor.rowcount}")
9 except Exception as e:
10 conn.rollback() # 回滚事务
11 print(f"插入失败：{e}")
12 finally:
13 cursor.close()
14查询数据
python
1def query_data(conn):
2 try:
3 cursor = conn.cursor(pymysql.cursors.DictCursor) # 返回字典格式
4 query_sql = "SELECT * FROM users WHERE age > %s"
5 cursor.execute(query_sql, (20,))
6 results = cursor.fetchall()
7
8 print("查询结果：")
9 for row in results:
10 print(row)
11 except Exception as e:
12 print(f"查询失败：{e}")
13 finally:
14 cursor.close()
15更新数据
python
1def update_data(conn):
2 try:
3 cursor = conn.cursor()
4 update_sql = "UPDATE users SET age = %s WHERE name = %s"
5 cursor.execute(update_sql, (26, "张三"))
6 conn.commit()
7 print(f"更新成功，影响行数：{cursor.rowcount}")
8 except Exception as e:
9 conn.rollback()
10 print(f"更新失败：{e}")
11 finally:
12 cursor.close()
13删除数据
python
1def delete_data(conn):
2 try:
3 cursor = conn.cursor()
4 delete_sql = "DELETE FROM users WHERE name = %s"
5 cursor.execute(delete_sql, ("张三",))
6 conn.commit()
7 print(f"删除成功，影响行数：{cursor.rowcount}")
8 except Exception as e:
9 conn.rollback()
10 print(f"删除失败：{e}")
11 finally:
12 cursor.close()
135. 完整示例
python
1if __name__ == "__main__":
2 conn = connect_mysql()
3 if conn:
4 try:
5 create_table(conn)
6 insert_data(conn)
7 query_data(conn)
8 update_data(conn)
9 query_data(conn)
10 delete_data(conn)
11 query_data(conn)
12 finally:
13 conn.close()
14 print("MySQL连接已关闭")
15二、MongoDB数据库操作
1. 环境准备
安装PyMongo库：
bash
1pip install pymongo
22. 数据库连接
python
1from pymongo import MongoClient
2
3def connect_mongodb():
4 try:
5 # 连接MongoDB（默认本地27017端口）
6 client = MongoClient("mongodb://localhost:27017/")
7 print("MongoDB连接成功！")
8 return client
9 except Exception as e:
10 print(f"MongoDB连接失败：{e}")
11 return None
123. 数据操作
插入数据
python
1def insert_mongodb(client):
2 try:
3 db = client["test_db"] # 选择/创建数据库
4 collection = db["users"] # 选择/创建集合
5
6 # 插入单个文档
7 user1 = {"name": "李四", "age": 30, "email": "lisi@example.com"}
8 result1 = collection.insert_one(user1)
9 print(f"插入单个文档ID：{result1.inserted_id}")
10
11 # 插入多个文档
12 users = [
13 {"name": "王五", "age": 28, "email": "wangwu@example.com"},
14 {"name": "赵六", "age": 35, "email": "zhaoliu@example.com"}
15 ]
16 result2 = collection.insert_many(users)
17 print(f"插入多个文档IDs：{result2.inserted_ids}")
18 except Exception as e:
19 print(f"插入失败：{e}")
20查询数据
python
1def query_mongodb(client):
2 try:
3 db = client["test_db"]
4 collection = db["users"]
5
6 # 查询所有文档
7 print("所有用户：")
8 for user in collection.find():
9 print(user)
10
11 # 条件查询
12 print("\n年龄大于30的用户：")
13 for user in collection.find({"age": {"$gt": 30}}):
14 print(user)
15
16 # 查询单个文档
17 print("\n查询单个用户：")
18 user = collection.find_one({"name": "李四"})
19 print(user)
20 except Exception as e:
21 print(f"查询失败：{e}")
22更新数据
python
1def update_mongodb(client):
2 try:
3 db = client["test_db"]
4 collection = db["users"]
5
6 # 更新单个文档
7 result1 = collection.update_one(
8 {"name": "李四"},
9 {"$set": {"age": 31}}
10 )
11 print(f"更新文档数：{result1.modified_count}")
12
13 # 更新多个文档
14 result2 = collection.update_many(
15 {"age": {"$lt": 30}},
16 {"$set": {"status": "young"}}
17 )
18 print(f"更新多个文档数：{result2.modified_count}")
19 except Exception as e:
20 print(f"更新失败：{e}")
21删除数据
python
1def delete_mongodb(client):
2 try:
3 db = client["test_db"]
4 collection = db["users"]
5
6 # 删除单个文档
7 result1 = collection.delete_one({"name": "赵六"})
8 print(f"删除文档数：{result1.deleted_count}")
9
10 # 删除多个文档
11 result2 = collection.delete_many({"status": "young"})
12 print(f"删除多个文档数：{result2.deleted_count}")
13 except Exception as e:
14 print(f"删除失败：{e}")
154. 完整示例
python
1if __name__ == "__main__":
2 client = connect_mongodb()
3 if client:
4 try:
5 insert_mongodb(client)
6 query_mongodb(client)
7 update_mongodb(client)
8 query_mongodb(client)
9 delete_mongodb(client)
10 query_mongodb(client)
11 finally:
12 client.close()
13 print("MongoDB连接已关闭")
14三、总结
本文详细介绍了Python操作MySQL和MongoDB数据库的完整流程，包括：
- 数据库连接和配置
- 表/集合的创建
- 数据的增删改查操作
- 事务管理（MySQL）
- 查询条件的使用
- 连接的安全关闭
开发者可以根据实际需求选择合适的数据库：
- MySQL：适合结构化数据，需要严格模式的关系型数据库
- MongoDB：适合非结构化或半结构化数据，需要灵活模式的文档型数据库
通过掌握这些基础操作，可以构建更复杂的数据处理应用，为后续的Web开发、数据分析等项目打下坚实基础。
