sqlalchemy oracle 批量添加记

sqlalchemy oracle 批量添加记录

• 前提：要安装 cx_oracle

自己封装了一些常用的操作

import math
from sqlalchemy import orm

class Connect(object):
    instance = None
    @classmethod
    def get_instance(cls, engine):
        if cls.instance:
            return cls.instance
        else:
            obj = cls(engine)
            cls.instance = obj
            return obj

    def __init__(self, engine):
        self.engine = engine
        self.session = None
        self.connect_session()

    def connect_session(self):
        try:
            self.engine.connect()
        except Exception as e:
            print(e)
            raise Exception("数据库连接失败")

        dbSession = orm.sessionmaker(bind=self.engine)
        self.session = dbSession()

    def add_all(self, records):
        '''
        指添加记录
        :param records:
        :return:
        '''

        if len(records) == 0:
            return

        post_count = 10000
        record_count = len(records)
        post = math.ceil(record_count / post_count)

        for i in range(post):
            begin_index = i * post_count
            if i + 2 <= post:
                end_index = (i + 1) * post_count
            else:
                end_index = record_count
           # TODO 暂时先这么处理 解决不能重复提交的问题
            self.session.identity_map._dict = {}
            self.session.add_all(records[begin_index:end_index])
            self.save()

    def add(self, record):
        self.session.add(record)

    def save(self):
        try:
            self.session.commit()
        except Exception as e:
            print(e)
            self.session.rollback()

    # 执行sql语句
    def execute_ext(self, sql):
        return self.session.execute(sql)

    # 返加是数据 []
    def execute(self, sql):
        try:
            return self.session.execute(sql).fetchall()
        except Exception as e:
            logger.error(e)
            self.session.rollback()

    # 返回一条数据元组()
    def oneexecute(self, sql):
        try:
            return self.session.execute(sql).first()
        except Exception as e:
            logger.error(e)
            self.session.rollback()

    def query(self, query):
        return self.session.query(query)

    def close(self):
        self.session.close()

第一步： 声明一个engine

engine_db = create_engine("oracle+cx_oracle://admin:admin@127.0.0.1/orcl", coerce_to_unicode=True, max_overflow=100, pool_size=100, pool_recycle=3600)

第二步： 获取一个connection

def session_db():
    return Connect(engine_db)

第三步： 导入SQLAlchemy

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

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))

第四步：向数据中批量添加数据

# 创建session对象:
session = session_db()
# 创建新User对象,插入数组:
list_user = []
for i in range(10000):
    new_user = User(id='5', name='Bob')
    list_user.append(new_user)
# 添加到session:
session.add_all(list_user)
# 提交即保存到数据库:
session.save()
# 关闭session:
session.close()