· 8 years ago · Apr 06, 2018, 01:48 AM
1import gzip
2import re
3import shutil
4import sqlite3
5import xml.etree.ElementTree as ET
6
7integer = re.compile(r'^[-+]?[0-9]+$')
8real = re.compile(r'^[-+]?[0-9]+\.[0-9]*$')
9sanitize = re.compile('[^0-9a-zA-Z_]+')
10
11
12def predict_type(value):
13 if integer.match(value):
14 return "INTEGER"
15 if real.match(value):
16 return "REAL"
17 return "TEXT"
18
19
20class Reader:
21 def __init__(self, path = 'RAN_example_s.xml', inplace=True):
22 """
23
24 :param path: path to file
25 :param inplace: if true, the file will be decompressed in time, else file will be
26 fully decompressed and after that decompressed file will be read.
27 In the first situation, parsing will be slower than in second
28 """
29 self.ns = ''
30 if path.split('.')[-1] == 'xml':
31 file = path
32 elif path.split('.')[-1] == 'gz':
33 if inplace:
34 file = gzip.GzipFile(path)
35 else:
36 new_path = '.'.join(path.split('.')[:-1])
37 with open(new_path, 'wb') as f_in:
38 with gzip.open(path, 'rb') as f_out:
39 shutil.copyfileobj(f_out, f_in)
40 file = new_path
41 else:
42 raise ValueError("Supports only xml and gz files")
43 self.tree = ET.iterparse(file, events=('start-ns', 'start', 'end'))
44
45 self.tables = dict()
46
47 self.db = None
48 self.cursor = None
49
50 self.process_tree()
51
52 self.db.commit()
53
54 def process_tree(self):
55 for event, element in self.tree:
56 if event == 'start-ns':
57 # getting namespace namespace
58 self.ns = element[1]
59
60 elif event == 'start':
61 if element.tag == self.get_tag('header'):
62 date = self.get_date()
63 name = "Dump_{}.sqlite3".format(date.split('T')[0])
64 self.db = sqlite3.connect(name)
65 self.cursor = self.db.cursor()
66 continue
67
68 elif element.tag == self.get_tag('managedObject'):
69 self.process_managed_object(element)
70
71 element.clear()
72
73 def process_managed_object(self, root):
74
75 obj = {}
76 table = root.attrib['class']
77 primary_str = root.attrib.get('distName', "/")
78 primary_elements = self.get_primary_keys(primary_str)
79
80 obj.update(primary_elements)
81
82 # Loop over elements of managedObject
83 for event, element in self.tree:
84
85 # exit condition
86 if event == 'end' and element.tag == self.get_tag('managedObject'):
87
88 # Skip situation when element doesn't have any elements
89 if len(element) == 0:
90 return
91
92 if table not in self.tables:
93 self.create_table(table, obj, primary_elements)
94
95 self.insert_value(table, obj)
96 print(obj)
97
98 # Deleting all preprocessed tree elements
99 # Needed for reducing memory usage
100 root.clear()
101 return
102
103 elif event == 'start' and element.tag == self.get_tag('list'):
104 value = self.process_list(element, table, primary_elements)
105
106 # for lists without attribute name.
107 # If value is None it's means that list is a different table
108 if value is not None:
109 name = element.attrib['name']
110 obj[name] = value
111
112 elif event == 'start' and element.tag == self.get_tag('p'):
113 self.set_element(obj, element)
114
115 def process_list(self, root, table, primary_elements):
116 table = "{}_{}".format(table, root.attrib['name'])
117 primary_elements = primary_elements.copy()
118
119 for event, element in self.tree:
120 if event == 'end' and element.tag == self.get_tag('list'):
121 root.clear()
122 return
123
124 elif event == 'start' and element.tag == self.get_tag('item'):
125 self.process_list_item(table, primary_elements)
126
127 elif event == 'start' and element.tag == self.get_tag('p'):
128 return '{} {}'.format(element.text if element.text else '', self.get_options()).strip()
129
130 def process_list_item(self, table, primary_elements):
131 primary_elements = primary_elements.copy()
132 obj = {}
133 obj.update(primary_elements)
134
135 for event, element in self.tree:
136 if event == 'end' and element.tag == self.get_tag('item'):
137 if table not in self.tables:
138 self.create_table(table, obj, {})
139
140 self.insert_value(table, obj)
141 return
142
143 elif event == 'start' and element.tag == self.get_tag('p'):
144 self.set_element(obj, element)
145
146 @staticmethod
147 def sanitize(string):
148 if string == "NULL":
149 return "NULL"
150 predicted_type = predict_type(string)
151 if predicted_type == "TEXT":
152 return "'{}'".format(string) # sanitize_text.sub(' ', string))
153 return string
154
155 def create_table(self, table, obj, primary_elements):
156
157 # creating list with all columns where primaries are the first columns
158 primary_columns = list(primary_elements.keys())
159 all_columns = primary_columns + list(obj.keys() - set(primary_columns))
160
161 # Save those columns for inserting row elements in right order
162 self.tables[table] = all_columns
163
164 # Build SQL query for creating table
165 columns_str = ", ".join("{} {}".format(key, predict_type(obj[key]))
166 for key in all_columns)
167
168 if primary_elements:
169 columns_str += ', PRIMARY KEY({})'.format(', '.join(primary_columns))
170 sql_query = "CREATE TABLE IF NOT EXISTS {table_name} ({columns});"
171 sql_query = sql_query.format(table_name=table, columns=columns_str)
172 try:
173 self.cursor.execute(sql_query)
174 except Exception as e:
175 print(e)
176 print(sql_query)
177 exit(1)
178
179 def insert_value(self, table, obj):
180
181 # Building query for inserting value
182 columns = self.tables[table]
183 values = [self.sanitize(obj.get(key, 'NULL')) for key in columns]
184
185 columns = ', '.join(columns)
186
187 sql_query = "INSERT OR REPLACE INTO {table} ({column_list}) VALUES({value_list});"
188 sql_query = sql_query.format(table=table, column_list=columns, value_list=', '.join('?' * len(values)))
189 try:
190 self.cursor.execute(sql_query, values)
191 except Exception as e:
192 print(e)
193 print(sql_query)
194 exit(1)
195
196 def set_element(self, obj, element):
197 name = element.attrib['name']
198 value = element.text
199
200 # Dirty hack. Don't change this. It will save you a lot of time.
201 if value is None:
202 _, element = next(self.tree)
203 value = element.text
204
205 obj[name] = value
206
207 def get_tag(self, tag):
208 return "{{{ns}}}{tag}".format(ns=self.ns, tag=tag)
209
210 def get_date(self):
211 date = None
212 for event, element in self.tree:
213
214 if event == 'end' and element.tag == self.get_tag('header'):
215 if date is None:
216 raise ValueError("Date not found in header!")
217 return date
218
219 elif event == 'start' and element.tag == self.get_tag('log'):
220 date = element.attrib['dateTime']
221
222 def get_options(self):
223 tree = self.tree
224 result = []
225 for event, element in tree:
226 if event == 'end' and element.tag == self.get_tag('list'):
227 return ' '.join(result)
228
229 elif event == 'start' and element.tag == self.get_tag('p'):
230 if element.text is None:
231 try:
232 element = next(element)
233 result.append(element.text)
234 except (TypeError, StopIteration):
235 pass
236 else:
237 result.append(element.text)
238
239 @staticmethod
240 def get_primary_keys(string):
241 values = string.split('/')
242 keys = {}
243 for value in values[1:]:
244 key = '_'.join(value.split('-')[:-1])
245 key = sanitize.sub('_', key)
246 value = value.split('-')[-1]
247 keys[key] = value
248 return keys
249
250
251if __name__ == "__main__":
252 import time
253
254 start_time = time.time()
255 Reader(path='Dump_20171210.xml', inplace=False)
256 print((time.time() - start_time) / 60)