SQLAlchemy批量新增数据&返回新增数据ID&一失败全失败

# 看到有人点赞，想起这篇文章的后续，后来发现是我用的mac系统下的问题，SQLAlchemy在mac系统下会有这个问题，而在Windows和Linux系统下，add_all()即可以实现部分出错回滚，这个问题还没来得及到SQLAlchemy项目下反馈下。看到这篇文章的人先排除下这个问题，是的话就没必要往下看了。

业务需求是批量新增数据并且返回数据ID（成功写入数据库后新产生的自增ID）并且要求批量新增的操作为原子级别，其中一个数据新增失败，则全部失败（而不是部分成功，出错后的失败）。

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()


class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True, autoincrement=True, comment='自增ID')
name = Column(String, comment='用户昵称')

print("""----------目录结构----------""")

from contextlib import contextmanager

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DB = {
    "db_user":"mysql+mysqldb://root:db_password:3306/db_user?charset=utf8mb4",
}

engine = create_engine(DB["db_user"])
user_session = sessionmaker(bind=engine, autocommit=False, autoflush=False)


@contextmanager
def session_maker(session: sessionmaker):
    session = session()
    try:
        yield session
        session.commit()
    finally:
        session.close()

print("""----------目录结构----------""")

user_dict_list = [
{'name': 'u1'},
{'name': 'u2'},
{'name': 'u3'},
]

user_objects = [
    User(name='u1'),
    User(name='u2'),
    User(name='u3')
]


def insert_user_data(user_dict_list: List[dict]):
    with session_maker(user_session) as session:
        try:
            result = session.execute(User.__table__.insert(), user_dict_list)
            session.commit()
            last_insert_id = result.lastrowid
            end_insert_id = last_insert_id + len(user_dict_list)
            new_id_list = list(range(last_insert_id, end_insert_id))
            for new_id, new_user in zip(new_id_list, user_dict_list):
                new_user.update({'id': new_id})
        except Exception as e:
            logging.error(e)
        else:
            return user_dict_list
 

SQLAlchemy ORM：

提供的session.add()方法不能实现一失败全失败要求，遍历进行，会造成从失败那条数据之前的成功写入数据库，其及之后的数据失败，无法回滚。

session.add_all(user_objects)看源码只是遍历进行session.add()而已，无法自动获得新增数据的ID，不能实现一失败全失败。

session.bulk_insert_mappings(User, user_dict_list)、session.bulk_save_objects(user_objects)可以实现一失败全失败，但是没有返回值，无法自动获得新增数据的ID。

SQLAlchemy Core：

result = engine.execute(User.__table__.insert(), user_dict_list)最终执行的为一个原子级的单个SQL语句，可以实现一失败全失败。result.lastrowid返回值为该线程下最后新增的ID，批量则为批量的第一个新增ID。

这样最终实现了该需求，但在高并发情形下是否有bug未知，result.lastrowid是线程相关的，且是原子级单SQL语句，理论上应该是没问题的。

user_dict_list中数据如果有问题的话，可以先做可预见的验证再入数。

同样需求批量更新再开一篇。

参考资料：

使用SQLAlchemy ORM批量插入

python - 使用SQLAlchemy ORM批量插入

sqlalchemy 批量插入与坑

Performance — SQLAlchemy 1.4 Documentation

SqlAlchemy: getting the id of the last record inserted

sqlalchemy插入操作后自动返回自增ID