def set_blank_user_record(self, user):
db = Database()
sql = """
INSERT INTO economy_users (reddit_id)
VALUES (?)
"""
db.connection.cursor().execute(sql, (user.id, ))
db.connection.commit()
db.close()
def store_changes(self, user, funds_delta=0, items_delta=0):
db = Database()
sql = """
UPDATE economy_users
SET items_available = items_available + ?, funds_available = funds_available + ?
WHERE economy_users.reddit_id = ?
"""
db.connection.cursor().execute(sql,
(items_delta, funds_delta, user.id))
db.connection.commit()
db.close()
def set_database_schema(self):
db = Database()
sql = """
CREATE TABLE IF NOT EXISTS economy_users (
id integer PRIMARY KEY,
reddit_id text NOT NULL UNIQUE,
items_available integer NOT NULL DEFAULT 0,
funds_available integer NOT NULL DEFAULT 0)
"""
db.connection.cursor().execute(sql).fetchone()
db.close()
def retrieve_user_inventory(self, user):
db = Database()
sql = 'SELECT * FROM economy_users WHERE economy_users.reddit_id = ?'
user_record = db.connection.cursor().execute(sql,
(user.id, )).fetchone()
if user_record == None:
self.set_blank_user_record(user)
user_record = db.connection.cursor().execute(
sql, (user.id, )).fetchone()
db.close()
return user_record
def show(self):
print('\nCargando... Por favor espere')
db = Database()
backup = File('backup')
# Opciones CON base de datos
if db.connect():
os.system('cls')
print('>>> EL AHORCADO (x.x) <<< - ' + self.nickname)
db.updateData()
backup = File('backup')
if backup.count():
print('1. Jugar\n' + '2. Ver puntuaciones\n' +
'3. Agregar palabra (Hay ' + str(backup.count()) +
')\n' + '4. Revisar palabras (-.-)\n' +
'5. Salir\n\nElige una opción: ')
option = input().lower()
if option == '1':
game = Game('online')
while not game.isOver():
os.system('cls')
print(
'PALABRAS\t Total: ' + game.getWordTotal() +
' (Punt. máx.) \tRestante: ' +
game.getWordCount() +
'\n-----------------------------------------------------\n'
+ '\tNo. de intentos: ' + game.getAtt() +
'\tPuntaje: ' + game.getScore() +
'\n-----------------------------------------------------'
+ game.getHanged() + '\n' + game.getWordPrint())
print('\n' + game.play(
input(
"\nEscribe una letra ('end' para salir/terminar): "
).upper()))
input()
os.system('cls')
print("---------------------" + "| FIN DE LA PARTIDA |" +
"---------------------\n" + 'Puntaje: ' +
game.getScore())
if int(game.getScore()) > 0:
if db.connect():
db.regScore(self.nickname, game.getScore())
print("Partida registrada con exito")
else:
localScore = File('score').add(self.nickname +
'|' +
game.getScore())
print(
"Partida completada, intenta conectarte al servidor para que sea registrada"
)
input()
if option == '2':
os.system('cls')
print('---> Puntuaciones <---\n')
score = File('score').getWords()
print(
"JUGADOR \tPUNTAJE MÁX. \tPUNTAJE TOTAL \tPARTIDAS JUGADAS\n"
)
for row in score:
row = row.split('|')
print(row[0] + '\t\t' + str(row[1]) + '\t\t' +
str(row[2]) + '\t\t' + str(row[3]))
input()
elif option == '3':
word = input(
'\nEscribe la palabra a ingresar ("c" para cancelar):\n'
).upper()
if word != 'C':
db.insertWord(word)
elif option == '4':
os.system('cls')
print('Palabras guardadas (¡Esponja enloqueciste!)\n')
for word in backup.getWords():
print(word)
input()
elif option == '5':
backup.close()
db.close()
exit()
else:
print('1. Agregar palabras para jugar\n' +
'2. Salir\n\nElige una opción: ')
option = input().lower()
if option == '1':
word = input(
'\nEscribe la palabra a ingresar ("c" para cancelar):\n'
).upper()
if word != 'C':
db.insertWord(word)
elif option == '2':
db.close()
backup.close()
exit()
# Opciones SIN base de datos
else:
os.system('cls')
print(
'>>> EL AHORCADO (x.x) <<< - ' + self.nickname + '\n' +
'No hay conexión al servidor\n' +
'Tus partidas y palabras añadidas se guardarán cuando haya conexión\n'
)
backup = File('backup')
if backup.count():
print('1. Jugar\n' + '2. Ver palabras disponibles\n' +
'3. Conectarse al servidor\n' + '4. Ver puntuaciones\n' +
'5. Añadir palabras\n' +
'6. Salir\n\nElige una opción: ')
option = input().lower()
if option == '1':
game = Game('offline')
while not game.isOver():
os.system('cls')
print(
'PALABRAS\t Total: ' + game.getWordTotal() +
' (Punt. máx.) \tRestante: ' +
game.getWordCount() +
'\n-----------------------------------------------------\n'
+ '\tNo. de intentos: ' + game.getAtt() +
'\tPuntaje: ' + game.getScore() +
'\n-----------------------------------------------------'
+ game.getHanged() + '\n' + game.getWordPrint())
print('\n' + game.play(
input(
"\nEscribe una letra ('end' para salir/terminar): "
).upper()))
input()
os.system('cls')
print("---------------------" + "| FIN DE LA PARTIDA |" +
"---------------------\n" + 'Puntaje: ' +
game.getScore())
if int(game.getScore()) > 0:
if db.connect():
db.regScore(self.nickname, game.getScore())
print("Partida registrada con exito")
else:
localScore = File('unsaved_score').add(
self.nickname + '|' + game.getScore())
print(
"Partida completada, intenta conectarte al servidor para que sea registrada"
)
input()
if option == '2':
os.system('cls')
print('Palabras guardadas (¡Esponja enloqueciste!)\n')
for word in backup.getWords():
print(word)
input()
elif option == '4':
os.system('cls')
print('---> Puntuaciones <---\n')
score = File('score').getWords()
print(
"JUGADOR \tPUNTAJE MÁX. \tPUNTAJE TOTAL \tPARTIDAS JUGADAS\n"
)
for row in score:
row = row.split('|')
print(row[0] + '\t\t' + str(row[1]) + '\t\t' +
str(row[2]) + '\t\t' + str(row[3]))
unsavedScore = File('unsaved_score').getWords()
if len(unsavedScore):
print("\nPartidas por registrar:")
for row in unsavedScore:
row = row.split('|')
print(row[0] + '\t' + str(row[1]))
input()
elif option == '5':
word = ''
while word.lower() != 'n':
word = str(
input(
'\nEscribe la palabra a ingresar ("n" para terminar): '
))
if word.lower() != 'n':
backup.add(word)
elif option == '6':
backup.close()
exit()
else:
print('1. Agregar palabras para jugar\n' +
'2. Salir\n\nElige una opción: ')
option = input().lower()
if option == '1':
word = ''
while word.lower() != 'n':
word = str(
input(
'\nEscribe la palabra a ingresar ("n" para terminar): '
))
if word.lower() != 'n':
backup.add(word)
elif option == '2':
backup.close()
exit()
backup.close()
self.show()
class BaseHandler(tornado.web.RequestHandler):
def initialize(self):
self.db = Database()
def token_new(self, username):
user_data = self.db.select_one('users', dict(username=username))
#Check if user already has token
token = self.db.select_one('tokens', dict(users_id=user_data['_id']))
if token:
self.write(dict(token = token['token']))
else:
token = base64.b64encode(OpenSSL.rand.bytes(16))
self.db.insert('tokens', dict(users_id=user_data['_id'],
token=token,
created=datetime.datetime.utcnow()))
self.write(dict(token = token))
def token_check(self, token):
self.db = Database()
token_data = self.db.select_one('tokens', dict(token=token))
if token_data:
return ObjectId(token_data['users_id'])
raise tornado.web.HTTPError(403)
def pw_encrpyt(self, password):
return hashpw(password, gensalt())
def friend_check(self, user_id, friend_id):
friend_data = self.db.select_one('friends', dict(user_id = user_id,
friend_id = friend_id))
user_data = self.db.select_one('friends', dict(user_id = friend_id,
friend_id = user_id))
if friend_data and user_data:
return True
return False
def race_user_check(self, user_id, race_id):
if self.db.select_one('race_user', dict(user_id = user_id,
race_id = race_id)):
return True
return False
def race_owner_check(self, user_id, race_id):
if self.db.select_one('races', dict(user_id = user_id,
_id = race_id)):
return True
return False
def race_public_check(self, race_id):
if self.db.select_one('races', dict(private = False,
_id = race_id)):
return True
return False
def on_finish(self):
self.db.close()
class DML:
def __init__(self, db=None):
if db:
self.conn = db
else:
self.conn = Database().create_connection()
self.query_comanda_exists = """
SELECT * FROM Comandas
WHERE cliente_id = {}
AND fim IS NULL
AND data_comanda = '{}';
"""
def destroy_me(self):
try:
self.conn.close()
except Exception as err:
logging.critical(err, type(err))
##############################################
# Clientes #
##############################################
def insert_client(self, cliente: Cliente):
self.conn.execute(
"INSERT INTO Clientes (nome, cpf, telefone) VALUES (?, ?, ?)",
(cliente.nome, cliente.cpf, cliente.telefone))
self.conn.commit()
def delete_client(self, _id: int):
self.conn.execute("DELETE FROM Clientes WHERE ID = ?", str(_id))
self.conn.commit()
def edit_client(self, set_query, where):
self.conn.execute(f"UPDATE Clientes SET {set_query} WHERE {where}")
self.conn.commit()
def find_client(self, where):
execute = self.conn.execute(f"SELECT * FROM Clientes WHERE {where}")
fetch = execute.fetchone()
return {k[0]: v for k, v in list(zip(execute.description, fetch))}
def find_all_clientes(self):
try:
execute = self.conn.execute(
"SELECT ID, nome, telefone FROM Clientes;")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
##############################################
# Comandas #
##############################################
def insert_comanda(self, cliente_id: str, inicio: str, data_comanda: str):
verify = self.conn.execute(
self.query_comanda_exists.format(cliente_id, data_comanda))
if not verify.fetchone():
self.conn.execute(
"INSERT INTO Comandas (cliente_id, inicio, data_comanda) VALUES (?, ?, ?)",
(cliente_id, inicio, data_comanda))
self.conn.commit()
def delete_comanda(self, cliente_id: int, data_comanda: str):
self.conn.execute(
f"DELETE FROM Comandas WHERE cliente_id = {str(cliente_id)} AND data_comanda = '{data_comanda}' AND fim IS NULL"
)
self.conn.commit()
def find_active_comanda_by_client_id(self, cliente_id: int):
try:
execute = self.conn.execute(
"SELECT * FROM Comandas WHERE cliente_id = {} AND fim IS NULL".
format(cliente_id))
fetch = execute.fetchone()
return {k[0]: v for k, v in list(zip(execute.description, fetch))}
except Exception as err:
logging.critical(err, type(err))
return {}
def order(self, comanda_id: int):
try:
execute = self.conn.execute(
"""SELECT PedidosComanda.produto_id, produto, quantidade, Produtos.valor, ROUND(quantidade*valor, 2) as total
FROM PedidosComanda
JOIN Produtos ON PedidosComanda.produto_id = Produtos.ID
WHERE comanda_id = {}""".format(comanda_id))
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as err:
logging.critical(err, type(err))
return None
def total_comanda(self, comanda_id):
query = f"""
SELECT SUM(valor * quantidade) AS total_value FROM PedidosComanda
JOIN Produtos on PedidosComanda.produto_id = Produtos.ID
WHERE comanda_id = {comanda_id};
"""
try:
execute = self.conn.execute(query)
fetch = execute.fetchone()
return fetch[0]
except Exception as err:
logging.critical(err, type(err))
return None
def find_active_comandas(self):
try:
execute = self.conn.execute("""
SELECT c.ID, c.cliente_id, cl.nome, c.inicio, c.fim, c.data_comanda
FROM Comandas c
JOIN Clientes cl ON c.cliente_id = cl.ID
WHERE fim IS NULL
ORDER BY inicio ASC;
""")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
for comanda in list_return:
total = self.total_comanda(comanda["ID"])
comanda["total"] = round(total, 2) if total else 0
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def find_finished_comandas(self):
try:
execute = self.conn.execute("""
SELECT c.ID, c.cliente_id, cl.nome, c.inicio, c.fim, c.data_comanda
FROM Comandas c
JOIN Clientes cl ON c.cliente_id = cl.ID
WHERE fim IS NOT NULL
ORDER BY inicio ASC;
""")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
for comanda in list_return:
total = self.total_comanda(comanda["ID"])
comanda["total"] = round(total, 2) if total else 0
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def edit_comanda(self, set_query, where):
query = f"UPDATE Comandas SET {set_query} WHERE {where}"
self.conn.execute(query)
self.conn.commit()
def finish_comanda(self, cliente_id: int, fim: str):
self.conn.execute(
f"UPDATE Comandas SET fim = '{fim}' WHERE cliente_id = {cliente_id} and fim IS NULL"
)
self.conn.commit()
##############################################
# PRODUTOS #
##############################################
def insert_produto(self, produto_values: Produto):
verify = self.conn.execute(
f"SELECT * FROM Produtos WHERE produto = '{produto_values.produto}'; "
)
if not verify.fetchone():
self.conn.execute(
"INSERT INTO Produtos (produto, valor, unidade) VALUES (?, ?, ?);",
(produto_values.produto, produto_values.valor,
produto_values.unidade))
_id = self.conn.execute(
f"SELECT ID FROM Produtos WHERE produto = '{produto_values.produto}'; "
).fetchone()[0]
self.conn.execute(
"INSERT INTO Estoque (produto_id, quantidade) VALUES (?, ?);",
(_id, 0))
self.conn.commit()
def delete_produto(self, produto_id: int):
self.conn.execute(f"DELETE FROM Produtos WHERE ID = {produto_id};")
self.conn.execute(
f"DELETE FROM Estoque WHERE produto_id = {produto_id};")
self.conn.commit()
def find_all_products(self):
try:
execute = self.conn.execute("SELECT * FROM Produtos;")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def find_like_produtos(self, product_name: str):
try:
execute = self.conn.execute(
f"SELECT * FROM Produtos WHERE produto LIKE '%{product_name}%';"
)
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def edit_produto(self, set_query, where):
query = f"UPDATE Produtos SET {set_query} WHERE {where};"
self.conn.execute(query)
self.conn.commit()
##############################################
# PEDIDOSCOMANDA #
##############################################
def select_all_pedido(self, comanda_id):
try:
execute = self.conn.execute(f"""
SELECT * FROM PedidosComanda
JOIN Produtos P on PedidosComanda.produto_id = P.ID
WHERE comanda_id={comanda_id};
""")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def insert_pedido(self, comanda_id: int, produto_id: int, quantidade):
self.conn.execute(
"INSERT INTO PedidosComanda (comanda_id, produto_id, quantidade) VALUES (?, ?, ?)",
(comanda_id, produto_id, quantidade))
self.conn.commit()
def remove_pedido(self, comanda_id: int, produto_id: int):
self.conn.execute(
"DELETE FROM PedidosComanda WHERE comanda_id = {} AND produto_id = {}"
.format(comanda_id, produto_id))
self.conn.commit()
def edit_pedido(self, set_query, where):
query = f"UPDATE PedidosComanda SET {set_query} WHERE {where}"
self.conn.execute(query)
self.conn.commit()
##############################################
# ESTOQUE #
##############################################
def edit_quantity_estoque(self, produto_id: int, new_quantity: int):
self.conn.execute(
f"UPDATE Estoque SET quantidade = {new_quantity} WHERE produto_id = {produto_id};"
)
self.conn.commit()
def view_estoque(self):
try:
execute = self.conn.execute("""
SELECT Estoque.*, P.produto FROM Estoque
JOIN Produtos P on Estoque.produto_id = P.ID
""")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def view_single_product_estoque(self, product_id: int):
execute = self.conn.execute(f"""
SELECT Estoque.*, P.produto FROM Estoque
JOIN Produtos P on Estoque.produto_id = P.ID
WHERE produto_id = {product_id}
""")
fetch = execute.fetchone()
return {k[0]: v for k, v in list(zip(execute.description, fetch))}
"""
engine = create_engine(uri)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
engine.dispose()
def create_database(uri: str) -> None:
engine = create_engine(uri)
Base.metadata.create_all(engine)
engine.dispose()
def get_all(db):
sess = db.Session()
query = sess.query(WikiMap).order_by(WikiMap.title).all()
sess.close()
return query
if __name__ == "__main__":
database = Database(sslmode=False)
# delete_recreate_database(database.DATABASE_URI)
create_sample_entries(database.Session)
results = get_all(database)
print(results)
database.close()
# prod_url = config.get_production_config_locally()
# database = Database(prod_url)