def insert_rows(model_name, data_list):
"""
批量插入数据（遇到主键/唯一索引重复，忽略报错，继续执行下一条插入任务）
注意：
Warning: Duplicate entry
警告有可能会提示：
UnicodeEncodeError: 'ascii' codec can't encode characters in position 17-20: ordinal not in range(128)
处理：
import sys
reload(sys)
sys.setdefaultencoding('utf8')
sql 语句大小限制
show VARIABLES like '%max_allowed_packet%';
参考：http://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html
:param model_name:
:param data_list:
:return:
"""
try:
# result = db_session.execute(model_name.__table__.insert().prefix_with('IGNORE'), data_list)
result = None
if isinstance(model_name,Table):
result = db_session.execute(model_name.insert(),data_list)
else:
result = db_session.execute(model_name.__table__.insert(), data_list)
db_session.commit()
return result.rowcount
except Exception as e:
db_session.rollback()
raise e
def merge(model_name):
try:
db_session.merge(model_name)
db_session.commit()
except Exception as e:
db_session.rollback()
raise e
def update_rows_by_ids(model_name, pk_ids, data):
"""
根据一组主键id 批量修改数据
"""
model_pk = inspect(model_name).primary_key[0]
try:
model_obj = db_session.query(model_name).filter(model_pk.in_(pk_ids))
result = model_obj.update(data, synchronize_session=False)
db_session.commit()
return result
except Exception as e:
db_session.rollback()
raise e
def delete(model_name, pk_id):
"""
删除信息
:param model_name: 删除的模型
:type pk_id: 删除的主键
"""
model_pk = inspect(model_name).primary_key[0]
try:
model_obj = db_session.query(model_name).filter(model_pk == pk_id)
result = model_obj.delete()
db_session.commit()
except Exception as e:
db_session.rollback()
raise e
def add(model_name, data):
"""
添加信息
:type data: object
:type model_name: object
:return None/Value of model_obj.PK
"""
model_obj = model_name(**data)
try:
db_session.add(model_obj)
db_session.commit()
return inspect(model_obj).identity[0]
except Exception as e:
db_session.rollback()
raise e
def edit(model_name, pk_id, data):
"""
编辑信息
:param model_name: 编辑的模型
:param pk_id: 编辑数据的主键
:type data: 编辑的数据
"""
model_pk = inspect(model_name).primary_key[0]
try:
model_obj = db_session.query(model_name).filter(model_pk == pk_id)
result = model_obj.update(data)
db_session.commit()
return result
except Exception as e:
db_session.rollback()
raise e
def update_rows(model_name, data, *args, **kwargs):
"""
批量修改数据
:param model_name:
:param data:
:param args:
:param kwargs:
:return:
"""
try:
model_obj = db_session.query(model_name).filter(*args).filter_by(**kwargs)
result = model_obj.update(data, synchronize_session=False)
db_session.commit()
return result
except Exception as e:
db_session.rollback()
raise e