· 8 years ago · Jun 27, 2018, 08:30 PM
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3"""
4author: jeff maxey
5email: jeffrey.maxey@transamerica.com
6purpose: extract MoSes .dbf information to .csv and then to .db
7"""
8from __future__ import print_function
9import os
10import sys
11import glob
12import csv
13import sqlite3
14import pyodbc
15import pandas as pd
16pd.set_option('display.expand_frame_repr', False)
17from dbfread import DBF
18from bs4 import BeautifulSoup as Soup
19import xml.etree.ElementTree as ET
20
21
22def encode_decode(x, enc='cp850', dec='utf-8'):
23 """
24 DBF returns a unicode string encoded as args.input_encoding.
25 We convert that back into bytes and then decode as args.output_encoding.
26 """
27 if not isinstance(x, str):
28 # DBF converts columns into non-str like int, float
29 x = str(x)
30
31 x = x.encode(enc).decode(dec)
32 return x
33
34def convertDBFtoCSV(input_file_path, outdir):
35 print("Converting %s to csv" % input_file_path)
36 dbf_basename = os.path.basename(input_file_path)
37 csv_basename = dbf_basename[:-4] + ".csv"
38 output_file_path = os.path.join(outdir, csv_basename)
39
40 with open(output_file_path, 'w') as csvfile:
41 input_reader = DBF(input_file_path, ignore_missing_memofile=True)
42
43 output_writer = csv.DictWriter(csvfile, delimiter=',', lineterminator='\n', fieldnames=[x for x in input_reader.field_names])
44
45 output_writer.writeheader()
46 for record in input_reader:
47 row = {k: v for k, v in record.items()}
48 output_writer.writerow(row)
49
50
51def BatchCSVtoSQLite(db, csvDir):
52 csv.field_size_limit(sys.maxsize)
53 cnxn = sqlite3.connect(db)
54 cnxn.text_factory = str
55 cursor = cnxn.cursor()
56
57 for csvfile in glob.glob(os.path.join(csvDir, "*.csv")):
58 tablename = os.path.splitext(os.path.basename(csvfile))[0]
59
60 with open(csvfile, "r") as f:
61 reader = csv.reader(f)
62
63 header = True
64 for row in reader:
65 if header:
66 # gather column names from the first row of the csv
67 header = False
68
69 sqlCmd = "DROP TABLE IF EXISTS %s" % tablename
70 cursor.execute(sqlCmd)
71 sqlCmd = "CREATE TABLE %s (%s)" % (tablename, ", ".join(["%s text" % column for column in row]))
72 cursor.execute(sqlCmd)
73
74 for column in row:
75 if column.lower().endswith("_id"):
76 index = "%s__%s" % (tablename, column)
77 sqlCmd = "CREATE INDEX %s on %s (%s)" % (index, tablename, column)
78 cursor.execute(sqlCmd)
79
80 sqlInsert = "INSERT INTO %s VALUES (%s)" % (tablename, ", ".join(["?" for column in row]))
81 rowLength = len(row)
82 else:
83 # skip lines that don'tursor have the right number of columns
84 if len(row) == rowLength:
85 cursor.execute(sqlInsert, row)
86 cnxn.commit()
87 cursor.close()
88 cnxn.close()
89
90
91def RequiredDBFs():
92 list_RequiredDBFs = ["task", "prjbat", "othbat", "taskbat", "extbat", "val", "prodval", "par", "prodpar", "prodasx"]
93 return list_RequiredDBFs
94
95class MoSesApplication(object):
96 def __init__(self, Path):
97 self.__Path = Path
98
99 def trim(self, df):
100 df_obj = df.select_dtypes(['object'])
101 df[df_obj.columns] = df_obj.apply(lambda x: x.str.strip())
102 return df
103
104 def get_UserStamp(self):
105 if self.__UserStamp == "":
106 return "APPMaster"
107 else:
108 return self.__UserStamp
109
110 def set_UserStamp(self, value):
111 self.__UserStamp = value
112
113 UserStamp = property(fget=get_UserStamp, fset=set_UserStamp)
114
115 def get_ProgramFolder(self):
116 return self.__ProgramFolder
117
118 def set_ProgramFolder(self, value):
119 self.__ProgramFolder = value
120
121 ProgramFolder = property(fget=get_ProgramFolder, fset=set_ProgramFolder)
122
123 def get_Path(self):
124 if self.__Path != "":
125 return self.__Path
126 if not self.__Directory is None:
127 self.__Path = self.__Directory.FullName
128 return self.__Path
129
130 Path = property(fget=get_Path)
131
132 def get_Directory(self):
133 if hasattr(self, '_MoSesApplication__Directory'):
134 return self.__Directory
135 if self.__Path != "" and os.path.exists(self.__Path):
136 self.__Directory = os.listdir(self.__Path)
137 return self.__Directory
138
139 Directory = property(fget=get_Directory)
140
141 def get_Conn(self):
142 if hasattr(self, '_MoSesApplication__Conn'):
143 return self.__Conn
144 if self.Path == "":
145 return None
146 try:
147 ConnString = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB="
148 ConnString += self.__Path
149 ConnString += ";Exclusive=Yes; Collate=Machine;NULL=NO;DELETED=YES;BACKGROUNDFETCH=NO;"
150 self.__Conn = pyodbc.connect(ConnString)
151 return self.__Conn
152 except Exception as ex:
153 return None
154
155 Conn = property(fget=get_Conn)
156
157 def get_ProgConn(self):
158 if hasattr(self, '_MoSesApplication__ProgConn'):
159 return self.__ProgConn
160 if self.ProgramFolder == "" or not os.path.exists(self.ProgramFolder):
161 return None
162 try:
163 ConnString = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB="
164 ConnString += self.ProgramFolder
165 ConnString += ";Exclusive=Yes; Collate=Machine;NULL=NO;DELETED=YES;BACKGROUNDFETCH=NO;"
166 self.__ProgConn = pyodbc.connect(ConnString)
167 return self.__ProgConn
168 except Exception as ex:
169 return None
170
171 ProgConn = property(fget=get_ProgConn)
172
173 def get_UserGroups(self):
174 if hasattr(self, '_MoSesApplication__UserGroups'):
175 return self.__UserGroups
176 try:
177 self.__UserGroups = list()
178 dt = self.SelectQuery("SELECT ugrp_id, ugrp_desc FROM ugp ORDER BY ugrp_desc", True)
179 KnownIDs = list()
180 enumerator = dt.Rows.GetEnumerator()
181 while enumerator.MoveNext():
182 Row = enumerator.Current
183 self.__UserGroups.append(Row)
184 KnownIDs.Add(self.Row("ugrp_id"))
185 dt = self.SelectQuery("SELECT ugrp_id, ugrp_id AS ugrp_desc FROM task GROUP BY ugrp_id ORDER BY ugrp_id")
186 enumerator = dt.Rows.GetEnumerator()
187 while enumerator.MoveNext():
188 Row = enumerator.Current
189 if KnownIDs.Contains(self.Row("ugrp_id")):
190 continue
191 KnownIDs.Add("ugrp_id")
192 self.__UserGroups.Add(Row)
193 self.__UserGroups.Sort()
194 except:
195 self.__UserGroups = None
196 finally:
197 return self.__UserGroups
198
199 def Table(self, TableName):
200 if not os.path.exists(os.path.join(self.__Path, TableName + ".dbf")):
201 return None
202 return self.SelectQuery("SELECT * FROM " + TableName)
203
204 def SelectQuery(self, QueryText):
205 cnxn = self.Conn
206 cursor = cnxn.cursor()
207 cursor.execute(QueryText)
208 rows = cursor.fetchall()
209 # return cursor.execute(QueryText)
210 return self.trim(pd.read_sql_query(QueryText, cnxn))
211
212 def ProductFeatureSets(self):
213 if hasattr(self, "_MoSesApplication__ProductFeatureSets"):
214 return self.__ProductFeatureSets
215 if self.Conn is None:
216 return None
217 PFSdt = self.SelectQuery("SELECT prodparid, pardesc FROM prodpar ORDER BY pardesc")
218 self.__ProductFeatureSets = PFSdt
219 return PFSdt
220
221 def AssumptionSets(self):
222 if hasattr(self, "_MoSesApplication__AssumptionSets"):
223 return self.__AssumptionSets
224 if self.Conn is None:
225 return None
226 ASdt = self.SelectQuery("SELECT prodasx.prodasxid, prodasx.parid, "
227 "prodasx.prodparid, par.pardesc, prodasx.ugrp_id "
228 "FROM prodasx LEFT OUTER JOIN par ON "
229 "prodasx.parid = par.parid")
230 self.__AssumptionSets = ASdt
231 return ASdt
232
233 def Variables(self):
234 if hasattr(self, "_MoSesApplication__Variables"):
235 return self.__Variables
236 if self.Conn is None:
237 return None
238 dt = self.Table("var")
239 if dt is None:
240 return None
241 else:
242 self.__Variables = dt
243 return self.__Variables
244
245 def VariableSources(self):
246 if hasattr(self, "_MoSesApplication__VariableSources"):
247 return self.__VariableSources
248 if self.Conn is None:
249 return None
250 dt = self.SelectQuery("SELECT var.varid, varsrc.ugrp_id, varsrc.valtype "
251 "FROM varsrc JOIN var ON varsrc.product = var.product AND varsrc.v_name = var.v_name "
252 "ORDER BY var.varid")
253 if dt is None:
254 return None
255 else:
256 self.__VariableSources = dt
257 return self.__VariableSources
258
259 def get_ID(self):
260 if hasattr(self, "_MoSesApplication__ID"):
261 return self.__ID
262 else:
263 dt = self.Table(TableName="prodasx").to_dict(orient="records")[0]
264 self.__ID = dt
265 return self.__ID
266
267 ID = property(fget=get_ID)
268
269 def get_Description(self, dt, row):
270 self.Description = dt.loc[row, "pardesc"]
271 return self.Description
272
273 def Wildcards(self, WildcardSet):
274 if self.Conn is None:
275 return None
276 wcdt = self.SelectQuery(QueryText="SELECT pathkey, pathdir FROM pthname WHERE wildcrdset = '"
277 + WildcardSet + "'")
278 if wcdt is None:
279 return None
280 wcdt['pathkey'] = wcdt['pathkey'].str.strip()
281 wcdt['pathdir'] = wcdt['pathdir'].str.strip()
282 if wcdt is None:
283 return None
284 self.__Wildcards = wcdt
285 return wcdt
286
287 def WildcardSets(self):
288 if hasattr(self, "_MoSesApplication__WildcardSets"):
289 return self.__WildcardSets
290 if self.Conn is None:
291 return None
292 wcsdt = self.SelectQuery(QueryText="SELECT wildcrdset FROM pthname GROUP BY wildcrdset")
293 if wcsdt is None:
294 return None
295 self.__WildcardSets = wcsdt['wildcrdset'].values.tolist()
296 return self.__WildcardSets
297
298 def SubModels(self):
299 if hasattr(self, "_MoSesApplication__SubModels"):
300 return self.__SubModels
301 if self.Conn is None:
302 return None
303
304 smdt = self.SelectQuery("SELECT modelid, sm_name, path_nm, modeltype FROM modelset ORDER BY sm_name")
305 if smdt is None:
306 return None
307 self.__SubModels = smdt
308 return self.__SubModels
309
310 def Columns(self):
311 if hasattr(self, "_MoSesApplication__Columns"):
312 return self.__Columns
313 if self.Conn is None:
314 return None
315 cshdt = self.SelectQuery(QueryText="SELECT c_name, c_head, product, sl_window, cshid "
316 "FROM csh ORDER BY product, c_name")
317 if cshdt is None:
318 return None
319 self.__Columns = cshdt
320 return self.__Columns
321
322 def ProductFeatureSets(self):
323 if hasattr(self, "_MoSesApplication__ProductFeatureSets"):
324 return self.__ProductFeatureSets
325 if self.Conn is None:
326 return None
327 PFSdt = self.SelectQuery(QueryText="SELECT prodparid, pardesc FROM prodpar ORDER BY pardesc")
328 if PFSdt is None:
329 return None
330 self.__ProductFeatureSets = PFSdt
331 return self.__ProductFeatureSets
332
333 def AssumptionSets(self):
334 if hasattr(self, "_MoSesApplication__AssumptionSets"):
335 return self.__AssumptionSets
336 if self.Conn is None:
337 return None
338 ASdt = self.SelectQuery("SELECT prodasx.prodasxid, prodasx.parid, prodasx.prodparid, "
339 "par.pardesc, prodasx.ugrp_id "
340 "FROM prodasx LEFT OUTER JOIN par ON prodasx.parid = par.parid")
341 if ASdt is None:
342 return None
343 self.__AssumptionSets = ASdt
344 return self.__AssumptionSets
345
346 def Formulas(self):
347 if hasattr(self, "_MoSesApplication__Formulas"):
348 return self.__Formulas
349 if self.Conn is None:
350 return None
351 dt = self.SelectQuery("SELECT fproduct, form_name, fml_desc, formula, dstamp, tstamp, ustamp, "
352 "extfunc, cat_id, fmlid, tokens FROM fml ORDER BY fproduct, form_name")
353 if dt is None:
354 return None
355 self.__Formulas = dt
356 return self.__Formulas
357
358def DataTable(TableName, Conn):
359 Query = "SELECT * FROM " + TableName
360 dt = pd.read_sql_query(Query, Conn)
361 return dt
362
363
364
365def WildcardPaths(app):
366 dtPthDesc = app.Table("pthdesc")
367 dtPthname = app.Table("pthname")
368 dtProdPar = app.Table("prodpar")
369 dt = pd.merge(left=dtPthname, right=dtPthDesc, on="pathkey", how="left", suffixes=('', '_y'))
370 dt = pd.merge(left=dt, right=dtProdPar, left_on="wildcrdset", right_on="pardesc", how="inner", suffixes=('', '_y'))
371 cols = [c for c in dt.columns if not c.endswith("_y")]
372 dt = dt[cols]
373 dt['wildcardkey'] = "<*" + dt['pathkey'].str.strip() + "*>"
374 cols = ['pathkey', 'pathdir', 'wildcrdset', 'pathdesc', 'pardesc', 'prodparid', 'wildcardkey']
375 dt = dt[cols]
376 return dt
377
378
379
380
381
382def ReplaceWildcards(xmlstr, wcdt):
383 xmlstr = xmlstr.replace("<", "<")
384 xmlstr = xmlstr.replace(">", ">")
385
386 for wc in range(len(wcdt)):
387 pathkey = wcdt.loc[wc, "pathkey"].strip()
388 findString = r"<*" + pathkey + r"*>"
389 replaceString = wcdt.loc[wc, "pathdir"].strip()
390
391 if replaceString != "N/A":
392 xmlstr = xmlstr.replace(findString, replaceString)
393 xmlstr = xmlstr.replace("<![CDATA[", "")
394 xmlstr = xmlstr.replace("]]>", "")
395
396 return xmlstr
397
398path_moses_app = r"U:\Users\Sam\Modernization\Data\moses\moses_apps\20180102_QE_2017Q4_FL_IUL_001"
399App = MoSesApplication(Path=path_moses_app)
400
401dtProdpar = App.Table("prodpar")
402dtProdasx = App.Table('prodasx')
403dtProdlst = App.Table("prodlst")
404dtPthname = App.Table("pthname")
405dtPthdesc = App.Table("pthdesc")
406
407dtProdval = App.SelectQuery("SELECT v_value, mdlid, varid FROM prodval")
408dtProdlst = App.SelectQuery(QueryText="SELECT chgd.product, prodlst.v_choice FROM prodlst JOIN chgd ON prodlst.mdlid = chgd.mdlid ORDER BY chgd.product")
409dtWildcardPaths = WildcardPaths(App)
410
411row = 55
412XmlString = dtProdval.loc[row, "v_value"]
413XmlString = ReplaceWildcards(xmlstr=XmlString, wcdt=dtWildcardPaths)
414ValueXml = Soup(XmlString, "lxml")
415ValueNodeList = ValueXml.findAll("row")
416VariableValues = []
417j = 0
418while j < len(ValueNodeList):
419 d = {}
420 for ValSubnode in ValueNodeList[j].findChildren():
421
422 if ValSubnode.name == "prodcode":
423 ProdCode = ValSubnode.text
424 d['prodcode'] = ProdCode
425 if ValSubnode.name == "model":
426 ModelID = ValSubnode.text
427 d['model'] = ModelID
428 if ValSubnode.name == "variable":
429 Variable = ValSubnode.text
430 d['variable'] = Variable
431 if ValSubnode.name == "v_value":
432 # Value = ValSubnode.contents(strip=True)
433 Value = ValSubnode.next_element
434 d['value'] = Value
435 print(d)
436 VariableValues.append(d)
437 j += 1
438
439
440def ParseXmlValue(xmlstring):
441 xmlstring = ReplaceWildcards(xmlstr=xmlstring, wcdt=dtWildcardPaths)
442 ValueXml = Soup(xmlstring, "lxml")
443 ValueNodeList = ValueXml.findAll("row")
444 VariableValues = []
445 j = 0
446 while j < len(ValueNodeList):
447 d = {}
448 for ValSubnode in ValueNodeList[j].findChildren():
449
450 if ValSubnode.name == "prodcode":
451 ProdCode = ValSubnode.text
452 d['prodcode'] = ProdCode
453 if ValSubnode.name == "model":
454 ModelID = ValSubnode.text
455 d['model'] = ModelID
456 if ValSubnode.name == "variable":
457 Variable = ValSubnode.text
458 d['variable'] = Variable
459 if ValSubnode.name == "v_value":
460 # Value = ValSubnode.contents(strip=True)
461 Value = ValSubnode.next_element
462 d['value'] = Value
463 VariableValues.append(d)
464 j += 1
465 cols = ['model', 'prodcode', 'variable', 'value']
466 df = pd.DataFrame(VariableValues)
467 df = df[cols]
468 return df
469
470
471def MoSesProductFeatureSet(app):
472 prodval = app.SelectQuery("SELECT v_value, mdlid, varid, prodparid FROM prodval where prodparid = " + str(app.ID.get("prodparid")))
473 for row in range(len(prodval)):
474 print("Parsing Row {} of {}".format(row + 1, len(prodval)))
475 DataRow = prodval.loc[row, :]
476 XmlString = prodval.loc[row, "v_value"]
477 Data = ParseXmlValue(XmlString)
478 Data['varid'] = DataRow['varid']
479 Data['mdlid'] = DataRow['mdlid']
480 Data['prodparid'] = DataRow['prodparid']
481 if row == 0:
482 masterData = Data
483 else:
484 frames = [masterData, Data]
485 masterData = pd.concat(frames)
486 masterData = masterData.reset_index(drop=True).sort_values(by=['variable']).reset_index(drop=True)
487 return masterData
488
489
490MoSesProductFeatureSet = MoSesProductFeatureSet(App)
491MoSesProductFeatureSet
492
493
494def MoESesAssumptionSet(app):
495 ASdt = App.SelectQuery("SELECT * FROM val WHERE prodasxid = " + str(App.ID.get("prodasxid")))
496 return ASdt
497
498
499
500
501
502def main():
503 path_moses_app = r"U:\Users\Sam\Modernization\Data\moses\moses_apps\20180102_QE_2017Q4_FL_IUL_001"
504
505 filepaths = [os.path.join(path_moses_app, f) for f
506 in os.listdir(path_moses_app)
507 if f.lower().endswith(".dbf")]
508
509 for filepath in filepaths:
510 convertDBFtoCSV(input_file_path=filepath, outdir=r"C:\Temp\moses2axis\database\csv")
511
512 dbName = "MoSesData.db"
513 csvDirectory = r"c:\temp\moses2axis\database\csv"
514 BatchCSVtoSQLite(db=dbName, csvDir=csvDirectory)
515
516
517if __name__=='__main__':
518 main()