1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2021/10/26 10:15 上午
- # @Site :
- # @File : ReplaceAccountInfo.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- # @Tel 1501435553
- import logging
- from Apietl.conn.TidbConn import TidbConn
- class ReplaceAccountInfo:
- @staticmethod
- def handler():
- try:
- sql = """
- 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)
- select cua.account_id,
- cua.account_id,
- cp.id,
- product.id,
- ca.id,
- uc.company_id,
- cua.auth_name,
- cp.project_name,
- ca.name,
- uc.company_name,
- cp.sale_id,
- su.id,
- su.org_code
- from `jeecg-boot`.ctop_user_allocation cua
- left join `jeecg-boot`.ctop_project cp on cua.project_id = cp.id
- left join `jeecg-boot`.ctop_advertiser ca on ca.id = cua.advertiser_id
- left join `jeecg-boot`.sys_user su on su.id = cua.user_id
- left join `jeecg-boot`.user_company uc on uc.user_id = cua.user_id
- left join `jeecg-boot`.ctop_product product on product.id = cp.product_id
- where cua.account_id is not null
- """
- TidbConn.upsert(conn=TidbConn.get_connection(), sql_buff=sql)
- TidbConn.get_connection().commit()
- TidbConn.conn_close(conn=TidbConn.get_connection())
- except Exception as e:
- logging.error(e)
- if __name__ == '__main__':
- ReplaceAccountInfo.handler()
|