Python简单连接MySQL和PostgreSQL查询

依赖版本

pymysql
psycopg2-binary

1、代码

from sqlalchemy import create_engine, text
from sqlalchemy.engine import Engine
import uuid
from datetime import datetime


class DatabaseManager:
    def __init__(self):
        # 用于缓存不同的连接池，key 可以是 url
        self._engines = {}

    def get_engine(self, db_type, username, password, host, port, db_name) -> Engine:
        """
        动态构建 URL 并返回/创建连接池
        支持 mysql+pymysql, postgresql+psycopg2 等
        """
        # 1. 构建标准连接字符串
        url = f"{db_type}://{username}:{password}@{host}:{port}/{db_name}"

        # 2. 如果已经创建过，直接返回，避免重复创建连接池导致内存泄漏
        if url not in self._engines:
            print(f"--- 正在为 {db_name} 创建新的连接池 ---")
            self._engines[url] = create_engine(
                url,
                pool_size=10,
                max_overflow=5,
                pool_recycle=3600,
                pool_pre_ping=True
            )

        return self._engines[url]


# 使用示例
db_manager = DatabaseManager()

# 动态传入参数
mysql_db = db_manager.get_engine("mysql+pymysql", "root", "mysql123456", "127.0.0.1", "3306", "chaiys")
# 修改点：
# 1. 协议改为 postgresql+psycopg2
# 2. 端口改为 5432
postgre_db = db_manager.get_engine(
    "postgresql+psycopg2",
    "sjck",           # Docker 中的 POSTGRES_USER
    "sjck_2026",       # Docker 中的 POSTGRES_PASSWORD
    "127.0.0.1",
    "5432",
    "chaiys"        # Docker 中的 POSTGRES_DB
)

def add_employee(conn, emp_id, name, salary):
    stmt = text("INSERT INTO employee (id, name, salary, create_time) VALUES (:id, :name, :salary, :create_time)")
    conn.execute(stmt, {"id": emp_id, "name": name, "salary": salary, "create_time": datetime.now()})
    conn.commit()
    print(f"员工 {name} 添加成功")

def get_employees(conn):
    stmt = text("SELECT id, name, salary, create_time FROM employee")
    result = conn.execute(stmt)

    # 映射为字典格式，方便后续处理
    for row in result.mappings():
        print(f"ID: {row['id']}, 姓名: {row['name']}, 薪水: {row['salary']}, 创建时间: {row['create_time']}")

def update_salary(conn, emp_id, new_salary):
    stmt = text("UPDATE employee SET salary = :salary WHERE id = :id")
    result = conn.execute(stmt, {"salary": new_salary, "id": emp_id})
    conn.commit()
    print(f"更新成功，影响行数: {result.rowcount}")

def delete_employee(conn, emp_id):
    stmt = text("DELETE FROM employee WHERE id = :id")
    conn.execute(stmt, {"id": emp_id})
    conn.commit()
    print(f"员工 {emp_id} 已删除")

with mysql_db.connect() as conn:
    new_id = str(uuid.uuid4())
    new_id2 = str(uuid.uuid4())
    add_employee(conn, new_id, "张三", 15000.50)
    add_employee(conn, new_id2, "李四", 3000)
    get_employees(conn)
    update_salary(conn, new_id, 4000)
    update_salary(conn, new_id2, 4001)
    get_employees(conn)
    delete_employee(conn, new_id)
    delete_employee(conn, new_id2)
    get_employees(conn)
    print(f"结束")

with postgre_db.connect() as conn:
    new_id = str(uuid.uuid4())
    new_id2 = str(uuid.uuid4())
    add_employee(conn, new_id, "张三", 15000.50)
    add_employee(conn, new_id2, "李四", 3000)
    get_employees(conn)
    update_salary(conn, new_id, 4000)
    update_salary(conn, new_id2, 4001)
    get_employees(conn)
    delete_employee(conn, new_id)
    delete_employee(conn, new_id2)
    get_employees(conn)
    print(f"结束")

2、测试

--- 正在为 chaiys 创建新的连接池 ---
--- 正在为 chaiys 创建新的连接池 ---
员工 张三 添加成功
员工 李四 添加成功
ID: 2026c321-b75c-4932-9f85-98c51e3070ee, 姓名: 张三, 薪水: 15000.50, 创建时间: 2026-01-07 16:19:13
ID: 87ec3d20-9354-4c1f-b6fd-a9e3ee3a5826, 姓名: 李四, 薪水: 3000.00, 创建时间: 2026-01-07 16:19:13
更新成功，影响行数: 1
更新成功，影响行数: 1
ID: 2026c321-b75c-4932-9f85-98c51e3070ee, 姓名: 张三, 薪水: 4000.00, 创建时间: 2026-01-07 16:19:13
ID: 87ec3d20-9354-4c1f-b6fd-a9e3ee3a5826, 姓名: 李四, 薪水: 4001.00, 创建时间: 2026-01-07 16:19:13
员工 2026c321-b75c-4932-9f85-98c51e3070ee 已删除
员工 87ec3d20-9354-4c1f-b6fd-a9e3ee3a5826 已删除
结束
员工 张三 添加成功
员工 李四 添加成功
ID: 67f971e9-8105-4984-8413-cda76404dea3, 姓名: 张三, 薪水: 15000.50, 创建时间: 2026-01-07 16:19:12.580063
ID: 5d6a31c3-ae1d-46a0-a5d5-35477b444a84, 姓名: 李四, 薪水: 3000.00, 创建时间: 2026-01-07 16:19:12.582127
更新成功，影响行数: 1
更新成功，影响行数: 1
ID: 67f971e9-8105-4984-8413-cda76404dea3, 姓名: 张三, 薪水: 4000.00, 创建时间: 2026-01-07 16:19:12.580063
ID: 5d6a31c3-ae1d-46a0-a5d5-35477b444a84, 姓名: 李四, 薪水: 4001.00, 创建时间: 2026-01-07 16:19:12.582127
员工 67f971e9-8105-4984-8413-cda76404dea3 已删除
员工 5d6a31c3-ae1d-46a0-a5d5-35477b444a84 已删除
结束