Revision 12:1e59c54b9652
| 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