def cadastrar(self,hidrometro) -> None:
insert = "INSERT INTO HIDROMETRO (CHAVE, MODELO, ATIVO) " \
"VALUES (%s,%s,$s);"
conn = ConnectionFactory();
try:
connection = conn.getConnection()
cursor = connection.cursor()
record_to_insert = (hidrometro.getChave(),hidrometro.getModelo(),
hidrometro.getAtivo())
cursor.execute(insert, record_to_insert)
connection.commit()
#count = cursor.rowcount
#print(count, "Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
if (connection):
print("Failed to insert record into mobile table", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
def cadastrar(self, hidrometro) -> None:
#insert = "INSERT INTO REGISTRO (VALOR, DATA, HIDROMETROFK) VALUES {}"
conn = ConnectionFactory()
try:
connection = conn.getConnection()
cursor = connection.cursor()
print("[INFO] Registrando estado do hidrometro...")
print("[INFO] Valor : ", round(hidrometro.registro.valor, 3))
print("[INFO] Data : " + str(hidrometro.registro.data))
print("[INFO] Hidrometro: " + str(hidrometro.id))
# record_to_insert = (round(hidrometro.registro.valor,3),
# str(hidrometro.registro.data),
# hidrometro.id)
#cursor.execute(insert.format(record_to_insert))
cursor.callproc('inserirRegistro', [
round(hidrometro.registro.valor, 3),
str(hidrometro.registro.data), hidrometro.id
])
connection.commit()
existeAlertas = cursor.fetchone()
hidrometro.setExisteAlertas(existeAlertas[0])
except (Exception, psycopg2.Error) as error:
if (connection):
print("SQL Error:", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
def consultar(self,hidrometro) -> None:
select = "SELECT * FROM HIDROMETRO WHERE (ULTIMO_REGISTRO < (NOW() - INTERVAL '40 DAY') OR ULTIMO_REGISTRO IS NULL) AND REMOVIDO = 0 AND ATIVO = 1 ORDER BY ID LIMIT 1"
conn = ConnectionFactory();
try:
connection = conn.getConnection()
cursor = connection.cursor()
print("[INFO] Buscando no banco de dados as informacoes do hidrometro...")
cursor.execute(select)
reg = cursor.fetchall()
for row in reg:
hidrometro.setId(row[0])
hidrometro.setIdentificador(row[1])
hidrometro.setChave(row[2])
hidrometro.setModelo(row[3])
hidrometro.setAtivo(row[5])
print("[INFO] Encontramos o cadastro do hidrometro: ")
print("[INFO] Id: ", row[0])
print("[INFO] descr: ", row[1])
print("[INFO] Chave: ", row[2])
print("[INFO] Modelo: ", row[3])
print("[INFO] Ativo: ", row[5])
except (Exception, psycopg2.Error) as error:
if (connection):
print("Falha ao recuperar registro.", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
def gravaDiario(self, diario):
cur = ConnectionFactory()
conn = cur.get_connection()
conexao = conn.cursor()
conexao.execute(
"INSERT INTO dorj.diarios (numero, tipo, anoromano, ano, datadiario, datagravacao, nomearquivo, identidade) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
(diario.numero, diario.tipo, str(
diario.anoromano), diario.ano, diario.datadiario,
diario.datagravacao, diario.nomearquivo, diario.identidade))
conn.commit()
def create_database(self, database=''):
"""
Try to create new database
assumes that we have privileges to create new one
and try to drop database with the normal user
"""
if database:
name = database
else:
name = self.database
try:
co = cf.create_connector (self.conn)
co.connect(self.username, self.password, self.hostname)
mysqlquery = "DROP DATABASE IF EXISTS " + str(name) + ";\n"
co.execute(mysqlquery)
mysqlquery = "CREATE DATABASE " + str(name) + ";\n"
co.execute(mysqlquery)
co.close()
except StandardError:
sys.stderr.write ("WARNING: unable to create database. User " + self.username + " doesn't have privileges")
self.create_user()
def logarUsuario(self, senha):
try:
con = ConnectionFactory.conectar()
cursor = con.cursor()
#A variavel (senha,) precisa SEMPRE terminar em vírgula
#cursor.execute("SELECT * FROM AUTH_USER WHERE senha = '%s'", (senha,))
#Inner join criado para o postgres devido a sua limitação.
cursor.execute(
"""SELECT b.*
FROM AUTH_USER AS b
INNER JOIN
(
SELECT "senhaPorta", usuario_id_id
FROM users_acesso
AS a
)AS a
ON b.id=a.usuario_id_id
WHERE a."senhaPorta" = '%s'""", (senha, ))
except (Exception, psycopg2.Error) as error:
print("Falha ao obter o registro: {}".format(error))
else:
return cursor.fetchone()
finally:
con.close()
cursor.close()
def usuarioExiste(self, senha, sala):
try:
con = ConnectionFactory.conectar()
cursor = con.cursor()
#Inner join criado para o postgres devido a sua limitação de query.
cursor.execute("""SELECT b.*
FROM AUTH_USER AS b
INNER JOIN
(
SELECT "senhaPorta", usuario_id_id
FROM users_acesso
AS a
)AS a
ON b.id=a.usuario_id_id
WHERE a."senhaPorta" = '{0}'""".format(senha))
usuario = cursor.fetchone()
usuarioTemPermissao = self.checarPermissaoUsuario(usuario[0], sala)
except (Exception, psycopg2.Error) as error:
print("Falha ao obter o registro em logarUsuario(): {0}".format(
error))
else:
if (usuarioTemPermissao):
self.inserirRegistroDeAcesso(usuario, sala)
return True
else:
return False
finally:
con.close()
cursor.close()
def inserirLog(self, usuarioLogin):
try:
con = ConnectionFactory.conectar()
cursor = con.cursor()
cursor.execute(
"INSERT INTO monitoramento_registro (sala_acesso, usuario_id) VALUES ('Laboratorio', '%s')",
(usuarioLogin.usuario_id, ))
except (Exception, psycopg2.Error) as error:
print("Falha ao inserir o registro: {}".format(error))
else:
con.commit()
finally:
con.close()
cursor.close()
def inserirRegistroDeAcesso(self, usuario, sala):
try:
con = ConnectionFactory.conectar()
cursor = con.cursor()
cursor.execute(
"INSERT INTO monitoramento_registro (sala_acesso, usuario_id) VALUES ('{0}', '{1}')"
.format(self.salasDicionario[sala][0], usuario[0]))
except (Exception, psycopg2.Error) as error:
print("Falha ao inserir o registro: {}".format(error))
else:
con.commit()
finally:
con.close()
cursor.close()
def selecionarUsuario(self, usuarioConsulta):
try:
con = ConnectionFactory.conectar()
cursor = con.cursor()
#A variavel (senha,) precisa SEMPRE terminar em vírgula
cursor.execute("SELECT * FROM AUTH_USER WHERE senha = '%s'",
(usuarioConsulta.senha, ))
except (Exception, psycopg2.Error) as error:
print("Falha ao obter o registro: {}".format(error))
else:
#return cursor.fetchall()
return cursor.fetchone()
finally:
con.close()
cursor.close()
def __init__(self, driver):
server = ''
user = ''
pwd = ''
db = ''
conn = ''
# Parse and extract items from driver string
match=re.match('([^:]*)://(.*)/([^\?]*)\?*(.*)', driver)
if match:
gps = match.groups()
# Match at least with connection type
if len(gps) >= 1:
conn = gps[0]
# Match at least with connection + user/pass
if len(gps) >= 2:
aux = gps[1]
if aux.find('@') >= 0:
upwd, server = aux.split('@')
if aux.find(":") >= 0:
user, pwd = upwd.split(':')
else:
if aux.find(':') >= 0:
user, pwd = aux.split(':')
else:
server = aux
# Math with everything
if len(gps) >= 3:
db = gps[2]
self.username = user
self.password = pwd
self.hostname = server
self.database = db
self.conn = conn
# Create connection
self.__connection = None
self.__connection = cf.create_connector (conn)
#try:
self.__connection.connect (self.username, self.password, self.hostname, self.database)
"""
def checarPermissaoUsuario(self, usuario_id, sala):
try:
con = ConnectionFactory.conectar()
cursor = con.cursor()
cursor.execute("""SELECT user_id, group_id
FROM auth_user_groups
WHERE user_id = '{0}' AND group_id = '{1}' """.
format(usuario_id, self.salasDicionario[sala][1]))
usuarioComPermissao = cursor.fetchone()
except (Exception, psycopg2.Error) as error:
print("Falha no checarPermissaoUsuario: {0}".format(error))
else:
if (usuarioComPermissao): return True
else: return False
finally:
con.close()
cursor.close()
def inserirUsuario(self, novoUsuario):
try:
con = ConnectionFactory.conectar()
cursor = con.cursor()
#cursor.execute("INSERT INTO livro VALUES (%s, %s, %s, %s) ", (novoUsuario.isbn, novoUsuario.titulo, novoUsuario.autor, novoUsuario.preco))
cursor.execute(
"INSERT INTO Usuario VALUES (%s, %s, %s)",
(novoUsuario.nome, novoUsuario.usuario, novoUsuario.senha))
#bloco de exceção
except (Exception, psycopg2.Error) as error:
print("Falha ao inserir: {}".format(error))
#bloco que será executado caso tudo ocorra bem
else:
con.commit()
#bloco que sempre será executado para fechar a conexão
finally:
con.close()
cursor.close()
def create_user(self, user='', password='', hostname='', database=''):
"""
Create User. Connection needs to have privileges
"""
admin_user = raw_input ("MySQL admin username: "******"MySQL admin password: "******"GRANT ALL ON "+str(self.database)
mysqlquery += ".* TO " + str(self.username) + "@" + str(self.hostname)
mysqlquery += " IDENTIFIED BY \""+str(self.password)+"\";\n"
try:
connaux = cf.create_connector(self.conn)
connaux.connect(admin_user, admin_password, self.hostname)
connaux.execute(mysqlquery)
connaux.close()
self.create_database()
except StandardError:
sys.exit("Error: Cannot create user")
def criarBanco(self):
try:
con = ConnectionFactory.conectar()
cursor = con.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS Usuario (
nome VARCHAR(50) NOT NULL,
usuario VARCHAR(20),
senha int(8)
PRIMARY KEY(usuario, senha)
)""")
"""
Importante:
Não pode ser permitido repetir a senha, podendo ser ela uma chave primária
"""
except (Exception, psycopg2.Error) as error:
print("Falha ao criar o banco: {}".format(error))
else:
con.commit()
finally:
con.close()
cursor.close()
