SQLAlchemy ​不依赖 ORM，通过核心接口执行原生 SQL 语句的增删改查操作

1、安装SQLAlchemy和MySQL驱动

pip install SQLAlchemy
pip install pymysql

2、数据库连接

from sqlalchemy import create_engine

# 连接字符串格式: mysql+pymysql://<用户名>:<密码>@<主机>/<数据库名>?参数
engine = create_engine(
    'mysql+pymysql://root:password@localhost/mydb',
    echo=True,  # 显示执行的 SQL
    pool_size=10,  # 连接池大小
    pool_recycle=3600  # 连接回收时间（秒）
)

# 获取数据库连接
conn = engine.connect()

3、 执行 DDL（表结构操作）

创建表

from sqlalchemy import text

# 创建 users 表（MySQL 语法）
conn.execute(text("""
    CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) NOT NULL UNIQUE,
        email VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""))
conn.commit()

​删除表

conn.execute(text("DROP TABLE IF EXISTS users"))
conn.commit()

4. 增删改查（CRUD）操作

插入数据 (INSERT)

# 单条插入，使用text() :param 占位符防止 SQL 注入
insert_sql = text("""
    INSERT INTO users (username, email) 
    VALUES (:username, :email)
""")
conn.execute(insert_sql, {"username": "john_doe", "email": "john@example.com"})

# 批量插入（高性能）
users = [
    {"username": "alice", "email": "alice@example.com"},
    {"username": "bob", "email": "bob@example.com"}
]
conn.execute(insert_sql, users)  # 自动批量处理
conn.commit()

查询数据 (SELECT)

# 基础查询
result = conn.execute(text("SELECT * FROM users WHERE id = :id"), {"id": 1})
row = result.fetchone()
print(row)  # 输出：(1, 'john_doe', 'john@example.com', datetime.datetime(...))

# 分页查询（MySQL 的 LIMIT 语法）
result = conn.execute(text("""
    SELECT * FROM users 
    ORDER BY created_at DESC 
    LIMIT :limit OFFSET :offset
"""), {"limit": 10, "offset": 0})
for row in result:
    print(row.username, row.email)

# IN 查询（参数为元组）
ids = (1, 2, 3)
result = conn.execute(text("SELECT * FROM users WHERE id IN :ids"), {"ids": ids})

更新数据 (UPDATE) 

# 条件更新
update_sql = text("""
    UPDATE users 
    SET email = :email 
    WHERE username = :username
""")
conn.execute(update_sql, {"email": "new@example.com", "username": "john_doe"})
conn.commit()

删除数据 (DELETE)

# 条件删除
delete_sql = text("DELETE FROM users WHERE id = :id")
conn.execute(delete_sql, {"id": 3})
conn.commit()

5. 事务管理

# 手动事务控制
transaction = conn.begin()  # 开启事务
try:
    # 插入操作
    conn.execute(text("""
        INSERT INTO users (username, email)
        VALUES ('test_user', 'test@example.com')
    """))
    
    # 更新操作
    conn.execute(text("""
        UPDATE users 
        SET email = 'admin@example.com' 
        WHERE username = 'admin'
    """))
    
    transaction.commit()  # 提交事务
except Exception as e:
    transaction.rollback()  # 回滚事务
    print(f"操作失败: {e}")

6. 高级操作

存储过程调用

# 创建存储过程（MySQL 语法）
conn.execute(text("""
    CREATE PROCEDURE GetUserByUsername(IN p_username VARCHAR(50))
    BEGIN
        SELECT * FROM users WHERE username = p_username;
    END
"""))

# 调用存储过程
result = conn.execute(text("CALL GetUserByUsername(:username)"), {"username": "john_doe"})
print(result.fetchall())

批量插入性能优化

# 使用 executemany + 事务加速
data = [{"username": f"user_{i}", "email": f"user_{i}@test.com"} for i in range(1000)]

transaction = conn.begin()
try:
    conn.execute(insert_sql, data)
    transaction.commit()
except Exception as e:
    transaction.rollback()
    raise

7. 错误处理

from sqlalchemy.exc import SQLAlchemyError

try:
    conn.execute(text("INSERT INTO users (username) VALUES ('john_doe')"))
    conn.commit()
except SQLAlchemyError as e:
    print(f"数据库错误: {e}")
    conn.rollback()

8. 连接池配置（生产环境建议）

engine = create_engine(
    'mysql+pymysql://root:password@localhost/mydb',
    pool_size=20,          # 连接池保持的连接数
    max_overflow=10,       # 超出 pool_size 时允许临时增加的连接
    pool_pre_ping=True,    # 执行前检查连接是否存活
    pool_recycle=3600,     # 回收旧连接（避免 MySQL 默认 8 小时断开）
    connect_args={
        "connect_timeout": 5  # 连接超时时间（秒）
    }
)

关键注意事项

1、​参数绑定​
始终使用 :param 占位符，禁止直接拼接 SQL 字符串：

# ✅ 安全方式
conn.execute(text("SELECT * FROM users WHERE id = :id"), {"id": 1})

# ❌ 危险方式（SQL 注入漏洞！）
conn.execute(text(f"SELECT * FROM users WHERE id = {user_input}"))

2、​字符集设置​
MySQL 建议使用 utf8mb4 字符集以支持 Emoji 和全部 Unicode 字符：

conn.execute(text("ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4"))

3、事务隔离级别​
可通过 SQL 语句设置事务隔离级别：

conn.execute(text("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"))

示例：创建、插入、查询完整代码：

from sqlalchemy import create_engine, text

# 1. 连接数据库
engine = create_engine('mysql+pymysql://root:password@localhost/mydb')
conn = engine.connect()

# 2. 创建表
conn.execute(text("""
    CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) NOT NULL UNIQUE,
        email VARCHAR(100)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""))
conn.commit()

# 3. 插入数据
conn.execute(
    text("INSERT INTO users (username, email) VALUES (:user, :email)"),
    {"user": "john_doe", "email": "john@example.com"}
)
conn.commit()

# 4. 查询数据
result = conn.execute(text("SELECT * FROM users WHERE username = :user"), {"user": "john_doe"})
print(result.fetchone())  # (1, 'john_doe', 'john@example.com')

# 5. 关闭连接
conn.close()