Sqlalchemy批量操作

增

• add_all

    url = "http://101.132.238.119:10001/users/products_v"
    resp = requests.get(url)
    response = json.loads(resp.text)
    objects = []
    for appid, value in response.items():
        visits = value["visits"]
        one_object = OfflineDataCenter(date=date, app_id=appid, visits=visits, channel_id="27")
        objects.append(one_object)
    db.session.add_all(objects)
    db.session.commit()

• bulk_save_objects

    url = "http://101.132.238.119:10001/users/products_v"
    resp = requests.get(url)
    response = json.loads(resp.text)
    objects = []
    for appid, value in response.items():
        visits = value["visits"]
        one_object = OfflineDataCenter(date=date, app_id=appid, visits=visits, channel_id="27")
        objects.append(one_object)
	    db.session.bulk_save_objects(objects)
    db.session.commit()

• bulk_insert_mappings

db.session.bulk_insert_mappings(
        Card, # 当前映射类
        [*max_obj] # 列表里套的是字典打散
    )
    db.session.commit()

删

• Sqlalchemy 使用 in or notin 无法批量删除数据

  1. 解决方法

  可以在delete()括号内添加 synchronize_session=False 或者 synchronize_session=‘fetch’ 实现删除

  delete(synchronize_session=False)
  

  2. delete的基本用法

  delete(delete(synchronize_session='evaluate))
  synchronize_session=False : 该参数不会同步删除数据，而是在session结束前删除数据
  synchronize_session=‘evaluate’ : 该参数会先评估查询删除的可执行性，如果无法执行就会报错，不支持in or notin
  synchronize_session=‘fetch’ : 该参数会将会先查找一次对象，然后在将对象移除session并删除

改

• update

        Order.query.filter(
            Order.completed_at <= deleted_at,
            Order.state == 'completed',
            Order.is_deleted.is_(False)
        ).update(
            {
                'is_deleted': True,
                'deleted_at': db.func.now(),
            },
            synchronize_session=False
        )
        db.session.commit()

• bulk_update_mappings

db.session.bulk_update_mappings(
        Card, # 当前映射类
        [*max_obj] # 列表里套的是字典打散
    )
    db.session.commit()