class DataBase:
def __init__(self,
username,
password,
host='localhost',
dbname='postgres',
port=5432,
pool_size=5):
self.schema = 'soad'
self.username = username
self.host = host
self.port = port
self.dbname = dbname
self.folder = 'Resources' + os.sep + 'database'
os.environ["PGPASSWORD"] = password
self.password = password
self.dbinfo = 'postgres://' + username + ':' + password + '@' + host + ':' + str(
port) + '/' + self.dbname
self.db = DAL(self.dbinfo,
folder=self.folder,
pool_size=pool_size,
migrate=False,
attempts=1)
self.connection = None
self.threadpool = QThreadPool()
def busca_registro(self,
nome_tabela,
coluna,
valor='',
operador='=',
filtro=None):
if filtro == '' or filtro is None:
filtro = '1=1'
sql = "select * from " + self.schema + ".fnc_buscar_registro(" \
+ "p_tabela=>" + "'" + nome_tabela + "'" \
+ ", p_coluna=>" + "'" + coluna + "'" \
+ ", p_valor=>" + "'" + valor + "'" \
+ ", p_operador=>" + "'" + operador + "'" \
+ ", p_filtro=>" + "'" + filtro + "'" \
+ ");"
return self.execute_sql(sql)
def get_registro(self, fnc, campo, valor):
sql = "select * from " + self.schema + "." + fnc + "(" \
+ "p_" + campo + "=>" + "'" + str(valor) + "'" \
+ ");"
return self.execute_sql(sql)
def call_procedure(self, schema='soad', params=None):
if not params:
return
# Remove parametros vazios
vazio = []
for param in params["params"].items():
if param[1] == '':
vazio.append(param[0])
logging.info('[DataBase] Parâmetros vazios: ' + str(vazio))
for i in range(len(vazio)):
del params["params"][vazio[i]]
params = json.dumps(params, ensure_ascii=False)
sql = "select * from " + schema + ".fnc_chamada_de_metodo(" \
+ "p_json_params=>" + "'" + params + "'" \
+ ");"
return self.execute_sql(sql)
def execute_sql(self, sql, as_dict=True):
retorno = list()
try:
retorno = self.db.executesql(query=sql, as_dict=as_dict)
self.db.commit()
logging.debug('[DataBase] status=' + str(True))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] retorno=' + str(retorno))
prc = True, retorno, str(self.db._lastsql)
except Exception as e:
self.db.rollback()
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception=' + str(e))
retorno.append(e)
prc = False, retorno, str(sql)
except:
e = 'Exceção não tratada'
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception2=' + str(e))
retorno.append(e)
prc = False, e, str(sql)
return prc
def __conectar_banco__(self, progress_callback):
try:
self.connection = self.db.__call__()
#progress_callback.emit(100)
except Exception as e:
logging.debug('[DataBase] ' + str(e))
os.environ["PGPASSWORD"] = ''
pass
#progress_callback.emit(0)
return self
def definir_schema(self, schema):
self.schema = schema
self.execute_sql("SET search_path TO " + self.schema, as_dict=False)
def fechar_conexao(self):
self.db.close()
def progress_fn(self, n):
print("%d%% done" % n)
def retorno_conexao(self, s):
self.connection = s
def thread_complete(self):
logging.debug('[DataBase] Thread Completed')
def abrir_conexao(self):
# Pass the function to execute
worker = Worker(
self.db.__call__
) # Any other args, kwargs are passed to the run function
worker.signals.result.connect(self.retorno_conexao)
worker.signals.finished.connect(self.thread_complete)
#worker.signals.progress.connect(self.progress_fn)
# Execute
self.threadpool.start(worker)
class DataBase:
def __init__(self, dbname='postgres', schema='Acervo', pool_size=5):
load_dotenv()
self.dbname = dbname
self.schema = schema
self.username = os.getenv("DBUSERNAME")
self.password = os.getenv("DBPASS")
self.host = os.getenv("DBHOST")
self.port = os.getenv("DBPORT")
self.folder = 'Resources' + os.sep + 'database'
self.dbinfo = \
'postgres://' + str(self.username) + ':' + str(self.password) + '@' \
+ str(self.host) + ':' + str(self.port) + '/' + str(self.dbname)
self.db = DAL(
self.dbinfo,
folder=self.folder,
pool_size=pool_size,
migrate=False,
attempts=1
)
self.connection = None
def execute_sql(self, sql, as_dict=True):
retorno = list()
try:
retorno = self.db.executesql(query=sql, as_dict=as_dict)
self.db.commit()
logging.debug('[DataBase] status=' + str(True))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] retorno=' + str(retorno))
prc = True, retorno, str(self.db._lastsql)
except Exception as e:
self.db.rollback()
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception=' + str(e))
retorno.append(e)
prc = False, retorno, str(sql)
except:
e = 'Exceção não tratada'
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception2=' + str(e))
retorno.append(e)
prc = False, e, str(sql)
return prc
def __conectar_banco__(self):
try:
self.connection = self.db.__call__()
except Exception as e:
logging.debug('[DataBase] ' + str(e))
return self
def definir_schema(self, schema):
self.schema = schema
self.execute_sql("SET search_path TO " + self.schema, as_dict=False)
def fechar_conexao(self):
self.db.close()