db_func.py
2.3 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
# @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():
"""
初始化时清理数据库中的历史数据
:return:
"""
db = GetConnect()
cursor = db.cursor()
try:
#删除评论
cursor.execute("delete d from comment d LEFT JOIN comment a on d.id=a.cid LEFT JOIN question b on a.id = b.id LEFT JOIN departments c on b.relevantDepartmentId =c.id where c.company_id =42 and c.enabled = 1")
'''
--任务列表
DELETE from tasks t where t.company_id =366;
-- 获得素币
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 }');
--发送推送
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 }' );
--任务接收者
DELETE FROM rob_infos ri WHERE ri.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id = 366 );
--驳回任务
DELETE FROM reject_task_records rtr WHERE rtr.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id = 366 );
-- --关闭任务
DELETE FROM off_task_records otr WHERE otr.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id = 366 );
--通知
DELETE FROM notifications n WHERE n.external_resource IN ( SELECT ID FROM tasks T WHERE T.company_id = 366 );
--参与竞标
DELETE FROM bid_infos bi WHERE bi.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id = 366 );
--中标
DELETE FROM bidder_infos bin WHERE bin.task_id IN ( SELECT ID FROM tasks T WHERE T.company_id = 366 );
'''
db.commit()
print("delete OK")
except:
# 发生错误时回滚
db.rollback()
db.close()