sqlalchemy 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
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()
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()
class User(Base):
__tablename__ = 'user'
id = Column(String(20), primary_key=True)
name = Column(String(20))
第四步:向数据中批量添加数据
session = session_db()
list_user = []
for i in range(10000):
new_user = User(id='5', name='Bob')
list_user.append(new_user)
session.add_all(list_user)
session.save()
session.close()