def run(self): link = connectDB.ConnectDB(self.dbfile, self.logText) conn = link.get_conn() db_final = iLoadTools.DatabaseTool(conn) configName = db_final.getConfigName() credential = db_final.getCredential() tarList = db_final.getTarFieldList() tarTable = db_final.getTargetTable() loadType = db_final.getLoadType() keys = db_final.getKey() self.logText.AppendText("execute importing data") self.logText.AppendText(tarTable) self.logText.AppendText(str(keys)) data = db_final.getViewData(tarList, configName + "_tar_vw2_tab") try: tarClass = iLoadTools.getLoadTypeClass(loadType) tt = tarClass(credential, self.logText) except Exception as e: self.logText.AppendText(e.args[0]) tar_conn = tt.getConnection() if tar_conn: if keys["haskey"] == "one key": self.logText.AppendText("one key") tt.upsertData(data, tarTable, tarList, credential, keys) elif keys["haskey"] == "no key": self.logText.AppendText("no key") tt.insertData(data, tarTable, tarList, credential) else: self.logText.AppendText("You can only set up one primary key.") link.close_conn()
def __init__(self, conn): self.conn = conn dbt = iLoadTools.DatabaseTool(conn) self.keys = dbt.getKey() self.variables = dbt.getVariable() self.configName = dbt.getConfigName() self.sourceTable = dbt.getSourceTable()
def loading(self): SYMBOL = "`~!@#$%^&*()_+-=[]\{}|;':,./<>?" pdb.set_trace() dt = iLoadTools.DatabaseTool(self.conn) csvfile = [] filename = dt.getSourceFileName() filepath = mainFrame.workspace + "\\" + self.name + "\\" + filename csvfile.append(filepath) for cf in csvfile: reader = csv.reader(file(cf, "rb")) tablename = os.path.basename(cf)[0:-4] nli = [] insqllist = [] createsql = "" for line in reader: if reader.line_num == 1: for str in line: str = str.replace(" ", "") for s in SYMBOL: str = str.replace(s, "_") restr = str nli.append(restr) collist = "" for l in nli: collist = collist + l + " text, " collist = collist[0:-2] createsql = "create table %s ( %s )" % (tablename, collist) elif len(line) == 0: self.log.AppendText("process stop at number %d \n" % (reader.line_num, )) break else: insertSql = "insert into %s values(" % tablename for col in line: insertSql = insertSql + "'" + col + "', " insertSql = insertSql[0:-2] + ")" insqllist.append(insertSql) try: self.conn.execute("drop table " + tablename) except sqlite3.Error: self.log.AppendText("No this table error.\n") self.conn.commit() #print(createsql) self.conn.execute(createsql) self.conn.commit() for index, il in enumerate(insqllist): self.conn.execute(il) if index == 100: self.conn.commit() self.conn.commit() self.log.AppendText("Insert source data successfully\n")
def loading(self): dt = iLoadTools.DatabaseTool(self.conn) csvfile = [] filename = dt.getSourceFileName() filepath = iLoad.workspace + "\\" + self.name + "\\" + filename csvfile.append(filepath) for cf in csvfile: fo = codecs.open(cf, "rb", "utf-8") reader = csv.reader(fo) tablename = os.path.basename(cf)[0:-4] nli = [] insqllist = [] createsql = "" for line in reader: line = map(lambda x: x.replace('\xef\xbb\xbf', ''), line) if reader.line_num == 1: for str in line: str = str.replace(" ", "") str = iLoadTools.charReplace(str) restr = str nli.append(restr) collist = "" for l in nli: collist = collist + l + " text, " collist = collist[0:-2] createsql = "create table %s ( %s )" % (tablename, collist) elif len(line) == 0: self.log.AppendText("process stop at number %d \n" % (reader.line_num, )) break else: insertSql = "insert into %s values(" % tablename for col in line: insertSql = insertSql + "'" + col + "', " insertSql = insertSql[0:-2] + ")" insqllist.append(insertSql) try: self.conn.execute("drop table " + tablename) except sqlite3.Error: self.log.AppendText("No this table error.\n") self.conn.commit() self.conn.execute(createsql) self.conn.commit() for index, il in enumerate(insqllist): self.conn.execute(il) if index == 100: self.conn.commit() self.conn.commit() self.log.AppendText("Insert source data successfully\n")
def loading(self): dt = iLoadTools.DatabaseTool(self.conn) credential = dt.getCredential() sql = "select * from rule_reference where rule_type = 'lookup'" cursor = self.conn.execute(sql) li = [] for r in cursor: li.append(r) if len(li) != 0: for row in li: if row[8] == '': wherec = "" else: wherec = " where %s" % (row[8], ) soql = "select %s from %s" % (row[6], row[4]) soql = soql + wherec try: tarClass = iLoadTools.getLoadTypeClass(dt.getLoadType()) tt = tarClass(dt.getCredential(), self.log) except Exception as e: self.log.AppendText(str(e)) self.log.AppendText("\n") tar_conn = tt.getConnection() if tar_conn: try: query_result = tar_conn.query(soql) fields = row[6].split(",") length = len(fields) create_sql = "create table %s (%s)" % \ (row[4]+"_"+row[2], reduce(lambda x,y:x+", "+y, map(lambda x:x+" text", fields)), ) self.createTab(row[4] + "_" + row[2], create_sql) st = "" for i in fields: st = st + "'%s'," presql = "insert into %s (%s) values(%s)" % \ (row[4]+"_"+row[2], reduce(lambda x,y:x+","+y, fields), st[:-1]) if query_result["size"] > 0: while True: for row in query_result['records']: newDict = {} for k in row.keys(): if k != 'type': newDict[ k.lower()] = row[k].replace( "'", "''") li = [] li = map(lambda x: newDict[x.lower()], fields) isql = "" try: isql = presql % tuple(li) except UnicodeError as e: self.log.AppendText(str(e)) self.log.AppendText("\n") self.conn.execute(isql) if query_result['done'] is True: break query_result = tar_conn.queryMore( query_result['queryLocator']) else: self.log.AppendText( "There is not any data returned from lookup object\n" ) self.conn.commit() del (tar_conn) except Exception as e: self.log.AppendText("\n") self.log.AppendText(str(e)) self.log.AppendText("\n") else: self.log.AppendText( "cannot get target system connection\n") self.link.close_conn()
def loadJob(self, job): dbfile = iLoad.workspace + "\\" + job + "\\" + job + ".zdb" vlog = VirtualLog(self.logger) lookTab = loadingLookupTable.LookupTable(dbfile, vlog) lookTab.loading() link = connectDB.ConnectDB(dbfile, vlog) conn = link.get_conn() cv = createView.CreateView(conn) dt = cv.getViewFieldList() allLi1 = cv.jointFields1(dt) sql1 = cv.createViewSQL1(dt, allLi1) dbt = iLoadTools.DatabaseTool(conn) configName = dbt.getConfigName() try: conn.execute("drop view %s" % (configName + "_tar_vw1", )) except sqlite3.OperationalError as e: self.logger.info(e.args[0]) conn.execute(sql1) # create view 1 self.logger.info("create view 1") allLi2 = cv.jointFields2(dt) sql2 = cv.createViewSQL2(dt, allLi2) try: conn.execute("drop view %s" % (configName + "_tar_vw2", )) except sqlite3.OperationalError as e: self.logger.info(e.args[0]) conn.execute(sql2) # create view 2 conn.commit() self.logger.info("create view 2") try: conn.execute("drop table " + configName + "_tar_vw2_tab") except sqlite3.OperationalError as e: self.logger.info(e.args[0]) else: self.logger.info("Table %s droped successfully" % \ (configName+"_tar_vw2_tab", )) finalSQL = "create table %s as select * from %s" % \ (configName+"_tar_vw2_tab", configName+"_tar_vw2", ) conn.execute(finalSQL) conn.commit() self.logger.info("final table created") time.sleep(0.03) self.logger.info("load data to target starting") credential = dbt.getCredential() tarList = dbt.getTarFieldList() tarTable = dbt.getTargetTable() loadType = dbt.getLoadType() keys = dbt.getKey() self.logger.info("execute importing data") self.logger.info(tarTable) self.logger.info(str(keys)) data = dbt.getViewData(tarList, configName + "_tar_vw2_tab") try: tarClass = iLoadTools.getLoadTypeClass(loadType) tt = tarClass(credential, vlog) except Exception as e: self.logger.info(e.args[0]) tar_conn = tt.getConnection() if tar_conn: if keys["haskey"] == "one key": self.logger.info("one key") tt.upsertData(data, tarTable, tarList, credential, keys) elif keys["haskey"] == "no key": self.logger.info("no key") tt.insertData(data, tarTable, tarList, credential) else: self.logger.info("You can only set up one primary key.") link.close_conn() self.logger.info("job " + job + " has been finished")