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")