sqlalchemy 批量 insert ignore MySQL postgresql 批量插入 批量去重插入 oracle 插入

2024年5月22日 星期三 更新
oracle 插入不去重

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

# 创建基础映射类
Base = declarative_base()

# 定义 ORM 模型
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)

# 初始化数据库连接
engine = create_engine('oracle+cx_oracle://username:password@localhost:1521/dbname')

# 创建所有表（如果表不存在）
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 要插入的数据
data_to_insert = [
    {'id': 1, 'name': 'John', 'age': 25},
    {'id': 2, 'name': 'Jane', 'age': 28},
    {'id': 3, 'name': 'Tom', 'age': 22}
]

# 批量插入数据
session.bulk_insert_mappings(User, data_to_insert)

# 提交事务
session.commit()

去重插入

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

# 创建基础映射类
Base = declarative_base()

# 定义 ORM 模型
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)

# 初始化数据库连接
engine = create_engine('oracle+cx_oracle://username:password@localhost:1521/dbname')

# 创建所有表（如果表不存在）
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 要插入的数据
data_to_insert = [
    {'id': 1, 'name': 'John', 'age': 25},
    {'id': 2, 'name': 'Jane', 'age': 28},
    {'id': 3, 'name': 'Tom', 'age': 22}
]

def is_record_exists(session, model, record):
    return session.query(exists().where(
        model.id == record['id']
    )).scalar()

# 创建去重后的数据列表
unique_data_to_insert = [
    User(**record) for record in data_to_insert
    if not is_record_exists(session, User, record)
]

# 批量插入数据
if unique_data_to_insert:
    session.bulk_save_objects(unique_data_to_insert)
    session.commit()

sqlalchemy 批量 insert ignore MySQL postgresql

特别注意，格式为列表包含字典

[{ ‘id’: 1, ‘name’: ‘Ringo’ }, { ‘id’: 2, ‘name’: ‘Paul’ }, …]

The only thing is that my_entries needs to be a list of column to value mappings. That means [{ ‘id’: 1, ‘name’: ‘Ringo’ }, { ‘id’: 2, ‘name’: ‘Paul’ }, …] et cetera.

MySQL语法

from app import db

class People(db.Model):
    __tablename__ = "people"
    username = db.Column(db.String(100), primary_key=True)
    password = db.Column(db.String(100))


# db.create_all()

p1 = {
    'username': 'zhao'
}

my_entries = [p1]

inser_ignore =  People.__table__.insert().prefix_with('IGNORE').values(my_entries)
db.session.execute(inser_ignore)
db.session.commit()

postgresql

from sqlalchemy.dialects.postgresql import insert
session.execute(insert(MyTable)
                .values(my_entries)
                .on_conflict_do_nothing())

参考
https://stackoverflow.com/questions/63556777/sqlalchemy-add-all-ignore-duplicate-key-integrityerror