def test_db(): db.connect() tables = [ db_models.User, db_models.Message, db_models.Photo, db_models.Cost, db_models.Action ] db.drop_tables(tables) db.create_tables(tables) yield db db.drop_tables(tables) db.close()
def generate() -> None: """ Generates some initial records in database """ from tests.factories import UserFactory, ActionFactory, MessageFactory, CostFactory db.connect() db.create_tables([db_models.User, db_models.Cost, db_models.Action, db_models.Photo, db_models.Message]) # will generate the whole database by chain of subfactories UserFactory.create_batch(size=5) ActionFactory.create_batch(size=5) MessageFactory.create_batch(size=5) CostFactory.create_batch(size=5) click.secho("\nGeneration done!", fg='green') db.close()
def change_password(self, new_password: str) -> str: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" UPDATE users SET password = MD5(%s) WHERE id = {self.id} LIMIT 1; """, new_password) conn.commit() return self.token
def get_by_id(game_id: int) -> GameModel or None: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" SELECT * FROM games WHERE id = {game_id} LIMIT 1; """) result = cursor.fetchone() return None if result is None else GameModel(result)
def get_by_id(user_id: int) -> User or None: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" SELECT * FROM users WHERE id = {user_id} LIMIT 1; """) data = cursor.fetchone() return None if data is None else User(data)
def get_by_login(login: str) -> User or None: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(""" SELECT * FROM users WHERE login = %s LIMIT 1; """, [login]) data = cursor.fetchone() return None if data is None else User(data)
def get_by_token(token: str) -> User or None: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(""" SELECT * FROM users WHERE MD5(CONCAT(LOWER(login), password)) = %s LIMIT 1; """, [token]) data = cursor.fetchone() return None if data is None else User(data)
def get_user_games(user: u.User, count: int = 7) -> list: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" SELECT games.* FROM games JOIN players WHERE players.game = games.id AND players.user = {user.id} ORDER BY games.id DESC LIMIT {count}; """) return [GameModel(data) for data in cursor.fetchall()]
def players(self) -> list: if self.__cached_players is None: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" SELECT game, user FROM players WHERE game = {self.id}; """) data = cursor.fetchall() self.__cached_players = [u.get_by_id(obj[1]) for obj in data] return self.__cached_players
def winner(self) -> u.User or None: if self.__cached_winner is None: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" SELECT game, user FROM winners WHERE game = {self.id} LIMIT 1; """) result = cursor.fetchone() self.__cached_winner = None if result is None else u.get_by_id( result[1]) return self.__cached_winner
def register(login: str, password: str) -> User: try: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(""" INSERT INTO users (login, password) VALUES (%s, MD5(%s)); """, (login, password)) conn.commit() return get_by_login(login) except pymysql.IntegrityError: raise UserError("Пользователь с данным логином уже существует")
def edit_settings(self, about: str or None = None ): self.about = self.about if about is None else about.strip() with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" UPDATE users SET about = %s WHERE id = {self.id} LIMIT 1; """, [self.about]) conn.commit()
def add_player(self, user: u.User): if get_active_game(user) is not None: raise GameError('Вы уже участвуете в другой игре') if self.__status != g.NOT_STARTED: raise GameError('Игра уже окончена') with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" INSERT INTO players (game, user) VALUES ({self.id}, {user.id}); """) conn.commit()
def get_active_game(user: u.User) -> GameModel or None: if user.cached_game is not None: return user.cached_game with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" SELECT game FROM players WHERE user = {user.id} ORDER BY id DESC LIMIT 1; """) result = cursor.fetchone() if result is None: return None game = get_by_id(result[0]) if game is None: return None user.cached_game = game if game.can_play else None return user.cached_game
def get_ended_games(time_now: int, mark_as_handling: bool = True) -> list: with db.connect() as conn: with conn.cursor() as cursor: cursor.execute(f""" SELECT * FROM games WHERE status = {g.NOT_STARTED} AND end_time <= {time_now}; """) result = [GameModel(data) for data in cursor.fetchall()] if mark_as_handling: with conn.cursor() as cursor: cursor.execute(f""" UPDATE games SET status = {g.HANDLING} WHERE status = {g.NOT_STARTED} AND end_time <= {time_now}; """) conn.commit() return result
def end(self, best_player: u.User): with db.connect() as conn: with conn.cursor() as cursor: cursor.execute(f""" INSERT INTO winners (game, user) VALUES ({self.id}, {best_player.id}); """) conn.commit() players = self.players for player in players: player.end_game(best_player.id == player.id) with conn.cursor() as cursor: cursor.execute(f""" UPDATE games SET status = {g.HANDLED} WHERE id = {self.id} LIMIT 1; """) conn.commit()
def create(owner: u.User, add_self: bool, name: str, period: int, start_time: int, private: bool, mode: int, view_distance: int, **kwargs) -> GameModel: end_time = start_time + period * 60 settings = kwargs settings['view_distance'] = view_distance serialized_settings = json.JSONEncoder().encode(settings) with db.connect() as conn: with conn.cursor() as cursor: cursor.execute( f""" INSERT INTO games (name, start_time, end_time, period, owner, private, mode, settings) VALUES (%s, {start_time}, {end_time}, {period}, {owner.id}, {private}, {mode}, %s) """, [name, serialized_settings]) conn.commit() with conn.cursor() as cursor: cursor.execute(f""" SELECT * FROM games WHERE name = %s AND start_time = {start_time} AND owner = {owner.id} ORDER BY id DESC LIMIT 1; """) game_data = cursor.fetchone() if game_data is None: raise GameError( 'Произошла неизвестная ошибка при создании и игры') game = GameModel(game_data) if add_self: game.add_player(owner) return game
def create_db(): db.connect() db.create_tables([db_models.User, db_models.Cost, db_models.Action, db_models.Photo, db_models.Message]) db.close() click.secho("\nCreate DB tables!", fg='green')
def close(self): with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" UPDATE games SET status = {g.CANCELLED_BY_OWNER} WHERE id = {self.id} LIMIT 1; """) conn.commit()
def remove(self): with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" DELETE FROM games WHERE id = {self.id} LIMIT 1; """) conn.commit()
def handled_with_errors(self): with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" UPDATE games SET status = {g.ENDED_WITH_ERRORS} WHERE id = {self.id} LIMIT 1; """) conn.commit()
def end_game(self, winner: bool): with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" UPDATE users SET played = played + 1, wins = wins + {int(winner)} WHERE id = {self.id} LIMIT 1; """) conn.commit()
def get_games(count: int = 7, status: int = g.NOT_STARTED) -> list: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(f""" SELECT * FROM games WHERE (private = false) AND (status = {status}) ORDER BY id DESC """ + (f"LIMIT {count};" if count > 0 else ";")) return [GameModel(data) for data in cursor.fetchall()]
def get_top10() -> list: with db.connect() as conn, conn.cursor() as cursor: cursor.execute(""" SELECT * FROM users ORDER BY wins DESC LIMIT 10; """) return [User(data) for data in cursor.fetchall()]
import asyncio from app.database import db if __name__ == "__main__": loop = asyncio.get_event_loop() try: loop.run_until_complete(db.connect()) except Exception: exit(1) loop.run_until_complete(db.disconnect()) exit(0)