db_func.py
4.1 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
# @Time :2019/11/26 11:25
# @Author :dengyuting
# @File :db_func.py
import pymysql
db_config ={"host": "101.37.68.23",
"port": 3306,
"user": "dengyuting",
"password": "dyt_123456",
"db": "opportunity_test",
"charset": 'utf8'}
def get_id1_from_database(tbl, company_id, column, column_value):
db = pymysql.connect(**db_config)
cursor = db.cursor()
company_id = str(company_id)
column_value = str(column_value)
sql= "select id from " + tbl + " where company_id=" + company_id + " and " + column + "=" + column_value + \
" and delete_at=0"
try:
cursor.execute(sql)
result = cursor.fetchone()
return result[0]
except Exception as err:
# 发生错误时回滚
print(err)
db.rollback()
db.close()
def get_chanceid_from_database(company_id):
db = pymysql.connect(**db_config)
cursor = db.cursor()
company_id = str(company_id)
sql= "select id from chance where company_id=" + company_id + " order by create_at desc"
try:
cursor.execute(sql)
result = cursor.fetchone()
return result[0]
except Exception as err:
# 发生错误时回滚
print(err)
db.rollback()
db.close()
def setup_hook_clean_db(companyid):
"""
初始化时清理数据库中对应公司的历史数据
:return:
"""
db = pymysql.connect(**db_config)
cursor = db.cursor()
companyid = str(companyid)
# get_adminid = 'select admin_id from company where id='+companyid
# get_user_company_id = 'select id from user_company where company_id='+companyid
try:
# cursor.execute(get_adminid)
# result = cursor.fetchone()
# admin_id = result[0]
# admin_id = str(admin_id)
# cursor.execute(get_user_company_id)
# result = cursor.fetchone()
# user_company_id = result[0]
# user_company_id = str(user_company_id)
# cursor.execute('delete from department where company_id=' + companyid + ' and is_top!=1')
# cursor.execute('delete from user_company where company_id=' + companyid + ' and user_id!='+admin_id)
# cursor.execute('delete from user_department where company_id=' + companyid + ' and user_company_id!='+ user_company_id)
# cursor.execute('delete from position where company_id=' + companyid)
# cursor.execute('delete from user_position where company_id=' + companyid)
# cursor.execute('delete from role where company_id=' + companyid + ' and is_default!=1')
# cursor.execute('delete from user_role where company_id=' + companyid)
# cursor.execute('delete from chance_type where company_id=' + companyid)
# cursor.execute('delete from audit_template where company_id=' + companyid)
# cursor.execute('delete from audit_form where company_id=' + companyid)
cursor.execute('delete a from chance_data a left JOIN chance b on a.chance_id=b.id where b.company_id=' + companyid)
db.commit()
cursor.execute('delete a from audit_flow_process a left JOIN chance b on a.chance_id=b.id where b.company_id=' + companyid)
db.commit()
cursor.execute('delete a from chance_department a left JOIN chance b on a.chance_id=b.id where b.company_id=' + companyid)
db.commit()
cursor.execute('delete from user_msg where receive_user_id in (select id from user_company where company_id=' + companyid + ') ' \
'or sender_user_id in (select id from user_company where company_id=' + companyid +')')
db.commit()
cursor.execute('delete from comment where user_id in (select id from user_company where company_id=' + companyid + ')')
db.commit()
cursor.execute('delete from chance_favorite where user_id in (select id from user_company where company_id=' + companyid + ')')
db.commit()
#这句要放在最后执行
cursor.execute('delete from chance where company_id=' + companyid)
db.commit()
print("delete OK")
except Exception as err:
# 发生错误时回滚
print("this is:", err)
db.rollback()
db.close()