def getByCpf(self, cpf):
con = DatabaseConnection().get()
cur = con.cursor()
cur.execute('select "Id", "CustomerId", "Title", "Type", "AcquisitionDate", "EstimatedValue", "PayValue", "RemainingValue", "Status" from "Assets" where exists(select 1 from "Customer" c where c."Id" = "Assets"."CustomerId" and c."Cpf"=\'{0}\')'.format(cpf))
recset = cur.fetchall()
results = self.mapToDto(recset)
con.close()
return results
def getById(self, id):
con = DatabaseConnection().get()
cur = con.cursor()
cur.execute('select "Id", "CustomerId", "Title", "Type", "AcquisitionDate", "EstimatedValue", "PayValue", "RemainingValue", "Status" from "Assets" where "Id" = {0}'.format(id))
recset = cur.fetchall()
results = self.mapToDto(recset)
con.close()
return results
def getAll(self):
con = DatabaseConnection().get()
cur = con.cursor()
cur.execute(
'select "Id", "CustomerId", "Fixed", "Value" from "Income"')
recset = cur.fetchall()
results = self.mapToDto(recset)
con.close()
return results
def getByCpf(self, cpf):
con = DatabaseConnection().get()
cur = con.cursor()
cur.execute(
'select "Id", "CustomerId", "Fixed", "Value" from "Income" where exists(select 1 from "Customer" c where c."Id" = "Income"."CustomerId" and c."Cpf"=\'{0}\')'
.format(cpf))
recset = cur.fetchall()
results = self.mapToDto(recset)
con.close()
return results
def getById(self, id):
con = DatabaseConnection().get()
cur = con.cursor()
cur.execute(
'select "Id", "CustomerId", "Fixed", "Value" from "Income" where "Id" = {0}'
.format(id))
recset = cur.fetchall()
results = self.mapToDto(recset)
con.close()
return results
def insert(self, reg):
con = DatabaseConnection().get()
cur = con.cursor()
command = 'INSERT INTO public."Income" ("CustomerId", "Fixed", "Value") VALUES({0}, {1}, {2}) RETURNING "Id"'
sql = command.format(reg.customerId, reg.fixed, reg.value)
print(sql)
cur.execute(sql)
reg.id = cur.fetchone()[0]
con.commit()
con.close()
return reg
def update(self, id, reg):
con = DatabaseConnection().get()
cur = con.cursor()
command = 'UPDATE public."Income" SET "CustomerId"={0}, "Fixed"={1}, "Value"={2} WHERE "Id"={3}'
sql = command.format(reg.customerId, reg.fixed, reg.value, id)
print(sql)
cur.execute(sql)
reg.id = id
con.commit()
con.close()
return reg
def update(self, id, reg):
con = DatabaseConnection().get()
cur = con.cursor()
command = 'UPDATE public."Assets" SET "CustomerId"={0}, "Title"=\'{1}\', "Type"={2}, "AcquisitionDate"=\'{3}\', "EstimatedValue"={4}, "PayValue"={5}, "RemainingValue"={6} WHERE "Id"={7}'
sql = command.format(reg.customerId, reg.title, reg.type, reg.acquisitionDate, reg.estimatedValue, reg.payValue, reg.remainingValue, id)
print(sql)
cur.execute(sql)
reg.id = id
con.commit()
con.close()
return reg
def insert(self, reg):
con = DatabaseConnection().get()
cur = con.cursor()
command = 'INSERT INTO public."Assets"("CustomerId", "Title", "Type", "AcquisitionDate", "EstimatedValue", "PayValue", "RemainingValue") VALUES({0}, \'{1}\', {2}, \'{3}\', {4}, {5}, {6}) RETURNING "Id"'
sql = command.format(reg.customerId, reg.title, reg.type, reg.acquisitionDate, reg.estimatedValue, reg.payValue, reg.remainingValue)
print(sql)
cur.execute(sql)
reg.id = cur.fetchone()[0]
con.commit()
con.close()
return reg
class SQLCommand:
"""
Classe de execução de scripts no banco de dados
"""
conn = None
def __init__(self):
self.db = DatabaseConnection()
self.log = Logger(__name__).log_config()
def exec_query(self, query, params=None, x_return=False):
"""
Executa as Query's
:param query: Recebe a query a que será executada
:param params: (Opcional) recebe os parâmetros da query caso houver
:param x_return: (Opcional) passar como True se query tiver retorno
:return: result
"""
# Abre conexão com banco de dados
try:
cur = self.conn.cursor(dictionary=True)
if x_return is False:
if self.check_param(params):
cur.execute(query)
self.log.debug("Command: %s", cur.statement)
else:
cur.execute(query, params)
self.log.debug("Command: %s", cur.statement)
self.conn.commit()
else:
if self.check_param(params):
cur.execute(query)
self.log.debug("Command: %s", cur.statement)
else:
cur.execute(query, params)
self.log.debug("Command: %s", cur.statement)
result = cur.fetchall()
# Se o resultado do select for > 1 retorna uma lista de objeto, se não retorna apenas o objeto
if cur.rowcount > 1:
return result
else:
return result[0]
cur.close()
except Exception as err:
self.log.exception(err)
def open(self):
"""
Abre a conexão com banco de dados
:return: void
"""
self.conn = self.db.conn_open()
def close(self):
"""
Fecha a conexão com banco de dados
:return:
"""
self.conn.close()
@staticmethod
def check_param(params):
"""
:param params: Verifica se algum parâmetro foi informado para ser repassado na execução da query
:return: True or False
"""
if params is None:
return True
else:
return False
def __init__(self):
self.db = DatabaseConnection()
self.log = Logger(__name__).log_config()