· 8 years ago · Feb 12, 2018, 01:58 PM
1import sqlite3 as lite
2import mail_param
3from c_logger import CustomLogger
4from datetime import datetime as dt
5
6log = CustomLogger('{0}_log_file.txt'.format(dt.now().date()))
7
8def p_decorate(func):
9 def func_wrapper(*args, **kwargs):
10 log.debug("Running function {0} with number of arguments {1}".format(func.__name__, len(args) + len(kwargs)))
11 return func(*args, **kwargs)
12 return func_wrapper
13
14@p_decorate
15def put_sigle_row_into_table(issue_id, current_status, historical_status, issue_type, issue_name, db_file_name,
16 db_table, con=None, cur=None, notified=False):
17 """
18 :param issue_id:
19 :param current_status:
20 :param historical_status:
21 :param issue_type:
22 :param issue_name:
23 :param db_file_name:
24 :param db_table:
25 :param con:
26 :param cur:
27 :param notified:
28 :return:
29 """
30 created_inline = False
31 if not con and not cur:
32 cur, con = create_connection_to_db(db_file_name)
33 created_inline = True
34 try:
35 cur.execute('INSERT INTO {0}("issue_id", "current_status", "historical_status", "notified", "issue_type", '
36 '"friendly_name") VALUES ({1}, "{2}", "{3}", "{4}", "{5}", "{6}");'.format(db_table, issue_id,
37 current_status,
38 historical_status,
39 notified, issue_type,
40 issue_name))
41 except lite.IntegrityError:
42 cur.execute('UPDATE {0} SET "current_status" = "{1}", "historical_status" = "{2}",'
43 '"issue_type" = "{5}", "friendly_name" = "{6}" WHERE "issue_id" = "{4}";'.format(db_table,
44 current_status,
45 historical_status,
46 notified, issue_id,
47 issue_type,
48 issue_name))
49 except:
50 # create new table if the one with name not exists
51 cur.execute('CREATE TABLE {0}("issue_id" INTEGER, "current_status", "historical_status", "notified", '
52 '"issue_type", "friendly_name", UNIQUE("issue_id"));'.format(db_table))
53 cur.execute('INSERT INTO {0}("issue_id", "current_status", "historical_status", "notified", "issue_type", '
54 '"friendly_name") VALUES ({1}, "{2}", "{3}", "{4}", "{5}", "{6}");'.format(db_table, issue_id,
55 current_status,
56 historical_status,
57 notified, issue_type,
58 issue_name))
59 if created_inline:
60 con.commit()
61 else:
62 return con
63
64@p_decorate
65def update_notification_column(db_file_name, db_table_name, issue_id, notified=False):
66 cur, con = create_connection_to_db(db_file_name)
67 cur.execute('UPDATE {0} SET "notified" = "{1}" WHERE "issue_id" = "{2}";'.format(db_table_name, notified, issue_id))
68 con.commit()
69
70@p_decorate
71def get_full_data_from_table(db_file_name, db_table_name):
72 """
73 :param db_file_name: path to db file
74 :param db_table_name: name of table in db
75 :return: data from db
76 """
77 cur, con = create_connection_to_db(db_file_name)
78 cur.execute('SELECT * FROM {0};'.format(db_table_name))
79 data = cur.fetchall()
80 return data
81
82@p_decorate
83def get_data_with_issue_id(db_file_name, db_table_name, issue_id, cur=False, con=False):
84 """
85 :param db_file_name:
86 :param db_table_name:
87 :param issue_id:
88 :return:
89 """
90 if not con and not cur:
91 cur, con = create_connection_to_db(db_file_name)
92 cur.execute('SELECT * FROM {0} WHERE "issue_id" = {1};'.format(db_table_name, issue_id))
93 data = cur.fetchall()
94 return data
95
96@p_decorate
97def get_data_for_notification(db_file_name, db_table_name):
98 """
99 :param db_file_name:
100 :param db_table_name:
101 :return:
102 """
103 cur, con = create_connection_to_db(db_file_name)
104 cur.execute('SELECT * FROM {0} WHERE "current_status" = "done" AND "notified" = "False";'.format(db_table_name))
105 data = cur.fetchall()
106 return data
107
108@p_decorate
109def delete_row_from_table(db_file_name, db_table_name, line):
110 """
111 :param db_file_name: path to db file
112 :param db_table_name: name of table in db
113 :return: None
114 """
115 cur, con = create_connection_to_db(db_file_name)
116 cur.execute('DELETE FROM {0} WHERE lines = "{1}";'.format(db_table_name, line))
117 con.commit()
118
119
120@p_decorate
121def create_connection_to_db(db_file_name):
122 """
123 :param db_file_name: name of db file
124 :return: cursor and connection objects
125 """
126 connection = lite.connect(db_file_name)
127 cur = connection.cursor()
128 return cur, connection
129
130@p_decorate
131def put_multiple_data_into_table(db_file_name, table_name, in_list, val_list, drop_db=False):
132 """
133 :param db_file_name:
134 :param table_name:
135 :param in_list:
136 :param val_list:
137 :param drop_db:
138 :return:
139 """
140 cur, con = create_connection_to_db(db_file_name)
141 if not drop_db:
142 cur.execute('DELETE FROM {0};'.format(table_name))
143 for line, value in zip(in_list, val_list):
144 con = put_sigle_row_into_table(line, value, db_file_name, table_name, con, cur)
145 con.commit()
146
147@p_decorate
148def generate_sql_input(file):
149 """Temporary func to create input list from txt file
150 :param file: name of file contains lines with mail intent
151 :return: prepared text with " added on start and end
152 """
153 with open(file, 'r') as f:
154 data = f.readlines()
155 stripped_data = ['"{0}"'.format(item.strip()) for item in data]
156 return stripped_data
157
158# if __name__ == '__main__':
159# # put_sigle_row_into_table('12241', 'closed', 'candidate', mail_param.db_path, 'RECRUITT')
160# # print(get_data_for_notification(mail_param.db_path, 'RECRUITT'))
161# print(get_data_with_issue_id(mail_param.db_path, 'RECRUITT', '12241'))