flask sql查询 sqlalchemy高级查询

flask sql查询

分页加查询总数

 apply_obj = ApplyInfo.query.order_by(ApplyInfo.add_time).paginate(int(page_index), per_page=int(page_size),
                                                                          error_out=False)
          #模型对象+排序（对时间）第几页，每页显示几条
page_total = apply_obj.total #获取总数
        data = [
            {'apply_id': i.apply_id, 'name': i.name, 'start_time': i.start_time.strftime('%Y-%m-%d'),
             'end_time': i.end_time.strftime('%Y-%m-%d')} for i in apply_obj.items]
             #对象取值使用. 时间格式化
oday_page_view = PageInfoRecord.query.with_entities(func.count('*'),
                                                             func.count(PageInfoRecord.user_id.distinct())).filter(
            and_(PageInfoRecord.into_time > today_time, PageInfoRecord.apply_id == apply_id)).first()

            #按时间取值 计算数量 计算去重后user_id的数量 多条件查询
 crop_type = '%{}%'.format(crop_type).replace(' ', '') #对like语句进行封装
 
 query_list = [UserBaseInfo.apply_id == apply_id] #封装sql语句 ==表名.apply_id=apply_id
 query = UserBaseInfo.phone.like("%" + user_msg + "%") #对like语句进行封装

原生SQL中的 DAY HOURS YEARS MOUNH 使用 对时间进行进行分组查询需要数据

page_view_size = db.session.execute(
                'SELECT HOUR ( a.add_time ) AS days,count( a.page_id ) AS count FROM apply_page_record a WHERE a.add_time>= :start_time and a.add_time<=:end_time and a.apply_id=:apply_id AND DATE_FORMAT( add_time, "%y-%M-%d" ) GROUP BY days',
                {'start_time': start_time, 'end_time': end_time, 'apply_id': apply_id})
                
                # HOUR 代表小时 查询 page_id出现的次数   DATE_FORMAT() 对时间进行格式化

page_view_size = db.session.execute( # 按天进行查找
                'SELECT DAY ( a.add_time ) AS days,count( a.page_id ) AS count FROM apply_page_record a WHERE a.add_time>= :start_time and a.add_time<=:end_time and a.apply_id=:apply_id AND DATE_FORMAT( add_time, "%y-%M-%d" ) GROUP BY days',
                {'start_time': start_time, 'end_time': end_time, 'apply_id': apply_id})
                
                
 page_view_size = db.session.execute(# 按周去显示时间
                'SELECT WEEK ( a.add_time ) AS days,count( a.page_id ) AS count FROM apply_page_record a WHERE a.add_time>= :start_time and a.add_time<=:end_time and a.apply_id=:apply_id AND DATE_FORMAT( add_time, "%y-%M-%d" ) GROUP BY days',
                {'start_time': start_time, 'end_time': end_time, 'apply_id': apply_id})
                

批量插入数据

            apply_info = ApplyInfo(**args) 
            db.session.add(apply_info) 
            db.session.flush() #获取到新插入数据的id 并未生成到数据库
            apply_id = apply_info.apply_id 
            banner_img = ApplyBanner(
                info_award=info_banner_url, apply_award=apply_banner_url, apply_id=apply_id)
            db.session.add(banner_img)
            award_list = pack_award_message(apply_award, info_award, apply_id) # 使用pack_award_message生成sql对象
            db.session.bulk_insert_mappings(ApplyAwardInfo, award_list) #bulk_insert_mappings进行批量插入 第一个对象为 插入的实例模型，第二个必须为列表
            db.session.commit()