python操作之SQLAchemy

SQLAlchemy是Python的ORM框架,它基于数据库API,通过对象关系映射进行数据库操作。安装SQLAlchemy后,需要依赖如pymsql的第三方库来实际操作数据库。操作数据库可以分为两种方式:1. 使用Engine、ConnectionPooling和Dialect进行内部处理;2. 利用ORM、Schema Type等组件进行ORM功能。本文将介绍如何创建表、增删改查等基本操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

QLAlchemy是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果。

安装:

pip3 install SQLALchemy

SQLAlchemy本身无法操作数据库,其必须以来pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,

从而实现对数据库的操作,如:


MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
   
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
   
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
   
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
   
更多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html

一、内部处理

使用 Engine/ConnectionPooling/Dialect 进行数据库操作,Engine使用ConnectionPooling连接数据库,然后再通过Dialect执行SQL语句。

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
  
  
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
  
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"
# )
  
# 新插入行自增ID
# cur.lastrowid
  
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
# )
  
  
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
#     host='1.1.1.99', color_id=3
# )
  
# 执行SQL
# cur = engine.execute('select * from hosts')
# 获取第一行数据
# cur.fetchone()
# 获取第n行数据
# cur.fetchmany(3)
# 获取所有数据
# cur.fetchall()

二、ORM功能使用

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象,对象转换成SQL,执行SQL。

1、创建表

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
 
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
 
Base = declarative_base()
 
# 创建单表
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))
 
    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_id_name', 'name', 'extra'),
    )
 
 
# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)
 
 
class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
 
 
# 多对多
class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)
 
 
class Server(Base):
    __tablename__ = 'server'
 
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
 
 
class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))
 
 
def init_db():
    Base.metadata.create_all(engine)
 
 
def drop_db():
    Base.metadata.drop_all(engine)

注:设置外检的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

Base = declarative_base()

# 创建单表
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_id_name', 'name', 'extra'),
    )

    def __repr__(self):
        return "%s-%s" %(self.id, self.name)

# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)

    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 与生成表结构无关,仅用于查询方便
    favor = relationship("Favor", backref='pers')

# 多对多
class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))
    group = relationship("Group", backref='s2g')
    server = relationship("Server", backref='s2g')

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)
    # group = relationship('Group',secondary=ServerToGroup,backref='host_list')


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)




def init_db():
    Base.metadata.create_all(engine)


def drop_db():
    Base.metadata.drop_all(engine)


Session = sessionmaker(bind=engine)
session = Session()

表结构 + 数据库连接

表结构+数据库

obj = Users(name="alex0", extra='sb')
session.add(obj)
session.add_all([
    Users(name="alex1", extra='sb'),
    Users(name="alex2", extra='sb'),
])
session.commit()

1 session.query(Users).filter(Users.id > 2).delete()
2 session.commit()

session.query(Users).filter(Users.id > 2).update({"name" : "099"})
session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)
session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")
session.commit()

ret = session.query(Users).all()
ret = session.query(Users.name, Users.extra).all()
ret = session.query(Users).filter_by(name='alex').all()
ret = session.query(Users).filter_by(name='alex').first()

ret = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(User.id).all()

ret = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()

补充:

# 条件
ret = session.query(Users).filter_by(name='alex').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()

# 限制
ret = session.query(Users)[1:2]

# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all()


# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()











session.query(Users).filter(Users.id > 2).update({"name" : "099"})
session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)
session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")
session.commit()


