db_func.py
2.7 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
# @Time :2019/11/26 11:25
# @Author :dengyuting
# @File :db_func.py
import psycopg2
def GetConnect():
conn = False
try:
conn = psycopg2.connect(database="mmmworthtest",
user="bianxinhua",
password="bianxinhua_123",
host="114.55.200.59",
port="31543")
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.employee @> '{"uid":3248817613863936 }' or smrt.employee @> '{"uid":3247366011913216 }' or smrt.employee @> '{"uid":3248817613835264 }'or smrt.employee @> '{"uid":3248817613892608 }')''')
db.commit()
#发送推送
cursor.execute('''DELETE FROM sent_notifications sn WHERE ( sn.receiver @> '{"uid":3248817613863936 }' OR sn.receiver @> '{"uid":3247366011913216 }' OR sn.receiver @> '{"uid":3248817613835264 }' OR sn.receiver @> '{"uid":3248817613892608 }' )''')
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()
#任务列表
db.commit()
cursor.execute('''DELETE from tasks t where t.company_id ={};'''.format(company_id))
print(cursor.rowcount)
db.commit()
print("delete 成功")
except:
# 发生错误时回滚
db.rollback()
print('回滚操作')
db.close()