Python连接MySQL、PostgreSQL、MongoDB等数据库 – wiki基地

作者： /

Python 连接主流数据库详解：MySQL、PostgreSQL 和 MongoDB

在数据驱动的时代，应用程序与数据库的交互是至关重要的。Python 作为一种流行的编程语言，提供了丰富的库和工具来连接和操作各种类型的数据库。本文将深入探讨如何使用 Python 连接三种主流的数据库系统：关系型数据库 MySQL 和 PostgreSQL，以及文档型数据库 MongoDB。我们将详细介绍每种数据库的连接方法、基本操作、最佳实践和常见问题。

一、 连接 MySQL 数据库

MySQL 是一种广泛使用的开源关系型数据库管理系统（RDBMS），以其可靠性、性能和易用性而闻名。Python 社区提供了多个用于连接 MySQL 的库，其中最受欢迎的是 mysql-connector-python（官方驱动）和 PyMySQL（纯 Python 实现）。

1.1 使用 mysql-connector-python

mysql-connector-python 是 Oracle 官方提供的 MySQL 驱动程序，它提供了对 MySQL 服务器的完整支持，包括最新的特性和安全协议。

安装：

bash
pip install mysql-connector-python

连接示例：

“`python
import mysql.connector

try:
mydb = mysql.connector.connect(
host=”localhost”, # 数据库主机地址
user=”yourusername”, # 数据库用户名
password=”yourpassword”, # 数据库密码
database=”yourdatabase” # 要连接的数据库名
)

print("成功连接到 MySQL 数据库！")

# 创建游标对象
cursor = mydb.cursor()

# 执行 SQL 查询
cursor.execute("SELECT * FROM yourtable")

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

# 遍历并打印结果
for row in results:
    print(row)

except mysql.connector.Error as err:
print(f”连接 MySQL 数据库时发生错误: {err}”)

finally:
if ‘mydb’ in locals() and mydb.is_connected():
cursor.close()
mydb.close()
print(“数据库连接已关闭。”)
“`

代码解释：

  • mysql.connector.connect(): 建立与 MySQL 数据库的连接。关键参数包括：
    • host: 数据库服务器的主机名或 IP 地址。
    • user: 数据库用户名。
    • password: 数据库密码。
    • database: 要连接的数据库名称。
    • port: 数据库端口号，默认为3306
  • mydb.cursor(): 创建一个游标对象，用于执行 SQL 语句和获取结果。
  • cursor.execute(): 执行 SQL 查询。
  • cursor.fetchall(): 获取所有查询结果。
  • cursor.fetchone(): 获取单条查询结果
  • cursor.fetchmany(size):获取指定数量的查询结果
  • mydb.close(): 关闭数据库连接。
  • 异常处理: 使用 try...except...finally 块来捕获和处理潜在的连接错误，并在最后确保关闭连接。

1.2 使用 PyMySQL

PyMySQL 是一个纯 Python 实现的 MySQL 客户端库，它兼容 mysql-connector-python 的 API，但在某些环境中可能更容易安装（例如，不需要 MySQL 客户端库）。

安装：

bash
pip install pymysql

连接示例：

“`python
import pymysql

try:
mydb = pymysql.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”yourdatabase”
)

print("成功连接到 MySQL 数据库！")

with mydb.cursor() as cursor:
    cursor.execute("SELECT * FROM yourtable")
    results = cursor.fetchall()
    for row in results:
        print(row)

except pymysql.MySQLError as err:
print(f”连接 MySQL 数据库时发生错误: {err}”)

finally:
if ‘mydb’ in locals():
mydb.close()
print(“数据库连接已关闭。”)
“`

代码解释：
与mysql-connector-python 的代码基本相同，主要区别在于导入的库和异常类型。

1.3 MySQL 基本操作
连接数据库后可以对数据库进行增删改查等操作。

