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)