root / TroubleTicket_db2db.py @ 15:5e82fb01dfb3
History | View | Annotate | Download (7.7 kB)
1 |
#!/usr/bin/env python
|
---|---|
2 |
#-------------------------------------------------------------------------------
|
3 |
# Name: TroubleTicketParser.py
|
4 |
# Purpose: To parse Trouble Tickets mail messages stored in database
|
5 |
#
|
6 |
# Author: Panos Louridas, from an initial version by Spyros Kopsidas
|
7 |
# Copyright: (c) EGEE Project 2009
|
8 |
# Licence: Apache Software License
|
9 |
#
|
10 |
#-------------------------------------------------------------------------------
|
11 |
|
12 |
import sys |
13 |
import getopt |
14 |
|
15 |
from datetime import datetime |
16 |
|
17 |
import TTConfig |
18 |
import MySQLdb |
19 |
|
20 |
from TroubleTicketParser import * |
21 |
|
22 |
def get_unparsed_emails(cursor, 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 |
33 |
|
34 |
while (1): |
35 |
row = cursor.fetchone() |
36 |
if row is None: |
37 |
return
|
38 |
email = { |
39 |
'ID': row[0], |
40 |
'subject' : row[1], |
41 |
'header' : row[2], |
42 |
'body' : row[3] |
43 |
} |
44 |
yield email
|
45 |
|
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"""
|
51 |
try:
|
52 |
dt = datetime.strptime(raw_datetime, "%d-%m-%Y")
|
53 |
except ValueError: |
54 |
try:
|
55 |
dt = datetime.strptime(raw_datetime, "%Y-%m-%d")
|
56 |
except ValueError: |
57 |
return None |
58 |
return dt.isoformat()
|
59 |
|
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
|
63 |
message id in the unnormalised email table."""
|
64 |
|
65 |
# Find messages previously normalised with the same NREN / ID combination
|
66 |
# as this will be an update. The previous ones will have their
|
67 |
# MESSAGE_RNUMBER attribute increased by 1.
|
68 |
cursor = connection.cursor() |
69 |
original_id = int(values.get("ORIGINAL_ID", "")) |
70 |
partner_id = values.get("NREN", "") |
71 |
query = """SELECT ENOC_ID, MESSAGE_RNUMBER
|
72 |
FROM %s
|
73 |
WHERE ORIGINAL_ID = %d AND PARTNER_ID = '%s'"""
|
74 |
query = query % (destination_table, original_id, partner_id) |
75 |
cursor.execute(query) |
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]
|
83 |
update_stmt = 'UPDATE ' + destination_table + ' ' + \ |
84 |
'SET MESSAGE_RNUMBER = MESSAGE_RNUMBER + 1 ' + \
|
85 |
'WHERE ENOC_ID = ' + str(enoc_id) |
86 |
cursor.execute(update_stmt) |
87 |
|
88 |
statement_pattern = "INSERT INTO " + destination_table + """ |
89 |
(ORIGINAL_ID,
|
90 |
UNNORMALISED_ID,
|
91 |
PARTNER_ID,
|
92 |
TT_OPEN_DATETIME,
|
93 |
OPEN_ENGINEER,
|
94 |
CLOSE_ENGINEER,
|
95 |
TT_LASTUPDATE_DATETIME,
|
96 |
TT_TYPE,
|
97 |
NETWORK_NODE,
|
98 |
NETWORK_LINK_CIRCUIT,
|
99 |
TT_TITLE,
|
100 |
TT_SHORT_DESCRIPTION,
|
101 |
TT_PRIORITY,
|
102 |
TT_STATUS,
|
103 |
TT_CLOSE_DATETIME,
|
104 |
TT_LONG_DESCRIPTION,
|
105 |
ADDITIONAL_DATA,
|
106 |
HISTORY,
|
107 |
MESSAGE_RNUMBER)
|
108 |
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
|
109 |
%s, %s, %s, %s, %s, %s, %s, %s)"""
|
110 |
|
111 |
raw_datetime = values.get("TT_OPEN_DATETIME", "") |
112 |
tt_open_datetime = get_datetime_str(raw_datetime) |
113 |
if tt_open_datetime is None: |
114 |
return
|
115 |
raw_datetime = values.get("TT_CLOSE_DATETIME", "") |
116 |
tt_close_datetime = get_datetime_str(raw_datetime) |
117 |
raw_datetime = values.get("TT_LASTUPDATE_DATETIME", "") |
118 |
tt_lastupdate_datetime = get_datetime_str(raw_datetime) |
119 |
|
120 |
statement_params = ( |
121 |
original_id, |
122 |
ttid, |
123 |
partner_id, |
124 |
tt_open_datetime, |
125 |
values.get("OPEN_ENGINEER", ""), |
126 |
values.get("CLOSE_ENGINEER", ""), |
127 |
tt_lastupdate_datetime, |
128 |
values.get("TT_TYPE", ""), |
129 |
values.get("NETWORK_NODE", ""), |
130 |
values.get("NETWORK_LINK_CIRCUIT", ""), |
131 |
values.get("TT_TITLE", ""), |
132 |
values.get("TT_SHORT_DESCRIPTION", ""), |
133 |
values.get("TT_PRIORITY", ""), |
134 |
values.get("TT_STATUS", ""), |
135 |
tt_close_datetime, |
136 |
values.get("TT_LONG_DESCRIPTION", ""), |
137 |
values.get("ADDITIONAL_DATA", ""), |
138 |
values.get("TT_HISTORY", ""), |
139 |
1)
|
140 |
|
141 |
# Insert the new normalised ticket.
|
142 |
cursor.execute(statement_pattern, statement_params) |
143 |
cursor.close() |
144 |
connection.commit() |
145 |
|
146 |
def mark_as_parsed(connection, db, source_table, ttid): |
147 |
"""Marks the row with key ttid as processed in the email table."""
|
148 |
update_stmt = "UPDATE %s.%s SET IS_STANDARDISED = 1 " + \
|
149 |
"WHERE %s.Message_ID = '%s'"
|
150 |
update_stmt = update_stmt % (db, source_table, source_table, ttid) |
151 |
cursor = connection.cursor() |
152 |
cursor.execute(update_stmt) |
153 |
cursor.close() |
154 |
connection.commit() |
155 |
|
156 |
def overwrite_print(output_str, overwrite): |
157 |
"""Outputs output_str on top of the current output by overwrite
|
158 |
characters. Returns the number of characters output."""
|
159 |
sys.stdout.write("%s%s\r" % (output_str, " " * overwrite)) |
160 |
return len(output_str) |
161 |
|
162 |
def usage(): |
163 |
print "python TroubleTicket_db2db.py -passwd1=password1 -passwd2=password2" |
164 |
|
165 |
def main(argv): |
166 |
try:
|
167 |
opts, args = getopt.getopt(argv, "", ["passwd1=", "passwd2="]) |
168 |
except getopt.GetoptError:
|
169 |
usage() |
170 |
sys.exit(2)
|
171 |
for o, a in opts: |
172 |
if o == "--passwd1": |
173 |
TTConfig.TTdb['passwd'] = a
|
174 |
elif o == "--passwd2": |
175 |
TTConfig.TTdb_v2['passwd'] = a
|
176 |
else:
|
177 |
assert False, "unhandled option: " + o |
178 |
|
179 |
raw_db = TTConfig.TTdb['db']
|
180 |
try:
|
181 |
db1_conn = MySQLdb.connect(**TTConfig.TTdb) |
182 |
db1_cursor = db1_conn.cursor() |
183 |
except:
|
184 |
print "Error connecting to database " + raw_db |
185 |
print TTConfig.TTdb
|
186 |
exit()
|
187 |
|
188 |
normalised_db = TTConfig.TTdb_v2['db']
|
189 |
try:
|
190 |
db2_conn = MySQLdb.connect(**TTConfig.TTdb_v2) |
191 |
except:
|
192 |
print "Error connecting to database " + normalised_db |
193 |
exit()
|
194 |
|
195 |
print "Successfully connected to databases." |
196 |
print "Trying to read unparsed tickets from " + str(TTConfig.TTdb['db']) \ |
197 |
+ "..."
|
198 |
|
199 |
emails_table = TTConfig.configuration['table_raw_emails']
|
200 |
|
201 |
ttp = TroubleTicketParser() |
202 |
|
203 |
mails_read = 0
|
204 |
rows_inserted = 0
|
205 |
overwrite = 0
|
206 |
for raw_mail in get_unparsed_emails(db1_cursor, emails_table): |
207 |
mails_read = mails_read + 1
|
208 |
num_found_keys = 0
|
209 |
ttp.parse(raw_mail) |
210 |
parse_results = ttp.resultset |
211 |
num_found_keys = len(parse_results.keys())
|
212 |
if ('ORIGINAL_ID' in parse_results |
213 |
and parse_results['ORIGINAL_ID'].isdigit() |
214 |
and num_found_keys >= TTConfig.configuration['min_keys_parsed']): |
215 |
ttid = raw_mail['ID']
|
216 |
insert_values(parse_results, |
217 |
TTConfig.TTdb_v2['db'],
|
218 |
TTConfig.configuration['stdTickets_table'],
|
219 |
db2_conn, |
220 |
ttid) |
221 |
mark_as_parsed(db1_conn, raw_db, emails_table, ttid) |
222 |
rows_inserted = rows_inserted + 1
|
223 |
overwrite = overwrite_print("Mails read: %d; normalised: %d"
|
224 |
% (mails_read, rows_inserted), |
225 |
overwrite) |
226 |
|
227 |
db1_conn.close() |
228 |
db2_conn.close() |
229 |
|
230 |
if __name__ == "__main__": |
231 |
main(sys.argv[1:])
|
232 |
|