db_func.py
2.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
# @Time :2019/11/26 11:25
# @Author :dengyuting
# @File :db_func.py
import psycopg2
db_config ={'database': "mmmworthtest",
'user': "bianxinhua",
'password': "bianxinhua_123",
'host': "114.55.200.59",
'port': "31543"}
def GetConnect():
conn = False
try:
conn = psycopg2.connect(**db_config)
except Exception as err:
print("连接数据库失败，%s" % err)
return conn
def setup_hook_clean_db(company_id):
"""
初始化时清理数据库中的历史数据
:return:
"""
db = GetConnect()
cursor = db.cursor()
try:
#获得素币
cursor.execute("DELETE FROM su_money_transaction_records smrt WHERE smrt.operator#>>'{{uid}}' in (select uid::TEXT from employees where company_id={})".format(company_id))
db.commit()
#发送推送
cursor.execute('DELETE FROM sent_notifications sn WHERE sn.notification_id in (select n.id FROM notifications n WHERE n.external_resource IN ( SELECT t.ID FROM tasks t WHERE t.company_id ={} ))'.format(company_id))
db.commit()
#任务接收者
cursor.execute('DELETE FROM rob_infos ri WHERE ri.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id ={} )'.format(company_id))
db.commit()
#驳回任务
cursor.execute('DELETE FROM reject_task_records rtr WHERE rtr.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id = {} )'.format(company_id))
db.commit()
#关闭任务
cursor.execute('DELETE FROM off_task_records otr WHERE otr.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id = {} )'.format(company_id))
db.commit()
#通知
cursor.execute('DELETE FROM notifications n WHERE n.external_resource IN ( SELECT ID FROM tasks T WHERE T.company_id ={} )'.format(company_id))
db.commit()
#参与竞标
cursor.execute('DELETE FROM bid_infos bi WHERE bi.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id = {} )'.format(company_id))
db.commit()
#中标
cursor.execute('DELETE FROM bidder_infos bin WHERE bin.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id = {} )'.format(company_id))
db.commit()
#任务列表
cursor.execute('DELETE from tasks t where t.company_id ={}'.format(company_id))
db.commit()
cursor.execute('update employees set su_money=0 where company_id={}'.format(company_id))
db.commit()
print("delete 成功")
except Exception as err:
# 发生错误时回滚
db.rollback()
print('this is:', err)
db.close()