Python 数据库操作详细

一、Python 数据库操作基础

1. Python DB-API 规范

• Python 的数据库操作遵循 PEP 249（DB-API 2.0）规范，主要接口为：
  • connect()：连接数据库
  • cursor()：创建游标对象
  • execute()/executemany()：执行SQL语句
  • fetchone()/fetchall()：获取查询结果
  • commit()/rollback()：事务提交/回滚
  • close()：关闭连接和游标

---

二、SQLite 操作（内置，无需安装）

1. 基本流程

import sqlite3

# 连接数据库（自动创建文件）
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 创建表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
''')

# 插入数据
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 23))
cursor.executemany('INSERT INTO users (name, age) VALUES (?, ?)', [
    ('Bob', 25),
    ('Charlie', 30)
])

# 查询数据
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

# 更新数据
cursor.execute('UPDATE users SET age=? WHERE name=?', (24, 'Alice'))

# 删除数据
cursor.execute('DELETE FROM users WHERE name=?', ('Bob',))

# 事务提交
conn.commit()

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

---

三、MySQL 操作（需安装 mysql-connector-python 或 pymysql）

1. 安装库

pip install mysql-connector-python
# 或
pip install pymysql

2. 示例（以 mysql-connector-python 为例）

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='yourpassword',
    database='testdb'
)
cursor = conn.cursor()

# 创建表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50),
        age INT
    )
''')

# 插入数据
cursor.execute('INSERT INTO users (name, age) VALUES (%s, %s)', ('David', 28))
cursor.executemany('INSERT INTO users (name, age) VALUES (%s, %s)', [
    ('Eve', 35),
    ('Frank', 40)
])

# 查询数据
cursor.execute('SELECT * FROM users')
for row in cursor.fetchall():
    print(row)

# 更新数据
cursor.execute('UPDATE users SET age=%s WHERE name=%s', (29, 'David'))

# 删除数据
cursor.execute('DELETE FROM users WHERE name=%s', ('Eve',))

# 事务提交
conn.commit()

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

---

四、PostgreSQL 操作（需安装 psycopg2）

1. 安装库

pip install psycopg2

2. 示例

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    user='postgres',
    password='yourpassword',
    database='testdb'
)
cursor = conn.cursor()

# 创建表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(50),
        age INT
    )
