5vb7G5ev

1import time
2from asyncpg import create_pool
3from asyncpg.pool import Pool
4import config
5
6
7async def make_tables(pool: Pool):
8    """
9    Создает таблицы в дата базе если их ещё нет.
10    :param pool: пул для подключения.
11    """
12
13    sunpings = """
14    CREATE TABLE IF NOT EXISTS sunpings (
15        ip CIDR NOT NULL,
16        port SMALLINT NOT NULL DEFAULT 25565,
17        time TIME NOT NULL,
18        players INTEGER NOT NULL
19    );
20    """
21
22    sunservers = """
23    CREATE TABLE IF NOT EXISTS sunservers (
24        numip CIDR NOT NULL,
25        port SMALLINT NOT NULL DEFAULT 25565,
26        alias TEXT
27    );
28    """
29
30    db_entries = (sunpings, sunservers)
31    for db_entry in db_entries:
32        await pool.execute(db_entry)
33
34
35class PostgresController:
36    __slots__ = 'pool'
37
38    def __init__(self, pool: Pool):
39        self.pool = pool
40
41    @classmethod
42    async def get_instance(cls, connect_kwargs: str = config.POSTGRES, pool: Pool = None):
43        """
44        (лучше английский чем гугл переводчик)
45        Get a new instance of `PostgresController`
46        This method will create the appropriate tables needed.
47        :param connect_kwargs:
48            Keyword arguments for the
49            :func:`asyncpg.connection.connect` function.
50        :param pool: an existing connection pool.
51        One of `pool` or `connect_kwargs` must not be None.
52        :return: a new instance of `PostgresController`
53        """
54        assert connect_kwargs or pool, (
55            'Предоставьте либо пул подключений, либо данные о '
56            'подключении для создания нового пула подключений.'
57        )
58        if not pool:
59            pool = await create_pool(connect_kwargs)
60        await make_tables(pool)
61        return cls(pool)
62
63    async def get_ping_yest(self, numip, port=25565):
64        """
65        Возвращает пинг сервера сутки назад через FETCH
66        """
67        tm = time.localtime()
68        print(tm)
69        sql = """
70        SELECT * FROM sunpings
71        WHERE ip=$1 AND port=$2;
72        """
73        return await self.pool.fetch(sql, numip, port)
74