Revision 12:1e59c54b9652 TroubleTicket_db2db.py
b/TroubleTicket_db2db.py | ||
---|---|---|
6 | 6 |
# Author: Panos Louridas, from an initial version by Spyros Kopsidas |
7 | 7 |
# Copyright: (c) EGEE Project 2009 |
8 | 8 |
# Licence: Apache Software License |
9 |
|
|
9 |
# |
|
10 | 10 |
#------------------------------------------------------------------------------- |
11 | 11 |
|
12 | 12 |
import sys |
... | ... | |
20 | 20 |
from TroubleTicketParser import * |
21 | 21 |
|
22 | 22 |
def get_unparsed_emails(cursor, table): |
23 |
query = """SELECT Message_ID, Subject, Header, Body |
|
24 |
FROM %s |
|
25 |
WHERE IS_STANDARDISED IS NULL""" |
|
26 |
# "ORDER BY Date_Stored""" |
|
27 |
cursor.execute(query % (table)) |
|
23 |
""" A generator for returning unparsed emails.""" |
|
24 |
# Not using the more practical """ string because we want to |
|
25 |
# be able to add easily the following line for testing: |
|
26 |
# "AND header like '%grnet%' AND body like '%9966%' " |
|
27 |
query = "SELECT Message_ID, Subject, Header, Body " + \ |
|
28 |
"FROM " + table + " " + \ |
|
29 |
"WHERE IS_STANDARDISED IS NULL " + \ |
|
30 |
"ORDER BY Date_Stored ASC" |
|
31 |
cursor.execute(query) |
|
32 |
print "Found %d raw mails." % cursor.rowcount |
|
28 | 33 |
|
29 | 34 |
while (1): |
30 | 35 |
row = cursor.fetchone() |
... | ... | |
39 | 44 |
yield email |
40 | 45 |
|
41 | 46 |
def get_datetime_str(raw_datetime): |
47 |
"""A utility function for returning datetime objects out of strings. |
|
48 |
Tries to parse them according to the following rules: |
|
49 |
%d-%m-%Y |
|
50 |
%Y-%m-%d""" |
|
42 | 51 |
try: |
43 | 52 |
dt = datetime.strptime(raw_datetime, "%d-%m-%Y") |
44 | 53 |
except ValueError: |
... | ... | |
48 | 57 |
return None |
49 | 58 |
return dt.isoformat() |
50 | 59 |
|
51 |
def insert_values(values, db, destination_table, cursor, ttid): |
|
60 |
def insert_values(values, db, destination_table, connection, ttid): |
|
61 |
"""Inserts the normalised message represented by the values dictionary |
|
62 |
to the database at the given destination_table. ttid is the original |
|
63 |
message id in the unnormalised table.""" |
|
52 | 64 |
|
53 | 65 |
# Find messages previously normalised with the same NREN / ID combination |
54 | 66 |
# as this will be an update. The previous ones will have their |
55 | 67 |
# MESSAGE_RNUMBER attribute increased by 1. |
68 |
cursor = connection.cursor() |
|
56 | 69 |
original_id = int(values.get("ORIGINAL_ID", "")) |
57 | 70 |
partner_id = values.get("NREN", "") |
58 | 71 |
query = """SELECT ENOC_ID, MESSAGE_RNUMBER |
... | ... | |
60 | 73 |
WHERE ORIGINAL_ID = %d AND PARTNER_ID = '%s'""" |
61 | 74 |
query = query % (destination_table, original_id, partner_id) |
62 | 75 |
cursor.execute(query) |
63 |
while (1): |
|
64 |
row = cursor.fetchone() |
|
65 |
if row == None: |
|
66 |
break |
|
67 |
enoc_id = row[0] |
|
68 |
message_rnumber = row[1] |
|
76 |
# We need to fetchall to ensure that we have all the data in a |
|
77 |
# snapshot in our program, so that the update that follows |
|
78 |
# work correctly. |
|
79 |
previous_messages = cursor.fetchall() |
|
80 |
for message in previous_messages: |
|
81 |
enoc_id = message[0] |
|
82 |
message_rnumber = message[1] |
|
69 | 83 |
update_stmt = 'UPDATE ' + destination_table + ' ' + \ |
70 | 84 |
'SET MESSAGE_RNUMBER = MESSAGE_RNUMBER + 1 ' + \ |
71 | 85 |
'WHERE ENOC_ID = ' + str(enoc_id) |
... | ... | |
73 | 87 |
|
74 | 88 |
statement_pattern = "INSERT INTO " + destination_table + """ |
75 | 89 |
(ORIGINAL_ID, |
90 |
UN_ID, |
|
91 |
PARTNER_ID, |
|
76 | 92 |
TT_OPEN_DATETIME, |
77 | 93 |
OPEN_ENGINEER, |
78 | 94 |
CLOSE_ENGINEER, |
... | ... | |
89 | 105 |
ADDITIONAL_DATA, |
90 | 106 |
HISTORY, |
91 | 107 |
MESSAGE_RNUMBER) |
92 |
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, |
|
108 |
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
|
|
93 | 109 |
%s, %s, %s, %s, %s, %s, %s, %s)""" |
94 | 110 |
|
95 | 111 |
raw_datetime = values.get("TT_OPEN_DATETIME", "") |
... | ... | |
103 | 119 |
|
104 | 120 |
statement_params = ( |
105 | 121 |
original_id, |
106 |
# partner_id, |
|
122 |
ttid, |
|
123 |
partner_id, |
|
107 | 124 |
tt_open_datetime, |
108 | 125 |
values.get("OPEN_ENGINEER", ""), |
109 | 126 |
values.get("CLOSE_ENGINEER", ""), |
... | ... | |
123 | 140 |
|
124 | 141 |
# Insert the new normalised ticket. |
125 | 142 |
cursor.execute(statement_pattern, statement_params) |
143 |
cursor.close() |
|
144 |
connection.commit() |
|
126 | 145 |
|
127 | 146 |
def get_query_mark_as_parsed(row_id, db, source_table): |
128 | 147 |
return str("UPDATE `%s`.`%s` SET `IS_STANDARDISED` = '1' WHERE `%s`.`ID` =%d LIMIT 1 ;" % (str(db), str(source_table), str(source_table), row_id)) |
... | ... | |
158 | 177 |
|
159 | 178 |
try: |
160 | 179 |
db2_conn = MySQLdb.connect(**TTConfig.TTdb_v2) |
161 |
db2_cursor = db2_conn.cursor() |
|
162 | 180 |
except: |
163 | 181 |
print "Error connecting to database " + str(TTConfig.TTdb_v2['db']) |
164 | 182 |
exit() |
... | ... | |
180 | 198 |
ttp.parse(raw_mail) |
181 | 199 |
parse_results = ttp.resultset |
182 | 200 |
num_found_keys = len(parse_results.keys()) |
183 |
if not 'ORIGINAL_ID' in parse_results: |
|
184 |
continue |
|
185 |
if not parse_results['ORIGINAL_ID'].isdigit(): |
|
186 |
continue |
|
187 |
if num_found_keys >= TTConfig.configuration['min_keys_parsed']: |
|
201 |
if ('ORIGINAL_ID' in parse_results |
|
202 |
and parse_results['ORIGINAL_ID'].isdigit() |
|
203 |
and num_found_keys >= TTConfig.configuration['min_keys_parsed']): |
|
188 | 204 |
insert_values(parse_results, |
189 | 205 |
TTConfig.TTdb_v2['db'], |
190 | 206 |
TTConfig.configuration['stdTickets_table'], |
191 |
db2_cursor,
|
|
207 |
db2_conn,
|
|
192 | 208 |
raw_mail['ID']) |
193 | 209 |
rows_inserted = rows_inserted + 1 |
194 |
overwrite = overwrite_print("Mails read: %d; normalised" |
|
210 |
overwrite = overwrite_print("Mails read: %d; normalised: %d"
|
|
195 | 211 |
% (mails_read, rows_inserted), |
196 | 212 |
overwrite) |
197 | 213 |
|
198 |
|
|
199 | 214 |
db1_conn.close() |
200 | 215 |
db2_conn.commit() |
201 | 216 |
db2_conn.close() |
Also available in: Unified diff