''')

# 插入数据
cursor.execute('INSERT INTO users (name, age) VALUES (%s, %s)', ('Grace', 32))
cursor.executemany('INSERT INTO users (name, age) VALUES (%s, %s)', [
    ('Heidi', 27),
    ('Ivan', 38)
])

# 查询数据
cursor.execute('SELECT * FROM users')
for row in cursor.fetchall():
    print(row)

# 更新数据
cursor.execute('UPDATE users SET age=%s WHERE name=%s', (33, 'Grace'))

# 删除数据
cursor.execute('DELETE FROM users WHERE name=%s', ('Heidi',))

conn.commit()
cursor.close()
conn.close()

---

五、异常处理与事务

1. 异常处理

try:
    conn = sqlite3.connect('test.db')
    cursor = conn.cursor()
    # 操作
except Exception as e:
    print('数据库操作异常:', e)
finally:
    cursor.close()
    conn.close()

2. 事务回滚

try:
    # ...操作
    conn.commit()
except Exception as e:
    conn.rollback()
    print('事务回滚:', e)

---

六、批量操作与防止SQL注入

• 推荐使用参数化查询（如?、%s），避免拼接字符串，防止SQL注入。
• 使用 executemany() 批量插入/更新。

---

七、常用封装（with语法）

import sqlite3

with sqlite3.connect('test.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    print(cursor.fetchall())
    cursor.close()
# with块自动提交/关闭

---

八、ORM推荐

如需更高级功能，推荐使用 ORM 框架如 SQLAlchemy：

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)

engine = create_engine('sqlite:///test.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# 增
session.add(User(name='Jack', age=22))
session.commit()

# 查
for user in session.query(User).all():
    print(user.name, user.age)

session.close()

九、数据库连接池

1. 为什么需要连接池

• 数据库连接的建立和释放开销大，频繁操作会影响性能。
• 连接池可复用连接、限制最大连接数、提高并发效率。

2. 主流连接池库

• MySQL：DBUtils、SQLAlchemy、mysql.connector.pooling
• PostgreSQL：psycopg2.pool、SQLAlchemy
• 通用：SQLAlchemy自带连接池

3. 示例：DBUtils + pymysql（MySQL）

from dbutils.pooled_db import PooledDB
import pymysql

pool = PooledDB(
    creator=pymysql,
    maxconnections=5,
    host='localhost',
    user='root',
    password='yourpassword',
    database='testdb',
    charset='utf8mb4'
)

conn = pool.connection()
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
print(cursor.fetchall())
cursor.close()
conn.close()

4. SQLAlchemy 连接池（通用）

from sqlalchemy import create_engine

engine = create_engine(
    'mysql+pymysql://root:yourpassword@localhost/testdb',
    pool_size=5,
    max_overflow=10
)

with engine.connect() as conn:
    result = conn.execute('SELECT * FROM users')
    print(result.fetchall())

---

十、异步数据库操作

1. 场景

• 高并发、IO密集型应用（如Web服务、爬虫、异步任务等）

2. 主流异步库

• aiomysql（MySQL）
• aiopg（PostgreSQL）
• asyncpg（PostgreSQL，高性能）
• SQLAlchemy 2.0+（原生支持异步）

3. 示例：asyncpg（PostgreSQL）

import asyncio
import asyncpg

async def main():
    conn = await asyncpg.connect(user='postgres', password='yourpassword',
                                 database='testdb', host='127.0.0.1')
    await conn.execute('''
        CREATE TABLE IF NOT EXISTS users(
            id SERIAL PRIMARY KEY,
            name VARCHAR(50),
            age INT)
    ''')
    await conn.execute('INSERT INTO users(name, age) VALUES($1, $2)', 'Tom', 20)
    rows = await conn.fetch('SELECT * FROM users')
    for row in rows:
        print(dict(row))
    await conn.close()

asyncio.run(main())

4. SQLAlchemy 2.0+ 异步用法

import asyncio
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

engine = create_async_engine('postgresql+asyncpg://postgres:yourpassword@localhost/testdb')
AsyncSessionLocal = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)

async def async_query():
    async with AsyncSessionLocal() as session:
        result = await session.execute('SELECT 1')
        print(result.fetchall())

asyncio.run(async_query())

---

十一、复杂查询

1. 多表联查（JOIN）

cursor.execute('''
    SELECT u.id, u.name, o.order_id, o.amount
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE o.amount > %s
''', (100,))
for row in cursor.fetchall():
    print(row)

2. 聚合、分组

cursor.execute('SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1')
print(cursor.fetchall())

3. 子查询

cursor.execute('''
    SELECT name FROM users WHERE id IN (
        SELECT user_id FROM orders WHERE amount > %s
    )
''', (200,))
print(cursor.fetchall())

4. 事务与批量操作

try:
    cursor.execute('UPDATE users SET age=age+1 WHERE age<30')
    cursor.executemany('INSERT INTO users (name, age) VALUES (%s, %s)', [
        ('A', 20), ('B', 21)
    ])
    conn.commit()
except Exception as e:
    conn.rollback()

---

十二、ORM进阶用法（SQLAlchemy）

1. 复杂查询：联表、聚合、条件

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, func
from sqlalchemy.orm import declarative_base, relationship, sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)
    orders = relationship('Order', back_populates='user')

class Order(Base):
    __tablename__ = 'orders'
    order_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    amount = Column(Integer)
    user = relationship('User', back_populates='orders')

engine = create_engine('sqlite:///test.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# 联表查询
results = session.query(User.name, Order.amount).join(Order).filter(Order.amount > 100).all()
print(results)

# 聚合
agg_result = session.query(func.avg(User.age)).scalar()
print('平均年龄:', agg_result)

# 条件查询
users = session.query(User).filter(User.age.between(20, 30)).all()
for u in users:
    print(u.name, u.age)

session.close()

2. 事务与回滚

session = Session()
try:
    user = User(name='Zoe', age=25)
    session.add(user)
    session.commit()
except Exception as e:
    session.rollback()
    print('事务回滚:', e)
finally:
    session.close()

创作不易，点赞关注，持续更新！！！