async def create_pool(cls, pool: Pool) -> "FakeAsyncPGPool":
pool = cls(pool)
conn = await pool._pool.acquire()
tx = conn.transaction()
await tx.start()
pool._conn = conn
pool._tx = tx
return pool
async def test_user_can_not_change_following_state_to_the_same_twice(
app: FastAPI,
authorized_client: AsyncClient,
pool: Pool,
test_user: UserInDB,
api_method: str,
route_name: str,
following: bool,
) -> None:
async with pool.acquire() as conn:
users_repo = UsersRepository(conn)
user = await users_repo.create_user(
username="******",
email="*****@*****.**",
password="******",
)
if following:
profiles_repo = ProfilesRepository(conn)
await profiles_repo.add_user_into_followers(
target_user=user, requested_user=test_user)
response = await authorized_client.request(
api_method, app.url_path_for(route_name, username=user.username))
assert response.status_code == status.HTTP_400_BAD_REQUEST
async def get_batches(cls,
pool: Pool = None,
nax_batches: int = 30) -> Dict[str, List[str]]:
rc: Dict = {}
if not pool:
pool = config.db_conn_pool
async with pool.acquire() as con:
async with con.transaction():
rows = await con.fetch(
"""
SELECT batch_id, algo_run_id, algo_name, algo_env, build_number, start_time
FROM algo_run
ORDER BY start_time DESC
LIMIT $1
""",
nax_batches,
)
if rows:
for row in rows:
if row[0] not in rc:
rc[row[0]] = [list(row.values())[1:]]
else:
rc[row[0]].append(list(row.values())[1:])
return rc
async def save(self, pool: Pool) -> bool:
try:
async with pool.acquire() as con:
async with con.transaction():
await con.execute(
"""
INSERT INTO ticker_data (symbol, name, description, tags,
similar_tickers, industry, sector, exchange)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
ON CONFLICT (symbol)
DO UPDATE
SET name=$2, description=$3, tags=$4, similar_tickers=$5, industry=$6,
sector=$7, exchange=$8, modify_tstamp='now()'
""",
self.symbol,
self.name,
self.description,
self.tags,
self.similar_tickers,
self.industry,
self.sector,
self.exchange,
)
return True
except TooManyConnectionsError as e:
tlog(
f"too many db connections: {e}. Failed to write ticker {self.symbol}, will re-try"
)
return False
async def save(
self,
pool: Pool = None,
) -> None:
if not pool:
pool = config.db_conn_pool
async with pool.acquire() as con:
async with con.transaction():
await con.execute(
"""
INSERT INTO stock_ohlc (symbol, symbol_date, open, high, low, close, volume, indicators)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
ON CONFLICT (symbol, symbol_date)
DO UPDATE
SET open=$3, high=$4, low=$5, close=$6, volume=$7, indicators=$8, modify_tstamp='now()'
""",
self.symbol,
self.symbol_date,
self.open,
self.high,
self.low,
self.close,
self.volume,
json.dumps(self.indicators),
)
async def abstract_query(command, query, args=(), pool: Pool = None):
pool = await PgPool.get_pool()
async with pool.acquire() as conn:
async with conn.transaction():
func = getattr(conn, command)
result = await func(query, *args)
return result
async def test_update_game(pool: Pool, test_auth0_user: Auth0User):
async with pool.acquire() as conn:
db_repo = ChessDbRepository(conn)
game_repo = ChessDbGameRepository(conn)
db = await db_repo.create_db(name="db1", user=test_auth0_user)
expected = ChessDbGame(
date=datetime.date(1999, 1, 1),
result="1-0",
event="Some Tournament",
black="Some guy",
white="another dude",
chess_db_id=db.id,
user_id=test_auth0_user.id,
id=11
)
expected = await game_repo.create_game(db_id=expected.chess_db_id, user_id=expected.user_id,
white=expected.white,
black=expected.black, event=expected.event, date=expected.date,
result=expected.result)
expected.date = datetime.date(2000, 1, 2)
expected.result = "*"
expected.event = "Madrid"
expected.black = "A player"
expected.white = "another player"
actual = await game_repo.update_game(game=expected)
assert actual == expected
assert len(await game_repo.find_by_db_id(db_id=db.id)) == 1
async def get_data_from_db(uuid: UUID, pool: Pool) -> Union[bool, List]:
"""
Поиск локального uuid и возвращение данных из базы.
:param UUID uuid: - локальный uuid, по которому ищутся данные в базе.
:param Pool pool: - пул подключения к базе данных.
:return List: - список с данными из базы данных.
"""
if uuid is None or pool is None:
return False
try:
j = parse_event_table.join(
names_table, parse_event_table.c.primary_names_id == names_table.c.id
)
query_data = parse_event_table.select().with_only_columns(
[
parse_event_table.c.bankrupt_name,
parse_event_table.c.title,
parse_event_table.c.number,
parse_event_table.c.text,
parse_event_table.c.data_publish,
parse_event_table.c.guid,
]
).select_from(j).where(
names_table.c.local_uuid == uuid
).where(
parse_event_table.c.type == 'BankruptcyMessage'
)
async with pool.acquire() as conn:
values = ListEvent(data=await conn.fetch(query_data))
return values
except Exception as error:
return False
async def get_prices(
dataset_name: SessionDatasetNames,
session_id,
request_params: DataRequestSchema,
pool: Pool,
) -> List[PricepointSchema]:
"""Get prices."""
if not request_params.to_datetime:
request_params.to_datetime = datetime.now()
async with pool.acquire() as conn:
query = f"""
SELECT
-- time_bucket_gapfill($1, timestamp, now() - INTERVAL '2 hours', now()) AS time,
time_bucket_gapfill($1, timestamp) AS time,
locf(avg(price)) as price,
sum(volume) as volume
FROM {dataset_name}_ticks
WHERE session_id = $2
and label = $3
and data_type = $4
and timestamp BETWEEN $5 and $6
GROUP BY time
ORDER BY time ASC;
"""
params = (
timedelta(minutes=request_params.period),
session_id,
request_params.label,
request_params.data_type,
request_params.from_datetime,
request_params.to_datetime,
)
result = list(await conn.fetch(query, *params))
return parse_obj_as(List[PricepointSchema], result)
async def test_user_can_change_following_for_another_user(
app: FastAPI,
authorized_client: AsyncClient,
pool: Pool,
test_user: UserInDB,
api_method: str,
route_name: str,
following: bool,
) -> None:
async with pool.acquire() as conn:
users_repo = UsersRepository(conn)
user = await users_repo.create_user(
username="******",
email="*****@*****.**",
password="******",
)
if not following:
profiles_repo = ProfilesRepository(conn)
await profiles_repo.add_user_into_followers(
target_user=user, requested_user=test_user)
change_following_response = await authorized_client.request(
api_method, app.url_path_for(route_name, username=user.username))
assert change_following_response.status_code == status.HTTP_200_OK
response = await authorized_client.get(
app.url_path_for("profiles:get-profile", username=user.username))
profile = ProfileInResponse(**response.json())
assert profile.profile.username == user.username
assert profile.profile.following == following
async def test_create_db_game(authorized_client: AsyncClient, app: FastAPI,
test_auth0_user: Auth0User, pool: Pool):
db_id = (await post_create_db(authorized_client, app, "db1")).json()["id"]
json = {
"game": {
"white": "a player",
"black": "another player",
"event": "London Classic",
"date": datetime.date(2018, 6, 12).strftime("%Y-%m-%d"),
"result": "0-1"
}
}
res = await authorized_client.post(app.url_path_for("db:create_game",
db_id=db_id),
json=json)
assert res.status_code == HTTP_201_CREATED
g = res.json()
assert g["white"] == json["game"]["white"]
assert g["black"] == json["game"]["black"]
assert g["event"] == json["game"]["event"]
assert g["date"] == json["game"]["date"]
assert g["result"] == json["game"]["result"]
assert g["userId"] == test_auth0_user.id
assert g["chessDbId"] == db_id
assert g["id"] >= 0
async with pool.acquire() as connection:
repository = ChessDbGameRepository(connection)
assert len(await repository.find_by_db_id(db_id=db_id)) == 1
async def load_by_date(cls,
symbol_date: date,
pool: Pool = None) -> Dict[str, object]:
if not pool:
pool = config.db_conn_pool
async with pool.acquire() as con:
async with con.transaction():
rows = await con.fetch(
"""
SELECT symbol, symbol_date, open, high, low, close, volume, indicators
FROM stock_ohlc
WHERE symbol_date = $1
""",
symbol_date,
)
rc: Dict[str, object] = {}
for x in rows:
rc[x[0]] = StockOhlc(
symbol=x[0],
symbol_date=x[1],
open=x[2],
high=x[3],
low=x[4],
close=x[5],
volume=x[6],
indicators=json.loads(x[7]),
)
return rc
async def save(
self,
pool: Pool,
client_buy_time: str,
stop_price=None,
target_price=None,
):
async with pool.acquire() as con:
async with con.transaction():
try:
indicators_s = json.dumps(self.indicators or {},
allow_nan=False)
except ValueError:
indicators_s = json.dumps({})
self.trade_id = await con.fetchval(
"""
INSERT INTO new_trades (algo_run_id, symbol, operation, qty, price, indicators, client_time, stop_price, target_price)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING trade_id
""",
self.algo_run_id,
self.symbol,
self.operation,
self.qty,
self.price,
indicators_s,
client_buy_time,
stop_price,
target_price,
)
async def table_index(db_pool: Pool) -> Sequence:
"""Fetch Table index."""
async with db_pool.acquire() as conn:
q = await conn.prepare(sql_query)
content = await q.fetchval()
return json.loads(content)
async def set_max_sequence(self, dst_pool: Pool):
async with dst_pool.acquire() as connection:
try:
get_serial_sequence_sql = SQLRepository.get_serial_sequence_sql(
table_name=self.name,
pk_column_name=self.primary_key.name,
)
except AttributeError:
logger.warning(
f'AttributeError --- {self.name} --- set_max_sequence')
return
serial_seq_name = await connection.fetchrow(get_serial_sequence_sql
)
if serial_seq_name and serial_seq_name[0]:
serial_seq_name = serial_seq_name[0]
max_val = self.max_pk + 100000
set_sequence_val_sql = (
SQLRepository.get_set_sequence_value_sql(
seq_name=serial_seq_name,
seq_val=max_val,
))
await connection.execute(set_sequence_val_sql)
async def get_batch_list_by_date(
cls,
batch_date: date,
pool: Pool = None,
) -> Dict[str, List[str]]:
rc: Dict = {}
if not pool:
pool = config.db_conn_pool
async with pool.acquire() as con:
async with con.transaction():
rows = await con.fetch(
"""
SELECT batch_id, algo_run_id, algo_name, algo_env, build_number, start_time
FROM algo_run
ORDER BY start_time DESC
WHERE start_time >= $1 and start_time < $2
""",
batch_date,
batch_date + timedelta(days=1),
)
if rows:
for row in rows:
if row[0] not in rc:
rc[row[0]] = [list(row.values())[1:]]
else:
rc[row[0]].append(list(row.values())[1:])
return rc
async def get_batch_details(
cls, batch_id: str, pool: Pool = None
) -> List[Tuple[int, datetime, datetime, str]]:
rc: List = []
if not pool:
pool = config.db_conn_pool
async with pool.acquire() as con:
async with con.transaction():
rows = await con.fetch(
"""
SELECT algo_run_id, start_time, end_time, parameters, algo_name
FROM algo_run
WHERE batch_id = $1
ORDER BY start_time DESC
""",
batch_id,
)
if rows:
rc = [
(
row[0],
row[1],
row[2],
row[3],
)
for row in rows
]
return rc
async def get_batches(
cls,
pool: Pool = None,
nax_batches: int = 30,
start_date: date = date(2019, 1, 1),
) -> List:
rc: Dict = {}
if not pool:
pool = config.db_conn_pool
async with pool.acquire() as con:
async with con.transaction():
rows = await con.fetch(
"""
SELECT build_number, batch_id, algo_name, algo_env, date_trunc('minute', min(start_time)) as start
FROM algo_run
WHERE start_time > $2
GROUP BY batch_id, algo_name, algo_env, build_number
ORDER BY start DESC
LIMIT $1
""",
nax_batches,
start_date,
)
if rows:
return [list(map(str, row.values())) for row in rows]
return []
async def test_empty_feed_if_user_has_not_followings(
app: FastAPI,
authorized_client: Client,
test_article: Article,
test_user: UserInDB,
pool: Pool,
) -> None:
async with pool.acquire() as connection:
users_repo = UsersRepository(connection)
articles_repo = ArticlesRepository(connection)
for i in range(5):
user = await users_repo.create_user(username=f"user-{i}",
email=f"user-{i}@email.com",
password="******")
for j in range(5):
await articles_repo.create_article(
slug=f"slug-{i}-{j}",
title="tmp",
description="tmp",
body="tmp",
author=user,
tags=[f"tag-{i}-{j}"],
)
response = await authorized_client.get(
app.url_path_for("articles:get-user-feed-articles"))
articles = ListOfArticlesInResponse(**response.json())
assert articles.articles == []
async def test_article_will_contain_only_attached_tags(
app: FastAPI, authorized_client: Client, test_user: UserInDB,
pool: Pool) -> None:
attached_tags = ["tag1", "tag3"]
async with pool.acquire() as connection:
articles_repo = ArticlesRepository(connection)
await articles_repo.create_article(
slug=f"test-slug",
title="tmp",
description="tmp",
body="tmp",
author=test_user,
tags=attached_tags,
)
for i in range(5):
await articles_repo.create_article(
slug=f"slug-{i}",
title="tmp",
description="tmp",
body="tmp",
author=test_user,
tags=[f"tag-{i}"],
)
response = await authorized_client.get(
app.url_path_for("articles:get-article", slug="test-slug"))
article = ArticleInResponse(**response.json())
assert len(article.article.tags) == len(attached_tags)
assert set(article.article.tags) == set(attached_tags)
async def test_user_can_not_modify_article_that_is_not_authored_by_him(
app: FastAPI,
authorized_client: Client,
pool: Pool,
api_method: str,
route_name: str,
) -> None:
async with pool.acquire() as connection:
users_repo = UsersRepository(connection)
user = await users_repo.create_user(username="******",
email="*****@*****.**",
password="******")
articles_repo = ArticlesRepository(connection)
await articles_repo.create_article(
slug="test-slug",
title="Test Slug",
description="Slug for tests",
body="Test " * 100,
author=user,
tags=["tests", "testing", "pytest"],
)
response = await authorized_client.request(
api_method,
app.url_path_for(route_name, slug="test-slug"),
json={"article": {
"title": "Updated Title"
}},
)
assert response.status_code == status.HTTP_403_FORBIDDEN
async def test_user_can_change_favorite_state(
app: FastAPI,
authorized_client: Client,
test_article: Article,
test_user: UserInDB,
pool: Pool,
api_method: str,
route_name: str,
favorite_state: bool,
) -> None:
if not favorite_state:
async with pool.acquire() as connection:
articles_repo = ArticlesRepository(connection)
await articles_repo.add_article_into_favorites(
article=test_article, user=test_user)
await authorized_client.request(
api_method, app.url_path_for(route_name, slug=test_article.slug))
response = await authorized_client.get(
app.url_path_for("articles:get-article", slug=test_article.slug))
article = ArticleInResponse(**response.json())
assert article.article.favorited == favorite_state
assert article.article.favorites_count == int(favorite_state)
async def test_user_can_not_take_already_used_credentials(
app: FastAPI,
authorized_client: AsyncClient,
pool: Pool,
token: str,
credentials_part: str,
credentials_value: str,
) -> None:
user_dict = {
"username": "******",
"password": "******",
"email": "*****@*****.**",
}
user_dict.update({credentials_part: credentials_value})
async with pool.acquire() as conn:
users_repo = UsersRepository(conn)
await users_repo.create_user(**user_dict)
response = await authorized_client.put(
app.url_path_for("users:update-current-user"),
json={"user": {
credentials_part: credentials_value
}},
)
assert response.status_code == status.HTTP_400_BAD_REQUEST
async def load_latest(
cls, pool: Pool, symbol: str, strategy_name: str
) -> Tuple[int, float, float, float, Dict, datetime]:
async with pool.acquire() as con:
async with con.transaction():
row = await con.fetchrow(
"""
SELECT t.algo_run_id, t.price, t.stop_price, t.target_price, t.indicators, t.tstamp
FROM new_trades as t, algo_run as a
WHERE
t.algo_run_id=a.algo_run_id AND
a.algo_name=$2 AND
symbol=$1
ORDER BY tstamp DESC LIMIT 1
""",
symbol,
strategy_name,
)
if row:
return (
int(row[0]),
float(row[1]),
float(row[2]),
float(row[3]),
json.loads(row[4]),
row[5],
)
tlog(f"{symbol} no data for strategy {strategy_name}")
raise ValueError
async def test_filtering_with_limit_and_offset(app: FastAPI,
authorized_client: Client,
test_user: UserInDB,
pool: Pool) -> None:
async with pool.acquire() as connection:
articles_repo = ArticlesRepository(connection)
for i in range(5, 10):
await articles_repo.create_article(
slug=f"slug-{i}",
title="tmp",
description="tmp",
body="tmp",
author=test_user,
)
full_response = await authorized_client.get(
app.url_path_for("articles:list-articles"))
full_articles = ListOfArticlesInResponse(**full_response.json())
response = await authorized_client.get(
app.url_path_for("articles:list-articles"),
params={
"limit": 2,
"offset": 3
})
articles_from_response = ListOfArticlesInResponse(**response.json())
assert full_articles.articles[3:] == articles_from_response.articles
async def get_market_sectors(pool: Pool) -> List[str]:
async with pool.acquire() as conn:
async with conn.transaction():
records = await conn.fetch("""
SELECT DISTINCT sector
FROM ticker_data
""")
return [record[0] for record in records if record[0]]
async def db_fetch(db: Pool, sql, *params):
async with db.acquire() as conn:
star = time.time()
rows = await conn.fetch(sql, *params)
end = round((time.time() - star) * 1000)
LOG.info('Found %s rows in %sms for %s parameters', len(rows), end,
params)
return rows
async def test_delete_db(pool: Pool, test_auth0_user: Auth0User):
async with pool.acquire() as connection:
repo = ChessDbRepository(connection)
res = await repo.create_db(name="test", user=test_auth0_user)
assert len(await repo.get_db_for_user(user_id=test_auth0_user.id)) == 1
res = await repo.delete_db(db_id=res.id)
assert res == True
assert len(await repo.get_db_for_user(user_id=test_auth0_user.id)) == 0
async def test_create_db(pool: Pool, test_auth0_user: Auth0User):
db_name = "testDb"
async with pool.acquire() as connection:
repo = ChessDbRepository(connection)
db = await repo.create_db(name=db_name, user=test_auth0_user)
assert db.name == db_name
assert db.user_id == test_auth0_user.id
assert db.id >= 0
async def run_query(query: str, pool: Pool, message_store: MessageStore):
async with pool.acquire() as connection:
try:
result = await connection.fetchrow(query)
await message_store.append(
f'Fetched {len(result)} rows from: {query}')
except Exception as e:
await message_store.append(f'Got exception {e} from: {query}')
