def gera_query_delete(self, Importa, linha):
campos = Importa.get_campos()
tabela = Importa.get_tabela()
try:
query = "DELETE FROM %s WHERE " % tabela
query += campos[0] + "=%s"
return query
except Exception as e:
self.logger.set_errors(self.error_message % linha)
return None
def gera_query_update(self, Importa, linha):
campos = Importa.get_campos()
tabela = Importa.get_tabela()
try:
wherecampo = list(Importa.get_where().keys())[0]
query = "UPDATE %s SET " % tabela
query += '=%s,'.join(campos) + '=%s'
query += " WHERE " + wherecampo + '=%s'
return query
except Exception as e:
self.logger.set_errors(self.error_message % linha)
return None
def gera_query_insert(self, Importa, linha):
dados = len(Importa.get_dados())
campos = Importa.get_campos()
tabela = Importa.get_tabela()
try:
query = "INSERT INTO %s(" % tabela
query += ','.join(campos)
query += ") VALUES(" + "%s," * (dados - 1) + "%s);"
return query
except Exception as e:
self.logger.set_errors(self.error_message % linha)
return None
def __init__(self, logger):
self.logger = logger
self.conexao = ConexaoFactory().get_conexao()
self.dao = ImportaDao(self.conexao, logger)
self.helper = ImportaHelper(logger)
self.imp = Importa()
class ImportaController:
"""This class contain methods to import, remove and update"""
def __init__(self, logger):
self.logger = logger
self.conexao = ConexaoFactory().get_conexao()
self.dao = ImportaDao(self.conexao, logger)
self.helper = ImportaHelper(logger)
self.imp = Importa()
def importa_csv(self, path, tabela):
"""This method import data from CSV to DB"""
loadbar = Bar('Importando:', max=len(list(open(path, 'r'))) - 1)
with open(path, 'r') as arquivocsv:
for linha, item in enumerate(DictReader(arquivocsv)):
self.imp.set_tabela(tabela)
self.imp.set_campos(list(item.keys()))
self.imp.set_dados(list(item.values()))
query = self.helper.gera_query_insert(self.imp, linha + 2)
if not self.dao.run_query(self.imp, query):
break
if query:
loadbar.next()
loadbar.finish()
return True if not self.logger.get_errors() else False
def remove_csv(self, path, tabela):
"""This method remove data from DB using CSV column in where"""
loadbar = Bar('Removendo:', max=len(list(open(path, 'r'))) - 1)
with open(path, 'r') as arquivocsv:
for linha, item in enumerate(DictReader(arquivocsv)):
self.imp.set_tabela(tabela)
self.imp.set_campos(list(item.keys()))
self.imp.set_dados(list(item.values()))
query = self.helper.gera_query_delete(self.imp, linha + 2)
if not self.dao.run_query(self.imp, query):
break
if query:
loadbar.next()
loadbar.finish()
return True if not self.logger.get_errors() else False
def atualiza_csv(self, path, tabela, where):
"""This method update data from CSV to DB"""
loadbar = Bar('Atualizando:', max=len(list(open(path, 'r'))) - 1)
with open(path, 'r') as arquivocsv:
for linha, item in enumerate(DictReader(arquivocsv)):
self.imp.set_tabela(tabela)
if not where or where not in item:
self.logger.set_errors(
"Cláusula \"Where\" não informada ou inválida!")
break
self.imp.set_where({where: item.pop(where)})
self.imp.set_campos(list(item.keys()))
self.imp.set_dados(list(item.values()))
query = self.helper.gera_query_update(self.imp, linha + 2)
if not self.dao.run_query(self.imp, query):
break
if query:
loadbar.next()
loadbar.finish()
return True if not self.logger.get_errors() else False
def __init__(self, logger):
self.logger = logger
self.conexao = ConexaoFactory().get_conexao()
self.dao = ImportaDao(self.conexao, logger)
self.helper = ImportaHelper(logger)
self.imp = Importa()
class ImportaController:
def __init__(self, logger):
self.logger = logger
self.conexao = ConexaoFactory().get_conexao()
self.dao = ImportaDao(self.conexao, logger)
self.helper = ImportaHelper(logger)
self.imp = Importa()
def importa_csv(self, path, tabela):
bar = Bar('Importando:', max=len(list(open(path, 'r')))-1)
with open(path, 'r') as arquivocsv:
for linha, dict in enumerate(DictReader(arquivocsv)):
self.imp.set_tabela(tabela)
self.imp.set_campos(list(dict.keys()))
self.imp.set_dados(list(dict.values()))
query = self.helper.gera_query_insert(self.imp, linha+2)
if(not self.dao.run_query(self.imp, query)):
break
if query:
bar.next()
bar.finish()
return not self.logger.get_errors() if True else False
def remove_csv(self, path, tabela):
bar = Bar('Removendo:', max=len(list(open(path, 'r')))-1)
with open(path, 'r') as arquivocsv:
for linha, dict in enumerate(DictReader(arquivocsv)):
self.imp.set_tabela(tabela)
self.imp.set_campos(list(dict.keys()))
self.imp.set_dados(list(dict.values()))
query = self.helper.gera_query_delete(self.imp, linha+2)
if(not self.dao.run_query(self.imp, query)):
break
if query:
bar.next()
bar.finish()
return not self.logger.get_errors() if True else False
def atualiza_csv(self, path, tabela, where):
bar = Bar('Atualizando:', max=len(list(open(path, 'r')))-1)
with open(path, 'r') as arquivocsv:
for linha, dict in enumerate(DictReader(arquivocsv)):
self.imp.set_tabela(tabela)
if (not where or where not in dict):
self.logger.set_errors(
"Cláusula \"Where\" não informada ou inválida!")
break
self.imp.set_where({where: dict.pop(where)})
self.imp.set_campos(list(dict.keys()))
self.imp.set_dados(list(dict.values()))
query = self.helper.gera_query_update(self.imp, linha+2)
if(not self.dao.run_query(self.imp, query)):
break
if query:
bar.next()
bar.finish()
return not self.logger.get_errors() if True else False