def refresh_player_data(player_id,position,depth_chart_position,fantasy_positions,first_name,last_name,full_name,years_exp,status,birth_date,college,height,weight,age):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO players_tbl(player_id,position,depth_chart_position,fantasy_positions,
first_name,last_name,full_name,years_exp,status,birth_date,
college,height,weight,age)
VALUES (%s, %s, %s, %s, %s,%s, %s, %s, %s,%s, %s, %s, %s, %s)
ON CONFLICT (player_id)
DO UPDATE SET
position=players_tbl.position,
depth_chart_position=players_tbl.depth_chart_position,
fantasy_positions=players_tbl.fantasy_positions,
first_name=players_tbl.first_name,
last_name=players_tbl.last_name,
full_name=players_tbl.full_name,
years_exp=players_tbl.years_exp,
status=players_tbl.status,
birth_date=players_tbl.birth_date,
college=players_tbl.college,
height=players_tbl.height,
weight=players_tbl.weight,
age=players_tbl.age
"""
cursor.execute(insert_query, (player_id,position,depth_chart_position,fantasy_positions,first_name,last_name,full_name,years_exp,status,birth_date,college,height,weight,age))
db.commit()
cursor.close()
db.close()
return
async def register(host, port, user_name):
async with open_connection(host, port) as (reader, writer):
server_response = await reader.readline()
logger.debug(repr(server_response.decode()))
writer.write(b'\n')
await writer.drain()
server_response = await reader.readline()
logger.debug(repr(server_response.decode()))
filtered_nick = re.sub(r'(\\+n|\n|\\+)', '', user_name)
message_to_send = f'{filtered_nick}\n'
logger.debug(repr(message_to_send))
writer.write(message_to_send.encode())
await writer.drain()
server_response = await reader.readline()
logger.debug(repr(server_response.decode()))
user_features = json.loads(server_response)
try:
user_token = user_features['account_hash']
except (AttributeError, KeyError):
user_token = None
return user_token
def refresh_projections_data(player_id,pass_att,pass_cmp,pass_int,pass_td,pass_yd,pass_2pt,rush_att,rush_yd,rush_td,rush_2pt,
fum,fum_lost,rec,rec_td,rec_2pt,
rec_tgt,rec_yd,def_td,ff,fum_rec,def_int,pts_allow,qb_hit,sack,tkl_ast,tkl_loss,tkl_solo,yds_allow,
safe,blk_kick,def_st_td,fga,fgm,xpa,xpm,st_td,fgm_50p,fgm_40_49,fgm_30_39,fgm_20_29,
pts_half_ppr,any_pts):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO projections_tbl(player_id,pass_att,pass_cmp,pass_int,pass_td,pass_yd,pass_2pt,rush_att,rush_yd,rush_td,rush_2pt,
fum,fum_lost,rec,rec_td,rec_2pt,
rec_tgt,rec_yd,def_td,ff,fum_rec,def_int,pts_allow,qb_hit,sack,tkl_ast,tkl_loss,tkl_solo,yds_allow,
safe,blk_kick,def_st_td,fga,fgm,xpa,xpm,st_td,fgm_50p,fgm_40_49,fgm_30_39,fgm_20_29,
pts_half_ppr,any_pts)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s)"""
cursor.execute(insert_query, (player_id,pass_att,pass_cmp,pass_int,pass_td,pass_yd,pass_2pt,rush_att,rush_yd,rush_td,rush_2pt,
fum,fum_lost,rec,rec_td,rec_2pt,
rec_tgt,rec_yd,def_td,ff,fum_rec,def_int,pts_allow,qb_hit,sack,tkl_ast,tkl_loss,tkl_solo,yds_allow,
safe,blk_kick,def_st_td,fga,fgm,xpa,xpm,st_td,fgm_50p,fgm_40_49,fgm_30_39,fgm_20_29,
pts_half_ppr,any_pts))
db.commit()
cursor.close()
db.close()
return
def truncate_table(tbl_name):
db = open_connection.open_connection()
cursor = db.cursor()
drop = "TRUNCATE "+tbl_name
cursor.execute(drop)
db.commit()
cursor.close()
db.close()
return
def drop_table(tbl_name):
db = open_connection.open_connection()
cursor = db.cursor()
drop = "DROP TABLE IF EXISTS " + tbl_name
cursor.execute(drop)
db.commit()
cursor.close()
db.close()
return
def refresh_roster_data(User_ID,roster_id,Player_id):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = "INSERT INTO sleeper_raw.rosters_tbl(User_ID,roster_id,Player_id) VALUES (%s, %s, %s)"
cursor.execute(insert_query, (User_ID,roster_id,Player_id))
db.commit()
cursor.close()
db.close()
return
def refresh_user_data(display_name, league_id, user_id, team_name):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO users_tbl(display_name, league_id, user_id, team_name)
VALUES (%s, %s, %s, %s)
ON CONFLICT (user_id) DO UPDATE SET display_name=users_tbl.display_name, team_name=users_tbl.team_name"""
cursor.execute(insert_query, (display_name, league_id, user_id, team_name))
db.commit()
cursor.close()
db.close()
return
def refresh_team_data(user_year_id, user_id, abbrev, team_id, team_name):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO team_tbl(user_year_id, user_id, abbrev, team_id, team_name)
VALUES (%s, %s, %s, %s, %s)"""
cursor.execute(insert_query,
(user_year_id, user_id, abbrev, team_id, team_name))
db.commit()
cursor.close()
db.close()
return
def add_matchup_row(matchup_rost_key, year, week, matchup_id, roster_id,
points):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO matchups_tbl(matchup_rost_key,year,week,matchup_id,roster_id,points)
VALUES (%s, %s, %s, %s, %s, %s)"""
cursor.execute(
insert_query,
(matchup_rost_key, year, week, matchup_id, roster_id, points))
db.commit()
cursor.close()
db.close()
return
def refresh_user_data(user_year_id, year, display_name, user_id, first, last,
name):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO users_tbl(user_year_id, year, display_name, user_id, first, last, name)
VALUES (%s, %s, %s, %s, %s, %s, %s)"""
cursor.execute(
insert_query,
(user_year_id, year, display_name, user_id, first, last, name))
db.commit()
cursor.close()
db.close()
return
async def write_chat_history(host, port, output_file_path):
async with open_connection(host, port) as (reader, writer):
while True:
formatted_datetime = datetime.now().strftime('%d.%m.%y %H:%M')
received_data = await reader.readline()
message = received_data.decode()
log_note = f'{formatted_datetime} {message}'
try:
async with aiofiles.open(output_file_path, 'a') as file:
await file.write(log_note)
except FileNotFoundError:
logger.error(f'Can not write to the file {output_file_path}')
return
async def watch_for_connection(host, port, token):
async with open_connection(host, port) as (reader, writer):
await authorize(reader, writer, token)
while True:
try:
async with timeout(1) as timeout_manager:
writer.write(b'\n')
await writer.drain()
await reader.readline()
except asyncio.TimeoutError:
if not timeout_manager.expired:
raise
watchdog_logger.debug('1s timeout is elapsed')
raise ConnectionError
await asyncio.sleep(0.1)
def add_matchup_player_data(matchup_rost_plr_key, matchup_rost_key, year, week,
matchup_id, roster_id, Player_id, is_starter):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO sleeper_raw.matchups_plr_tbl(matchup_rost_plr_key, matchup_rost_key,year,week,matchup_id,roster_id,Player_id,is_starter)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (matchup_rost_plr_key)
DO NOTHING
"""
cursor.execute(insert_query,
(matchup_rost_plr_key, matchup_rost_key, year, week,
matchup_id, roster_id, Player_id, is_starter))
db.commit()
cursor.close()
db.close()
return
async def run_client(host, port, token, user_name, message):
if not token:
token = await register(host, port, user_name)
if not token:
error_message = 'Не удалось зарегистрировать нового пользователя.'
logger.error(error_message)
print(error_message)
return
async with open_connection(host, port) as (reader, writer):
user_features = await authorize(reader, writer, token)
if not user_features:
print('Не удалось получить свойства юзера.')
print('Проверьте токен юзера и номер порта сервера.')
return
await submit_message(reader, writer, message)
def add_draft_data(draft_player_key, draft_year, draft_type, round, pick_no,
overall_pick_no, user_id, roster_id, player_id):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO
draft_tbl(draft_player_key,draft_year,draft_type,round,pick_no,
overall_pick_no,user_id,roster_id,player_id)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (draft_player_key)
DO NOTHING
"""
cursor.execute(insert_query,
(draft_player_key, draft_year, draft_type, round, pick_no,
overall_pick_no, user_id, roster_id, player_id))
db.commit()
cursor.close()
db.close()
return
async def read_msgs(
message_queue,
history_queue,
host,
port,
status_updates_queue,
):
status_updates_queue.put_nowait(ReadConnectionStateChanged.INITIATED)
async with open_connection(host, port) as (reader, writer):
status_updates_queue.put_nowait(ReadConnectionStateChanged.ESTABLISHED)
try:
while True:
received_data = await reader.readline()
message = received_data.decode()
message_queue.put_nowait(message)
history_queue.put_nowait(message)
finally:
logger.debug('Stop the coroutine "read_msgs"')
status_updates_queue.put_nowait(ReadConnectionStateChanged.CLOSED)
raise
def add_matchup_data(matchup_rost_key, year, week, matchup_id, roster_id,
players, starters, points, matchup_start_date):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO sleeper_raw.matchups_tbl(matchup_rost_key,year,week,matchup_id,
roster_id,players,starters,points,matchup_start_date)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (matchup_rost_key)
DO UPDATE SET
points = Excluded.points,
matchup_start_date = Excluded.matchup_start_date,
starters = Excluded.starters,
players = Excluded.players
"""
cursor.execute(insert_query,
(matchup_rost_key, year, week, matchup_id, roster_id,
players, starters, points, matchup_start_date))
db.commit()
cursor.close()
db.close()
return
def add_transaction_data(tn_rost_plyr_id, transaction_id, creater_user_id,
transaction_type, year, week, status_date,
create_date, waiver_bid_ammount, transaction_status,
roster_id, player_id, add_drop, asset_type,
faab_ammount):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO
sleeper_raw.transactions_tbl(tn_rost_plyr_id,transaction_id,creater_user_id,transaction_type,
year,week,status_date,create_date,
waiver_bid_ammount,transaction_status,
roster_id,player_id,add_drop,asset_type,faab_ammount)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (tn_rost_plyr_id) DO NOTHING
;"""
cursor.execute(insert_query,
(tn_rost_plyr_id, transaction_id, creater_user_id,
transaction_type, year, week, status_date, create_date,
waiver_bid_ammount, transaction_status, roster_id,
player_id, add_drop, asset_type, faab_ammount))
db.commit()
cursor.close()
db.close()
return
async def send_messages(
host,
port,
sending_queue,
token,
status_msgs_queue,
):
status_msgs_queue.put_nowait(SendingConnectionStateChanged.INITIATED)
async with open_connection(host, port) as (reader, writer):
status_msgs_queue.put_nowait(SendingConnectionStateChanged.ESTABLISHED)
user_features = await authorize(reader, writer, token)
if not user_features:
logger.error('Не удалось получить свойства юзера.')
logger.error('Проверьте токен юзера.')
messagebox.showerror('Неверный токен',
'Проверьте токен, сервер его не узнал')
raise InvalidToken
user_name = user_features["nickname"]
logger.debug(f'Выполнена авторизация. Пользователь {user_name}')
status_msgs_queue.put_nowait(NicknameReceived(user_name))
try:
while True:
sending_message = await sending_queue.get()
logger.debug(f'Пользователь написал: {sending_message}')
filtered_message = re.sub(r'\n\n', '', sending_message)
server_response = await reader.readline()
logger.debug(repr(server_response.decode()))
writer.write((filtered_message + '\n').encode())
await writer.drain()
writer.write(b'\n')
await writer.drain()
finally:
logger.debug('Stop the coroutine "send_messages"')
status_msgs_queue.put_nowait(SendingConnectionStateChanged.CLOSED)
raise
# roster_data = pd.DataFrame(rosters_json)
# #pd.read_json(_, orient='split')
# print(roster_data)
drop_table("users_tbl")
user_create = """
CREATE TABLE users_tbl
(
display_name character(255),
league_id character(255),
user_id character(255),
team_name character(255),
primary key(user_id)
)
"""
db = open_connection.open_connection()
cursor = db.cursor()
cursor.execute(user_create)
db.commit()
cursor.close()
db.close()
#roster_tbl = pd.DataFrame(columns=['User_ID', 'Player_id'])
#DataFrameName.insert(loc, column, value, allow_duplicates = False)
for i in range(0, len(usrs_json)):
display_name = usrs_json[i]['display_name']
league_id = usrs_json[i]['league_id']
user_id = usrs_json[i]['user_id']
try:
team_name = usrs_json[i]['metadata']['team_name']
except:
