async def test_connection1(name, pool: aiomysql.Pool):
sql = """
SELECT 1;
"""
while True:
logging.info(f"{name}: pool_freesize %d", pool.freesize)
async with pool.acquire_with_transaction() as connection:
logging.info(connection)
await connection.begin()
async with connection.cursor() as cursor:
try:
await cursor.execute(sql)
await cursor.fetchone()
logging.info(f"{name}: OK")
except asyncio.CancelledError:
raise
except Exception as e:
logging.error(f"{name}: {e}")
# raise
await test_connection2('test_connection2', pool)
await test_connection3('test_connection3', pool)
await connection.rollback()
await asyncio.sleep(1)
async def migrate(db_conn_pool: Pool) -> None:
print("Beginning database migrations")
async with db_conn_pool.acquire() as conn:
async with conn.cursor() as cur:
await schedule_v1.upgrade(cur)
await requests_v3.upgrade(cur)
await ggroups_v1.upgrade(cur)
await conn.commit()
async def _query(pool: Pool, query: str) -> Tuple[Any]:
async with pool.acquire() as conn:
conn: Connection = conn # NOTE(pjongy): For type hinting
async with conn.cursor() as cur:
cur: Cursor = cur # NOTE(pjongy): For type hinting
await cur.execute(query)
result = await cur.fetchall()
await conn.commit()
return result
async def postgres_clean_sequences(mysql_pool: aiomysql.Pool, postgres_pool: asyncpg.pool.Pool):
async with mysql_pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT SeqId FROM temp.libseqname")
mysql_seqs_ids = set([x[0] for x in (await cursor.fetchall())])
postgres_seqs_ids = set([x["seq_id"] for x in (await postgres_pool.fetch("SELECT seq_id FROM seqname"))])
seqs_ids_to_delete = postgres_seqs_ids - mysql_seqs_ids
await delete_seqs(postgres_pool, seqs_ids_to_delete)
async def postgres_clean_authors(mysql_pool: aiomysql.Pool, postgres_pool: asyncpg.pool.Pool):
async with mysql_pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT AvtorId from temp.libavtor")
mysql_authors_ids = set([x[0] for x in (await cursor.fetchall())])
postgres_authors_ids = set([x["id"] for x in (await postgres_pool.fetch("SELECT id FROM author"))])
authors_ids_to_delete = postgres_authors_ids - mysql_authors_ids
await delete_authors(postgres_pool, authors_ids_to_delete)
async def postgres_clean_books(mysql_pool: aiomysql.Pool, postgres_pool: asyncpg.pool.Pool):
async with mysql_pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT BookId FROM temp.libbook")
mysql_books_ids = set([x[0] for x in (await cursor.fetchall())])
postgres_books_ids = set([x["id"] for x in (await postgres_pool.fetch("SELECT id FROM book"))])
books_ids_to_delete = postgres_books_ids - mysql_books_ids
await delete_books(postgres_pool, books_ids_to_delete)
async def producent(queue: Queue, pool: Pool):
async with pool.acquire() as conn:
async with conn.cursor() as cur:
num = await cur.execute("SELECT id, final_url from webs where wp=1"
)
items = await cur.fetchall()
for row in items:
await queue.put(row)
for _ in range(WORKERS):
await queue.put(STOP)
async def test_connection3(name, pool: aiomysql.Pool):
sql = """
SELECT 1;
"""
async with pool.acquire() as connection:
logging.info(connection)
async with connection.cursor() as cursor:
try:
await cursor.execute(sql)
await cursor.fetchone()
logging.info(f"{name}: OK")
except asyncio.CancelledError:
raise
except Exception as e:
logging.error(f"{name}: {e}")