db_func.py
4.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
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()