root / TroubleTicket_db2db.py @ 16:f43fef6d7e8d
History | View | Annotate | Download (8.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 |
|
14 |
from optparse import OptionParser |
15 |
|
16 |
from datetime import datetime |
17 |
|
18 |
import TTConfig |
19 |
import MySQLdb |
20 |
|
21 |
from TroubleTicketParser import * |
22 |
|
23 |
def get_unparsed_emails(cursor, table): |
24 |
""" A generator for returning unparsed emails."""
|
25 |
# Not using the more practical """ string because we want to
|
26 |
# be able to add easily the following line for testing:
|
27 |
# "AND header like '%grnet%' AND body like '%9966%' "
|
28 |
query = "SELECT Message_ID, Subject, Header, Body " + \
|
29 |
"FROM " + table + " " + \ |
30 |
"WHERE IS_STANDARDISED IS NULL " + \
|
31 |
"AND header like '%grnet%' AND body like '%9966%' " + \
|
32 |
"ORDER BY Date_Stored ASC"
|
33 |
cursor.execute(query) |
34 |
print "Found %d raw mails." % cursor.rowcount |
35 |
|
36 |
while (1): |
37 |
row = cursor.fetchone() |
38 |
if row is None: |
39 |
return
|
40 |
email = { |
41 |
'ID': row[0], |
42 |
'subject' : row[1], |
43 |
'header' : row[2], |
44 |
'body' : row[3] |
45 |
} |
46 |
yield email
|
47 |
|
48 |
def get_partner_original_id(partner_id, original_id): |
49 |
"""Returns the partner_original_id.
|
50 |
|
51 |
Composes the partner_original_id field in the normalised database
|
52 |
by using the rule: partner_id + '_' + original_id.
|
53 |
"""
|
54 |
return partner_id + '_' + str(original_id) |
55 |
|
56 |
def get_datetime_str(raw_datetime): |
57 |
"""A utility function for returning datetime objects out of strings.
|
58 |
|
59 |
Tries to parse them according to the following rules:
|
60 |
%d-%m-%Y
|
61 |
%Y-%m-%d
|
62 |
"""
|
63 |
try:
|
64 |
dt = datetime.strptime(raw_datetime, "%d-%m-%Y")
|
65 |
except ValueError: |
66 |
try:
|
67 |
dt = datetime.strptime(raw_datetime, "%Y-%m-%d")
|
68 |
except ValueError: |
69 |
return None |
70 |
return dt.isoformat()
|
71 |
|
72 |
def insert_values(values, db, destination_table, connection, ttid): |
73 |
"""Insert a normalised message to the database.
|
74 |
|
75 |
Inserts the normalised message represented by the values dictionary
|
76 |
to the database at the given destination_table. ttid is the
|
77 |
message id in the unnormalised email table.
|
78 |
"""
|
79 |
|
80 |
# Find messages previously normalised with the same NREN / ID combination
|
81 |
# as this will be an update. The previous ones will have their
|
82 |
# MESSAGE_RNUMBER attribute increased by 1.
|
83 |
cursor = connection.cursor() |
84 |
original_id = int(values.get("ORIGINAL_ID", "")) |
85 |
partner_id = values.get("NREN", "") |
86 |
partner_original_id = get_partner_original_id(partner_id, original_id) |
87 |
query = """SELECT ENOC_ID, MESSAGE_RNUMBER
|
88 |
FROM %s
|
89 |
WHERE PARTNER_ORIGINAL_ID = '%s'"""
|
90 |
query = query % (destination_table, partner_original_id) |
91 |
cursor.execute(query) |
92 |
# We need to fetchall to ensure that we have all the data in a
|
93 |
# snapshot in our program, so that the update that follows
|
94 |
# work correctly.
|
95 |
previous_messages = cursor.fetchall() |
96 |
for message in previous_messages: |
97 |
enoc_id = message[0]
|
98 |
message_rnumber = message[1]
|
99 |
update_stmt = 'UPDATE ' + destination_table + ' ' + \ |
100 |
'SET MESSAGE_RNUMBER = MESSAGE_RNUMBER + 1 ' + \
|
101 |
'WHERE ENOC_ID = ' + str(enoc_id) |
102 |
cursor.execute(update_stmt) |
103 |
|
104 |
statement_pattern = "INSERT INTO " + destination_table + """ |
105 |
(PARTNER_ORIGINAL_ID,
|
106 |
UNNORMALISED_ID,
|
107 |
TT_OPEN_DATETIME,
|
108 |
OPEN_ENGINEER,
|
109 |
CLOSE_ENGINEER,
|
110 |
TT_LASTUPDATE_DATETIME,
|
111 |
TT_TYPE,
|
112 |
NETWORK_NODE,
|
113 |
NETWORK_LINK_CIRCUIT,
|
114 |
TT_TITLE,
|
115 |
TT_SHORT_DESCRIPTION,
|
116 |
TT_PRIORITY,
|
117 |
TT_STATUS,
|
118 |
TT_CLOSE_DATETIME,
|
119 |
TT_LONG_DESCRIPTION,
|
120 |
ADDITIONAL_DATA,
|
121 |
HISTORY,
|
122 |
MESSAGE_RNUMBER)
|
123 |
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
|
124 |
%s, %s, %s, %s, %s, %s, %s, %s)"""
|
125 |
|
126 |
raw_datetime = values.get("TT_OPEN_DATETIME", "") |
127 |
tt_open_datetime = get_datetime_str(raw_datetime) |
128 |
if tt_open_datetime is None: |
129 |
return
|
130 |
raw_datetime = values.get("TT_CLOSE_DATETIME", "") |
131 |
tt_close_datetime = get_datetime_str(raw_datetime) |
132 |
raw_datetime = values.get("TT_LASTUPDATE_DATETIME", "") |
133 |
tt_lastupdate_datetime = get_datetime_str(raw_datetime) |
134 |
|
135 |
statement_params = ( |
136 |
get_partner_original_id(partner_id, original_id), |
137 |
ttid, |
138 |
tt_open_datetime, |
139 |
values.get("OPEN_ENGINEER", ""), |
140 |
values.get("CLOSE_ENGINEER", ""), |
141 |
tt_lastupdate_datetime, |
142 |
values.get("TT_TYPE", ""), |
143 |
values.get("NETWORK_NODE", ""), |
144 |
values.get("NETWORK_LINK_CIRCUIT", ""), |
145 |
values.get("TT_TITLE", ""), |
146 |
values.get("TT_SHORT_DESCRIPTION", ""), |
147 |
values.get("TT_PRIORITY", ""), |
148 |
values.get("TT_STATUS", ""), |
149 |
tt_close_datetime, |
150 |
values.get("TT_LONG_DESCRIPTION", ""), |
151 |
values.get("ADDITIONAL_DATA", ""), |
152 |
values.get("TT_HISTORY", ""), |
153 |
1)
|
154 |
|
155 |
# Insert the new normalised ticket.
|
156 |
cursor.execute(statement_pattern, statement_params) |
157 |
cursor.close() |
158 |
connection.commit() |
159 |
|
160 |
def mark_as_parsed(connection, db, source_table, ttid): |
161 |
"""Marks the row with key ttid as processed in the email table."""
|
162 |
update_stmt = "UPDATE %s.%s SET IS_STANDARDISED = 1 " + \
|
163 |
"WHERE %s.Message_ID = '%s'"
|
164 |
update_stmt = update_stmt % (db, source_table, source_table, ttid) |
165 |
cursor = connection.cursor() |
166 |
cursor.execute(update_stmt) |
167 |
cursor.close() |
168 |
connection.commit() |
169 |
|
170 |
def overwrite_print(output_str, overwrite): |
171 |
"""Outputs output_str on top of the current output by overwrite
|
172 |
characters. Returns the number of characters output."""
|
173 |
sys.stdout.write("%s%s\r" % (output_str, " " * overwrite)) |
174 |
return len(output_str) |
175 |
|
176 |
def usage(): |
177 |
print "python TroubleTicket_db2db.py -f password1 -t password2" |
178 |
print "python TroubleTicket_db2db.py --passwd1=password1 --passwd2=password2" |
179 |
|
180 |
def main(argv): |
181 |
parser = OptionParser() |
182 |
parser.add_option("-f", "--frompass", dest="frompass", |
183 |
help="password of trouble tickets database",
|
184 |
metavar="PASSWORD")
|
185 |
parser.add_option("-t", "--topass", dest="topass", |
186 |
help="password of normalised database",
|
187 |
metavar="PASSWORD")
|
188 |
(options, args) = parser.parse_args() |
189 |
if options.frompass is None: |
190 |
print "Password of trouble tickets database is missing\n" |
191 |
parser.print_help() |
192 |
exit(-1) |
193 |
if options.topass is None: |
194 |
print "Password of normalised database is missing\n" |
195 |
parser.print_help() |
196 |
exit(-1) |
197 |
|
198 |
TTConfig.TTdb['passwd'] = options.frompass
|
199 |
TTConfig.TTdb_v2['passwd'] = options.topass
|
200 |
|
201 |
raw_db = TTConfig.TTdb['db']
|
202 |
try:
|
203 |
db1_conn = MySQLdb.connect(**TTConfig.TTdb) |
204 |
db1_cursor = db1_conn.cursor() |
205 |
except:
|
206 |
print "Error connecting to database " + raw_db |
207 |
print TTConfig.TTdb
|
208 |
exit()
|
209 |
|
210 |
normalised_db = TTConfig.TTdb_v2['db']
|
211 |
try:
|
212 |
db2_conn = MySQLdb.connect(**TTConfig.TTdb_v2) |
213 |
except:
|
214 |
print "Error connecting to database " + normalised_db |
215 |
exit()
|
216 |
|
217 |
print "Successfully connected to databases." |
218 |
print "Trying to read unparsed tickets from " + str(TTConfig.TTdb['db']) \ |
219 |
+ "..."
|
220 |
|
221 |
emails_table = TTConfig.configuration['table_raw_emails']
|
222 |
|
223 |
ttp = TroubleTicketParser() |
224 |
|
225 |
mails_read = 0
|
226 |
rows_inserted = 0
|
227 |
overwrite = 0
|
228 |
for raw_mail in get_unparsed_emails(db1_cursor, emails_table): |
229 |
mails_read = mails_read + 1
|
230 |
num_found_keys = 0
|
231 |
ttp.parse(raw_mail) |
232 |
parse_results = ttp.resultset |
233 |
num_found_keys = len(parse_results.keys())
|
234 |
if ('ORIGINAL_ID' in parse_results |
235 |
and parse_results['ORIGINAL_ID'].isdigit() |
236 |
and num_found_keys >= TTConfig.configuration['min_keys_parsed']): |
237 |
ttid = raw_mail['ID']
|
238 |
insert_values(parse_results, |
239 |
TTConfig.TTdb_v2['db'],
|
240 |
TTConfig.configuration['stdTickets_table'],
|
241 |
db2_conn, |
242 |
ttid) |
243 |
mark_as_parsed(db1_conn, raw_db, emails_table, ttid) |
244 |
rows_inserted = rows_inserted + 1
|
245 |
overwrite = overwrite_print("Mails read: %d; normalised: %d"
|
246 |
% (mails_read, rows_inserted), |
247 |
overwrite) |
248 |
|
249 |
db1_conn.close() |
250 |
db2_conn.close() |
251 |
|
252 |
if __name__ == "__main__": |
253 |
main(sys.argv[1:])
|
254 |
|