request_json.py 3.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108
  1. import pandas as pd
  2. import numpy as np
  3. import datetime
  4. import pymysql
  5. from sqlalchemy import create_engine
  6. # online_con_str = "mysql+pymysql://%s:%s@%s:%d/%s" % ("readonly", "hcst@2021", "139.186.27.96", 4000, "jeecg-boot")
  7. # engine = create_engine(online_con_str, connect_args={'charset': 'utf8'})
  8. # test_con_str = "mysql+pymysql://%s:%s@%s:%d/%s" % ("hcst", "hcst2020", "139.186.151.174", 3306, "jeecg-boot")
  9. # engine = create_engine(test_con_str, connect_args={'charset': 'utf8'})
  10. online_con_str = "mysql+pymysql://%s:%s@%s:%d/%s" % ("hcst", "hcst@2020", "139.186.151.174", 3306, "jeecg-boot")
  11. engine = create_engine(online_con_str, connect_args={'charset': 'utf8'})
  12. # 拼接请求的json
  13. # example: 整个项目近30天内,消耗top10的视频,创建组
  14. account_id = 9556344
  15. request_dict = {}
  16. # 1、读取 客户策略表中 status = 1 的记录 (ctop_ai_kuaishou_advertiser_strategy)
  17. get_advertiser_strategy_sql = """
  18. select *
  19. from ctop_ai_kuaishou_advertiser_strategy
  20. where account_id = %d
  21. and status = 1
  22. """ % account_id
  23. advertiser_strategy_df = pd.read_sql(get_advertiser_strategy_sql, engine)
  24. advertiser_strategy_drop_cols = ['id', 'status', 'create_time', 'effective_time', 'expiry_time']
  25. advertiser_strategy_df.drop(columns=advertiser_strategy_drop_cols, inplace=True)
  26. request_dict['advertiser_strategy_info'] = advertiser_strategy_df.T.to_dict()[0]
  27. # 2、拼接请求的json
  28. # 2.1 获取需要创建广告组所需要的视频
  29. # 调用于蒙的视频接口
  30. # 获取视频方式一
  31. days = 30
  32. num = 10
  33. sql = """
  34. select * from ctop_kuaishou_video_get
  35. where account_id = %d
  36. and signature in
  37. (
  38. select
  39. daily_material.signature
  40. from
  41. ctop_kuaishou_report_daily_material daily_material
  42. inner join
  43. ctop_user_allocation allocation
  44. on daily_material.account_id = allocation.account_id
  45. where allocation.project_id in (select project_id from ctop_user_allocation where account_id = %d)
  46. and to_days(now()) - to_days(daily_material.stat_date) <= %d
  47. and daily_material.signature is not null
  48. GROUP BY daily_material.signature
  49. order by sum(daily_material.charge) desc
  50. limit %d
  51. )
  52. """ % (account_id, account_id, days, num)
  53. # video_df = pd.read_sql(sql, engine)
  54. # print(video_df.shape)
  55. # 获取视频方式二
  56. # 离线分析手动提取 signature,然后创建广告组
  57. # signature_list = ['f17059ef15d5940a43bd38f9b13dc551', 'c0065cbd882d94c7c0a808db68f73666']
  58. # sql = """
  59. # select * from ctop_kuaishou_video_get
  60. # where account_id = %d
  61. # and signature in %s
  62. # """ % (account_id, tuple(signature_list))
  63. # video_df = pd.read_sql(sql, engine)
  64. # print(video_df)
  65. # 2.1 计划层级的参数
  66. campaign_name = "户14-上下滑&信息流-跑量视频-剪辑-低价-低价包邮-活动-年龄18岁以上-单出价-" + \
  67. datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")
  68. # campaign_id 为空表示需要创建广告计划, 不为空则表示在该广告计划下新增广告组
  69. campaign_dict = {}
  70. campaign_id = None
  71. campaign_dict['campaign_name'] = campaign_name
  72. campaign_dict['campaign_id'] = campaign_id
  73. campaign_dict['campaign_type'] = 2
  74. campaign_dict['day_budget'] = None
  75. campaign_dict['day_budget_schedule'] = None
  76. campaign_dict['operation_type'] = "add"
  77. request_dict['campaign'] = campaign_dict
  78. # 2.2 组层级的参数