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