ReplaceAccountInfo.py 1.6 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/10/26 10:15 上午
  4. # @Site :
  5. # @File : ReplaceAccountInfo.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from Apietl.conn.TidbConn import TidbConn
  11. class ReplaceAccountInfo:
  12. @staticmethod
  13. def handler():
  14. try:
  15. sql = """
  16. replace into `jeecg-boot`.ctop_account_attribution_info(id, account_id, project_id,product_id, advertiser_id, company_id,account_name, project_name, advertiser_name, company_name, sale_id, user_id,org_code)
  17. select cua.account_id,
  18. cua.account_id,
  19. cp.id,
  20. product.id,
  21. ca.id,
  22. uc.company_id,
  23. cua.auth_name,
  24. cp.project_name,
  25. ca.name,
  26. uc.company_name,
  27. cp.sale_id,
  28. su.id,
  29. su.org_code
  30. from `jeecg-boot`.ctop_user_allocation cua
  31. left join `jeecg-boot`.ctop_project cp on cua.project_id = cp.id
  32. left join `jeecg-boot`.ctop_advertiser ca on ca.id = cua.advertiser_id
  33. left join `jeecg-boot`.sys_user su on su.id = cua.user_id
  34. left join `jeecg-boot`.user_company uc on uc.user_id = cua.user_id
  35. left join `jeecg-boot`.ctop_product product on product.id = cp.product_id
  36. where cua.account_id is not null
  37. """
  38. TidbConn.upsert(conn=TidbConn.get_connection(), sql_buff=sql)
  39. TidbConn.get_connection().commit()
  40. TidbConn.conn_close(conn=TidbConn.get_connection())
  41. except Exception as e:
  42. logging.error(e)
  43. if __name__ == '__main__':
  44. ReplaceAccountInfo.handler()