创建表：
python
cursor.execute("CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

插入数据：
“`python
sql = “INSERT INTO customers (name, address) VALUES (%s, %s)”
val = (“John Doe”, “Highway 37”)
cursor.execute(sql, val)
mydb.commit() # 提交更改
print(cursor.rowcount, “记录插入成功.”)

插入多条记录

sql = “INSERT INTO customers (name, address) VALUES (%s, %s)”
val = [
(‘Peter’, ‘Lowstreet 4’),
(‘Amy’, ‘Apple st 652’)
]
cursor.executemany(sql, val) # executemany用于插入多条记录
mydb.commit()
print(cursor.rowcount, “记录插入成功.”)

“`

更新数据：

python
sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Highway 37")
cursor.execute(sql, val)
mydb.commit()
print(cursor.rowcount, "记录被修改.")

删除数据：
python
sql = "DELETE FROM customers WHERE address = %s"
val = ("Highway 37",)
cursor.execute(sql, val)
mydb.commit()
print(cursor.rowcount, "记录被删除.")

二、 连接 PostgreSQL 数据库

PostgreSQL 是一个功能强大的开源对象关系型数据库系统（ORDBMS），以其对 SQL 标准的严格遵守、数据完整性和可扩展性而著称。Python 连接 PostgreSQL 最常用的库是 psycopg2。

2.1 使用 psycopg2

psycopg2 是最流行的 PostgreSQL 适配器，它提供了对 PostgreSQL 的全面支持，并针对性能进行了优化。

安装：

bash
pip install psycopg2-binary # 通常建议安装二进制版本
如果安装二进制包失败，可以安装源码包,但需要先安装PostgreSQL开发库
“`bash

例如在 Ubuntu/Debian 上：

sudo apt-get install libpq-dev

pip install psycopg2

“`

连接示例：

“`python
import psycopg2

try:
mydb = psycopg2.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”yourdatabase”,
port=”5432″ #PostgreSQL默认端口号为5432
)

print("成功连接到 PostgreSQL 数据库！")

with mydb.cursor() as cursor:
    cursor.execute("SELECT * FROM yourtable")
    results = cursor.fetchall()
    for row in results:
        print(row)

except psycopg2.Error as err:
print(f”连接 PostgreSQL 数据库时发生错误: {err}”)

finally:
if ‘mydb’ in locals():
mydb.close()
print(“数据库连接已关闭。”)
“`

代码解释：

  • psycopg2.connect(): 建立与 PostgreSQL 数据库的连接。参数与 MySQL 类似。
  • mydb.cursor(): 创建游标对象。
  • cursor.execute(): 执行 SQL 查询。
  • cursor.fetchall(): 获取所有查询结果。
  • mydb.close(): 关闭数据库连接。
  • 异常处理: 使用 try...except...finally 块。

2.2 PostgreSQL 基本操作

PostgreSQL的基本操作与MySQL类似，以下是一些示例：

创建表：

python
cursor.execute("CREATE TABLE IF NOT EXISTS products (id SERIAL PRIMARY KEY, name VARCHAR(255), price INTEGER)")

插入数据：

“`python
sql = “INSERT INTO products (name, price) VALUES (%s, %s)”
val = (“Laptop”, 1200)
cursor.execute(sql, val)
mydb.commit()

插入多条

sql = “INSERT INTO products (name, price) VALUES (%s, %s)”
val = [
(“Mouse”, 25),
(“Keyboard”, 75)
]
cursor.executemany(sql, val)
mydb.commit()
“`

查询数据：
“`python

查询所有

cursor.execute(“SELECT * from products”)
result = cursor.fetchall()

条件查询

cursor.execute(“SELECT * FROM products WHERE price > %s”, (50,))
result = cursor.fetchall()
“`
更新数据：

python
sql = "UPDATE products SET price = %s WHERE name = %s"
val = (1500, "Laptop")
cursor.execute(sql, val)
mydb.commit()
删除数据：
python
sql = "DELETE FROM products WHERE id = %s"
val = (1,)
cursor.execute(sql,val)
mydb.commit()

三、 连接 MongoDB 数据库

MongoDB 是一种流行的 NoSQL 文档型数据库，它以其灵活性、可扩展性和高性能而受到开发者的欢迎。Python 连接 MongoDB 最常用的库是 pymongo。

3.1 使用 pymongo

pymongo 是 MongoDB 官方提供的 Python 驱动程序，它提供了对 MongoDB 的完整支持。

安装：

bash
pip install pymongo

连接示例：

“`python
from pymongo import MongoClient

try:
# 连接到本地 MongoDB 服务器
client = MongoClient(‘mongodb://localhost:27017/’) # 默认端口是 27017

# 选择数据库
mydb = client['yourdatabase']

# 选择集合（相当于关系型数据库中的表）
mycollection = mydb['yourcollection']

print("成功连接到 MongoDB 数据库！")

# 查找所有文档
for document in mycollection.find():
    print(document)

# 查找符合条件的文档
for document in mycollection.find({"name": "John Doe"}):
    print(document)

except Exception as err:
print(f”连接 MongoDB 数据库时发生错误: {err}”)

finally:
client.close()
print(“数据库连接已关闭。”)
“`

代码解释：

  • MongoClient('mongodb://localhost:27017/'): 建立与 MongoDB 服务器的连接。
    也可以用多个参数分别指定host, port, username, password等
  • client['yourdatabase']: 选择要使用的数据库。
  • mydb['yourcollection']: 选择要使用的集合（collection）。
  • mycollection.find(): 查找集合中的所有文档。
  • mycollection.find({"name": "John Doe"}): 查找 name 字段为 “John Doe” 的文档。
  • 异常处理: 捕获并处理连接错误。

3.2 MongoDB 基本操作

插入文档：
“`python

插入单个文档

mydict = { “name”: “John Doe”, “address”: “Highway 37” }
x = mycollection.insert_one(mydict)
print(x.inserted_id)

插入多个文档

mylist = [
{ “name”: “Amy”, “address”: “Apple st 652”},
{ “name”: “Hannah”, “address”: “Mountain 21”}
]
x = mycollection.insert_many(mylist)
print(x.inserted_ids)
“`

更新文档：
“`python

更新单个文档

myquery = { “address”: “Highway 37” }
newvalues = { “$set”: { “address”: “Canyon 123” } }
mycollection.update_one(myquery, newvalues)

更新多个文档

myquery = { “address”: { “$regex”: “^S” } } # 使用正则表达式
newvalues = { “$set”: { “name”: “Minnie” } }
x = mycollection.update_many(myquery, newvalues)
print(x.modified_count, “文档被更新”)
“`

删除文档：

“`python

删除单个文档

myquery = { “address”: “Mountain 21” }
mycollection.delete_one(myquery)

删除多个文档

myquery = { “address”: {“$regex”: “^S”} }
x = mycollection.delete_many(myquery)
print(x.deleted_count, “文档被删除”)

删除所有文档

x = mycollection.delete_many({})
print(x.deleted_count, “文档被删除”)
“`

四、最佳实践和常见问题

4.1 最佳实践

  • 使用连接池： 对于高并发的应用程序，使用连接池可以显著提高性能和资源利用率。连接池维护一组可重用的数据库连接，避免了频繁创建和销毁连接的开销。对于MySQL可以使用mysql.connector.pooling。对于PostgreSQL可以使用psycopg2.pool。
  • 参数化查询： 使用参数化查询（Parameterized Queries）或预处理语句（Prepared Statements）可以防止 SQL 注入攻击，并提高查询效率。永远不要直接将用户输入拼接到 SQL 语句中。
  • 事务管理： 对于需要保证数据一致性的操作，使用事务来确保一系列操作要么全部成功，要么全部回滚。
  • 适当处理大数据： 对于大数据量的查询，使用分页、游标迭代或其他优化技术来避免一次性加载过多数据到内存中。
  • 关闭连接: 使用完数据库后，记得要关闭连接，释放资源

4.2 常见问题

  • 连接超时： 检查数据库服务器是否可访问、网络是否稳定、防火墙设置是否正确。
  • 权限不足： 确保使用的数据库用户具有足够的权限来执行相应的操作。
  • 编码问题： 如果遇到乱码问题，确保数据库、客户端和应用程序的字符编码设置一致（通常推荐使用 UTF-8）。
  • SQL 注入： 使用参数化查询或预处理语句来防止 SQL 注入攻击。
  • 死锁： 在并发环境中，多个事务可能因为相互等待对方持有的资源而导致死锁。合理设计事务逻辑和锁机制可以减少死锁的发生。

总结

本文详细介绍了如何使用 Python 连接和操作 MySQL、PostgreSQL 和 MongoDB 三种主流数据库。通过掌握这些基本技能，开发者可以构建各种数据驱动的应用程序，并有效地管理和利用数据。请记住，在实际开发中，除了掌握基本操作外，还需要关注数据库的性能优化、安全性和可维护性。希望本文能帮助你更好地理解和应用 Python 数据库编程。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部