<think>嗯,用户问的是Python SQLAlchemy的异步批量插入,我得先理清楚异步和批量插入这两个关键点。首先,用户可能已经了解过SQLAlchemy的基本用法,现在想优化性能,特别是在高并发或处理大量数据时,所以需要异步操作来避免阻塞,同时批量插入减少数据库交互次数。 接下来,我需要回忆SQLAlchemy的异步模块,也就是asyncio的部分。记得从1.4版本开始,SQLAlchemy开始支持异步,通过AsyncSession来实现。所以应该先提到安装必要的库,比如sqlalchemy和async驱动,比如asyncmy或asyncpg,这里可能需要用户根据数据库类型选择。 然后,如何建立异步引擎。这里要给出示例代码,使用create_async_engine,并注意连接字符串的差异,比如mysql需要加上+asyncmy。这部分容易出错,用户可能会忽略驱动导致连接失败,所以需要强调。 接下来是创建AsyncSession。需要说明session的用法,和同步的区别,比如要用async with来管理上下文,执行操作时要用await。 然后重点在于批量插入的方法。用户可能知道同步中的add_all,但异步环境下是否同样适用?或者有没有更高效的方式。需要说明在异步中可以使用add_all,但如果是非常大的数据集,可能需要分块处理,避免内存问题。同时,executemany方法可能更高效,但需要结合Core的Table对象,使用insert().values()的方式,然后通过session.execute来执行。这时候需要用await。 可能用户还会关心性能优化,比如是否使用事务,或者批处理的大小设置。此外,异常处理也很重要,比如回滚操作,确保在出错时能够正确处理。这里需要给出try-exatch块的示例,并展示await session.rollback()。 另外,是否需要关闭session?在异步环境中,async with会自动处理,但最好还是提醒用户正确管理资源。 最后,要总结一下步骤:安装驱动、创建引擎和会话、选择批量插入方法(add_all或executemany)、处理异常、性能优化建议。还要提醒用户根据数据库类型选择正确的异步驱动,以及分块处理大数据集的重要性。 可能用户在实际操作中会遇到的问题包括驱动安装错误、没有正确使用await、事务管理不当,或者批量数据过大导致内存溢出。需要预见到这些问题,并在回答中给出相应的注意事项。</think>我将分步为您介绍如何在Python中使用SQLAlchemy异步(AsyncIO)实现批量插入: 1. 环境准备 ```python # 安装必要库 pip install sqlalchemy[asyncio] asyncmy # MySQL示例 # 或 pip install sqlalchemy[asyncio] asyncpg # PostgreSQL示例 ``` 2. 创建异步引擎 ```python from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession # 注意连接字符串格式差异 engine = create_async_engine( "mysql+asyncmy://user:pass@host/db", echo=True # 开发时查看SQL日志 ) ``` 3. 建立会话工厂 ```python from sqlalchemy.orm import sessionmaker AsyncSessionFactory = sessionmaker( engine, class_=AsyncSession, expire_on_commit=False ) ``` 4. 批量插入方法对比 方法一:使用add_all(适合中小批量) ```python async with AsyncSessionFactory() as session: try: # 构建对象列表 users = [User(name=f"user_{i}") for i in range(1000)] session.add_all(users) await session.commit() except: await session.rollback() raise ``` 方法二:使用executemany(适合大批量) ```python from sqlalchemy import insert async with AsyncSessionFactory() as session: stmt = insert(User).values([{"name": f"user_{i}"} for i in range(10000)]) await session.execute( stmt, execution_options={"populate_existing": True} ) await session.commit() ``` 5. 性能优化建议 - 批量大小控制在500-2000条/批 - 使用`executemany`时设置合适的`execution_options` - 对于PostgreSQL可开启`execute_stream=True` - 禁用自动刷新:`autoflush=False` 6. 注意事项 - 异步操作必须全程使用`await` - 异常处理需要显式回滚 - 不同数据库的异步驱动不同 - 建议使用SQLAlchemy 2.0+版本 两种方法的选择依据: - `add_all`更适合ORM对象操作 - `executemany`在纯数据插入时效率更高 - 10万+数据量建议分批次提交 实际使用时建议封装为事务处理: ```python async def batch_insert(data): async with AsyncSessionFactory() as session: async with session.begin(): # 自动事务管理 await session.execute( insert(User), [{"name": item} for item in data] ) ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值