def gen_dealt_hands_and_add_to_db(
n_games,
table=GameC.CHINESE_POKER_db_consts['dealt_hands_table'],
write_every=1000):
conn = DBF.connect_to_db()
cursor = conn.cursor()
start = timer()
to_be_inserted = []
base_query = f'INSERT INTO {table} (DealtHandsStr) VALUES '
for i in range(1, n_games + 1):
dealt_cards = deck_obj.deal_cards()
dealt_cards_str = _convert_dealt_cards_to_rep_str(dealt_cards)
to_be_inserted.append(dealt_cards_str)
if i % write_every == 0:
query = base_query
for dealt_cards_str in to_be_inserted:
query += f'({dealt_cards_str}),'
#insert_query = """INSERT INTO random_dealt_hands (DealtHandsStr) VALUES (%s)"""
#cursor.executemany(insert_query, to_be_inserted)
query = query[:-1]
cursor.execute(query)
conn.commit()
min_elapsed = (timer() - start) / 60
print(
f'Inserted {i} of {n_games} DealtHandsStr rows into {table} - {min_elapsed} min elasped.'
)
to_be_inserted = []
return
def update_CPT_round_row_with_com_split(app_round_ID, com_id, seq_no):
#split_inds_str = DF._convert_card_inds_to_set_inds_str(split_inds)
query = f"UPDATE {CPT_rounds_table} SET Com{com_id}SplitSeqNo = {seq_no} WHERE RoundID = {app_round_ID}"
#print(query)
_ = DBF.insert_query(query, None, False, False)
return
def yield_dealt_hands_from_db(
self,
start_game_no,
end_game_no,
db_connector=None,
db_load_batch_size=1000,
cards_as_str=False,
):
dealt_hands_table = GameC.CHINESE_POKER_db_consts["dealt_hands_table"]
base_query = f'SELECT GameID, DealtHandsStr FROM {dealt_hands_table} WHERE GameID BETWEEN %s AND %s'
deck_obj = self.deck
if start_game_no is None:
start_game_no = 1
temp_start_game_no = start_game_no
while 1:
temp_end_game_no = min(end_game_no,
temp_start_game_no + db_load_batch_size - 1)
query = base_query % (temp_start_game_no, temp_end_game_no)
db_output, _ = DBF.select_query(query)
for row in db_output:
dealt_cards = self._convert_rep_str_to_dealt_cards(
row[1], deck_obj)
if cards_as_str:
dealt_cards = [[str(card) for card in hand]
for hand in hands]
yield row[0], dealt_cards
if temp_end_game_no >= end_game_no:
break
temp_start_game_no += db_load_batch_size
yield None, None
def create_CPT_game_row(clientIP, com_difficulties):
query = f"INSERT INTO {CPT_game_table} (ClientIP, Com1Difficulty, Com2Difficulty, Com3Difficulty, StartTimeUTC) VALUES ('%s', %s, %s, %s, '%s')"
cur_datetime = datetime.utcnow().strftime(GConst.SQL_DATETIME_FORMAT)
query = query % (clientIP, com_difficulties[0], com_difficulties[1],
com_difficulties[2], cur_datetime)
print(query)
_, app_game_ID = DBF.insert_query(query, None, False, True)
return app_game_ID
def gen_leaderboard(show_top_n_scores=10, cur_app_game_ID=None):
query = f"SELECT AppGameID, Name, Com1Difficulty+Com2Difficulty+Com3Difficulty AS TotComDifficulty, NoRounds, TotScore, TotCptScore, CAST(EndTimeUTC AS DATE) AS GameDate FROM {CPT_game_table} WHERE InLeaderboard=1 ORDER BY (TotScore-TotCptScore)/NoRounds DESC, (Com1Difficulty+Com2Difficulty+Com3Difficulty) DESC, NoRounds DESC, EndTimeUTC ASC LIMIT {show_top_n_scores}"
output, _ = DBF.select_query(query)
processed_output = []
for row_i, row in enumerate(output):
is_user = 0
if row[0] == cur_app_game_ID:
is_user = 1
print(row)
row_data = [
row_i + 1, # Rank
row[1], # Name
row[2], # TotCompDifficulty
row[3], # NoRounds
row[4], # TotScore
row[5], # TotCPTScore
row[6].strftime('%Y/%m/%d'), # GameDate
is_user, # IsUser
]
processed_output.append(row_data)
if cur_app_game_ID:
if sum([row[-1] for row in processed_output]) == 0:
query = f"SELECT AppGameID, Name, Com1Difficulty+Com2Difficulty+Com3Difficulty AS TotComDifficulty, NoRounds, TotScore, TotCptScore, CAST(EndTimeUTC AS DATE) AS GameDate FROM {CPT_game_table} WHERE AppGameID={cur_app_game_ID} AND InLeaderboard=1"
output, _ = DBF.select_query(query)
if output:
row_data = [
None, # Rank
output[0][1], # Name
output[0][2], # TotCompDifficulty
output[0][3], # NoRounds
output[0][4], # TotScore
output[0][5], # TotCPTScore
output[0][6].strftime('%Y/%m/%d'), # GameDate
1, # IsUser
]
processed_output.append(row_data)
#query = f"SELECT AppGameID, Name, Com1Difficulty+Com2Difficulty+Com3Difficulty AS TotComDifficulty, NoRounds, TotScore, TotCptScore, CAST(EndTimeUTC AS DATE) AS GameDate FROM {CPT_game_table} WHERE InLeaderboard=1 WHERE AppGameID={cur_app_game_ID}"
# Fetch the rank of the cur_app_game_ID
# If cur_app_game_ID not in the output, then run a separate query
return processed_output
def create_CPT_round_row(app_game_ID, app_round_no, hand_game_ID,
hand_seat_ID):
query = f"INSERT INTO {CPT_rounds_table} (AppGameID, RoundNo, HandGameID, HandSeatID, StartTimeUTC) VALUES (%s, %s, %s, %s, '%s')"
cur_datetime = datetime.utcnow().strftime(GConst.SQL_DATETIME_FORMAT)
query = query % (app_game_ID, app_round_no, hand_game_ID, hand_seat_ID,
cur_datetime)
#print(query)
_, app_round_ID = DBF.insert_query(query, None, False, True)
return app_round_ID
def max_game_id_in_splits_table(cls):
splits_table = GameC.CHINESE_POKER_db_consts['splits_table']
query = f'SELECT MAX(GameID) FROM {splits_table}'
db_output, _ = DBF.select_query(query)
return db_output[0][0]
######################################
### END Useful query functions END ###
######################################
def number_of_feasible_splits_for_game_and_seat(game_id, seat_id):
splits_table = GameC.CHINESE_POKER_db_consts['splits_table']
query = f'SELECT Max(SplitSeqNo) FROM {splits_table} WHERE GameID={game_id} AND SeatID={seat_id}'
db_output, _ = DBF.select_query(query)
return db_output[0][0]
######################################
### END Useful query functions END ###
######################################
def yield_dealt_hands_from_db(
start_game_no,
end_game_no,
db_connector=None,
db_load_batch_size=1000,
cards_as_str=False,
):
"""Generator that yields dealt cards from DB one game at a time for a range of GameIDs.
Args:
start_game_no (int): Start game number.
end_game_no (int): End game number.
db_connector ([type], optional): [description]. Defaults to None.
db_load_batch_size (int, optional): [description]. Defaults to 1000.
cards_as_str (bool, optional): Return cards as string rather then Card objects. Defaults to False.
Yields:
[type]: [description]
"""
dealt_hands_table = GameC.CHINESE_POKER_db_consts["dealt_hands_table"]
base_query = f'SELECT GameID, DealtHandsStr FROM {dealt_hands_table} WHERE GameID BETWEEN %s AND %s'
if start_game_no is None:
start_game_no = 1
temp_start_game_no = start_game_no
while 1:
temp_end_game_no = min(end_game_no,
temp_start_game_no + db_load_batch_size - 1)
query = base_query % (temp_start_game_no, temp_end_game_no)
db_output, _ = DBF.select_query(query)
for row in db_output:
dealt_cards = _convert_rep_str_to_dealt_cards(row[1])
if cards_as_str:
dealt_cards = [[str(card) for card in hand]
for hand in dealt_cards]
yield row[0], dealt_cards
if temp_end_game_no >= end_game_no:
break
temp_start_game_no += db_load_batch_size
yield None, None
def _get_split(game_id, seat_id, split_seq_no):
splits_table = GameC.CHINESE_POKER_db_consts['splits_table']
query = f'SELECT SplitStr FROM {splits_table} WHERE GameID={game_id} AND SeatID={seat_id} AND SplitSeqNo={split_seq_no}'
db_output, _ = DBF.select_query(query)
return db_output[0][0]
def update_CPT_round_row_with_player_split_as_com_100(app_round_ID, seq_no):
query = f"UPDATE {CPT_rounds_table} SET PlayerSplitSeqNoCom100 = {seq_no} WHERE RoundID = {app_round_ID}"
#print(query)
_ = DBF.insert_query(query, None, False, False)
return
def get_splits_data_for_single_game_and_seat_from_db(
game_id,
seat_id,
cards=None,
):
"""[summary]
Args:
game_id (int): GameID
seat_id (int): Between 1 and 4
cards (List): List of Card objects or card strings.
"""
if cards is None:
hands = next(yield_dealt_hands_from_db(game_id, game_id))[1]
cards = hands[seat_id - 1]
elif isinstance(cards[0], str):
deck = Deck()
cards = deck.deal_custom_hand(cards)
splits_table = GameC.CHINESE_POKER_db_consts['splits_table']
codes_table = GameC.CHINESE_POKER_db_consts['split_codes_table']
query = f'SELECT SplitSeqNo, SplitStr, + ' \
f'c1.L1Code AS S1L1Code, c1.L2Code AS S1L2Code, c1.L3Code AS S1L3Code, c1.L4Code AS S1L4Code, c1.L5Code AS S1L5Code, c1.L6Code AS S1L6Code, ' + \
f'c2.L1Code AS S2L1Code, c2.L2Code AS S2L2Code, c2.L3Code AS S2L3Code, c2.L4Code AS S2L4Code, c2.L5Code AS S2L5Code, c2.L6Code AS S2L6Code, ' + \
f'c3.L1Code AS S3L1Code, c3.L2Code AS S3L2Code, c3.L3Code AS S3L3Code, c3.L4Code AS S3L4Code, c3.L5Code AS S3L5Code, c3.L6Code AS S3L6Code ' + \
f'FROM {splits_table} s ' + \
f'JOIN {codes_table} c1 ON s.SplitID=c1.SplitID ' + \
f'JOIN {codes_table} c2 ON s.SplitID=c2.SplitID ' + \
f'JOIN {codes_table} c3 ON s.SplitID=c3.SplitID ' + \
f'WHERE s.GameID={game_id} AND s.SeatID={seat_id} ' + \
f'AND c1.SetNo=1 AND c2.SetNo=2 AND c3.SetNo=3'
db_output, _ = DBF.select_query(query)
hand_splits = []
for row in db_output:
split_seq_no, split_str, s1c1, s1c2, s1c3, s1c4, s1c5, s1c6, s2c1, s2c2, s2c3, s2c4, s2c5, s2c6, s3c1, s3c2, s3c3, s3c4, s3c5, s3c6 = row
split_cards, split_inds = _convert_split_str_to_split_cards(
cards, split_str)
s1code = [s1c1, s1c2, s1c3, s1c4, s1c5, s1c6]
s2code = [s2c1, s2c2, s2c3, s2c4, s2c5, s2c6]
s3code = [s3c1, s3c2, s3c3, s3c4, s3c5, s3c6]
s1code = CardGroupCode([code for code in s1code if code is not None])
s2code = CardGroupCode([code for code in s2code if code is not None])
s3code = CardGroupCode([code for code in s3code if code is not None])
split_info_factory = ChinesePokerStrategy.ranked_split_info_factory
split_info = split_info_factory(
split_inds,
split_cards,
(s1code, s2code, s3code),
None,
None,
None,
None,
None,
split_seq_no,
)
hand_splits.append(split_info)
hand_splits = sorted(hand_splits, key=lambda x: x.SeqNo)
return hand_splits
def write_splits_data_to_db(game_id, splits_data):
"""Function for writing splits data generated by
yield_splits_from_dealt_hands to db.
Writes to: feasible_hand_splits - GameID, SeatID, SplitSeqNo, SplitStr, DateGen
split_set_codes - SplitID, SeatID, SetNo, L1Code, L2Code, L3Code, L4Code, L5Code, L6Code
Args:
game_id (int): Game ID - Should match what is in
random_dealt_hands
splits_data ([type]): Dict with following structure:
('P{PLAYERNO}':player_splits_data) where PLAYERNO between 1 and 4 inclusive
player_splits_data itself is a dict with the following structure:
'DealtCards': Dealt cards in original order
'CardInds': List of indices lists.
Each outer list corresponds to a possible split.
The indices refer to the indices of the DealtCards list.
The order combined with the split_into attribute of the strategy object infers the split card sets.
'Codes': List of list of tuples.
Each outer list corresponds to a possible split.
Each inner list consists of tuples representing the card group codes of the sets.
'Scores': List of score lists.
Each outer list corresponds to a possible split.
Each inner list contains the set scores.
'WeightedScores': List of weighted scores.
One score for each possible split.
'SplitIndsStrs': List of strings, each of length 13, the characters correspond to the dealt cards.
Each string corresponds to a possible split.
The characters refer to the set number where each card is split into.
E.g. '1112222233333' means the first 3 cards (of DealtCards) are in the first set,
the next 5 cards are in the second set and the last 5 cards are in the third set.
"""
date_gen = datetime.today().strftime('%Y-%m-%d')
splits_table = GameC.CHINESE_POKER_db_consts['splits_table']
codes_table = GameC.CHINESE_POKER_db_consts['split_codes_table']
db_connector = DBF.connect_to_db()
# First check that there are no existing splits in the DB. If there are, delete them.
check_query = f"SELECT COUNT(*) FROM {splits_table} WHERE GameID={game_id} AND DateGenerated ='{date_gen}'"
db_output, db_connector = DBF.select_query(check_query, db_connector)
if db_output[0][0] > 0:
delete_query = f"DELETE FROM {splits_table} WHERE GameID={game_id} AND DataGenerated='{date_gen}'"
db_connector, rows_deleted = DBF.delete_query(delete_query,
db_connector)
print(
f'***Deleted {rows_deleted} rows with identical GameID ({game_id}) and DateGenerated ({date_gen}).***'
)
for seat_ID in range(1, 5):
player_splits_data = splits_data[f'P{seat_ID}']
#n_splits = len(player_splits_data['SplitIndsStrs']
for seqI, split_str in enumerate(player_splits_data['SplitIndsStrs']):
splits_query = f'INSERT INTO {splits_table} (GameID, SeatID, SplitSeqNo, SplitStr, DateGenerated) VALUES (%s, %s, %s, %s, %s)'
splits_query = splits_query % (game_id, seat_ID, seqI + 1,
split_str, f"'{date_gen}'")
db_connector, split_id = DBF.insert_query(splits_query,
db_connector, False,
True)
base_codes_query = f'INSERT INTO {codes_table} ' + \
'(SplitID, SetNo, L1Code, L2Code, L3Code, L4Code, L5Code, L6Code) VALUES ' + \
'(%s, %s, %s, %s, %s, %s, %s, %s)'
values_list = []
for setI, set_code in enumerate(player_splits_data['Codes'][seqI]):
base_val = [None for i in range(8)]
base_val[0] = split_id
base_val[1] = setI + 1
for levelI, level_code in enumerate(set_code):
base_val[2 + levelI] = level_code
values_list.append(tuple(base_val))
db_connector = DBF.insert_many_query(base_codes_query, values_list,
db_connector, True)
db_connector = DBF.try_commit(db_connector)
return
def yield_splits_data_from_db(
self,
start_game_id=None,
end_game_id=None,
):
#db_connector = DBF.connect_to_db()
splits_table = GameC.CHINESE_POKER_db_consts['splits_table']
codes_table = GameC.CHINESE_POKER_db_consts['split_codes_table']
base_query = f'SELECT SplitSeqNo, SplitStr, + ' \
f'c1.L1Code AS S1L1Code, c1.L2Code AS S1L2Code, c1.L3Code AS S1L3Code, c1.L4Code AS S1L4Code, c1.L5Code AS S1L5Code, c1.L6Code AS S1L6Code, ' + \
f'c2.L1Code AS S2L1Code, c2.L2Code AS S2L2Code, c2.L3Code AS S2L3Code, c2.L4Code AS S2L4Code, c2.L5Code AS S2L5Code, c2.L6Code AS S2L6Code, ' + \
f'c3.L1Code AS S3L1Code, c3.L2Code AS S3L2Code, c3.L3Code AS S3L3Code, c3.L4Code AS S3L4Code, c3.L5Code AS S3L5Code, c3.L6Code AS S3L6Code ' + \
f'FROM {splits_table} s ' + \
f'JOIN {codes_table} c1 ON s.SplitID=c1.SplitID ' + \
f'JOIN {codes_table} c2 ON s.SplitID=c2.SplitID ' + \
f'JOIN {codes_table} c3 ON s.SplitID=c3.SplitID ' + \
f'WHERE s.GameID=%s AND s.SeatID=%s ' + \
f'AND c1.SetNo=1 AND c2.SetNo=2 AND c3.SetNo=3'
for game_id in range(start_game_id, end_game_id + 1):
hands = next(self.yield_dealt_hands_from_db(game_id, game_id))[1]
game_splits = []
for seat_id in range(1, 5):
query = base_query % (game_id, seat_id)
cards = hands[seat_id - 1]
db_output, _ = DBF.select_query(query)
hand_splits = []
for row in db_output:
split_seq_no, split_str, s1c1, s1c2, s1c3, s1c4, s1c5, s1c6, s2c1, s2c2, s2c3, s2c4, s2c5, s2c6, s3c1, s3c2, s3c3, s3c4, s3c5, s3c6 = row
split_cards = self._convert_split_str_to_split_cards(
cards, split_str)
s1code = [s1c1, s1c2, s1c3, s1c4, s1c5, s1c6]
s2code = [s2c1, s2c2, s2c3, s2c4, s2c5, s2c6]
s3code = [s3c1, s3c2, s3c3, s3c4, s3c5, s3c6]
s1code = CardGroupCode(
[code for code in s1code if code is not None])
s2code = CardGroupCode(
[code for code in s2code if code is not None])
s3code = CardGroupCode(
[code for code in s3code if code is not None])
split_info_factory = ChinesePokerStrategy.ranked_split_info_factory
split_info = split_info_factory(
None,
split_cards,
(s1code, s2code, s3code),
None,
None,
None,
None,
None,
split_seq_no,
)
hand_splits.append(split_info)
game_splits.append(hand_splits)
yield game_id, game_splits
# TODO Convert each row to RankedSplitInfo named tuple
# (Inds, Cards, Codes, )
return
def update_CPT_submit_score(app_game_ID, name, email=None):
query = f"UPDATE {CPT_game_table} SET Name='{name}', Email='{email}', InLeaderboard=1 WHERE AppGameID={app_game_ID}"
_ = DBF.insert_query(query, None, False, False)
return
def end_of_game_CPT_game_row_update(app_game_ID, n_rounds, tot_score,
tot_CPT_score):
cur_datetime = datetime.utcnow().strftime(GConst.SQL_DATETIME_FORMAT)
query = f"UPDATE {CPT_game_table} SET NoRounds={n_rounds}, TotScore={tot_score}, TotCptScore={tot_CPT_score}, EndTimeUTC='{cur_datetime}' WHERE AppGameID={app_game_ID}"
_ = DBF.insert_query(query, None, False, False)
return
def update_CPT_round_row_with_game_scores(app_round_ID, game_scores,
player_score_com_100):
cur_datetime = datetime.utcnow().strftime(GConst.SQL_DATETIME_FORMAT)
query = f"UPDATE {CPT_rounds_table} SET PlayerScore = {game_scores[0]}, Com1Score = {game_scores[1]}, Com2Score = {game_scores[2]}, Com3Score = {game_scores[3]}, PlayerScoreCom100 = {player_score_com_100}, EndTimeUTC = '{cur_datetime}' WHERE RoundID = {app_round_ID}"
_ = DBF.insert_query(query, None, False, False)
return
def update_CPT_round_row_with_player_split(app_round_ID, split_inds):
split_str = DF._convert_card_inds_to_set_inds_str(split_inds)
query = f"UPDATE {CPT_rounds_table} SET PlayerSplitInds = {split_str} WHERE RoundID = {app_round_ID}"
#print(query)
_ = DBF.insert_query(query, None, False, False)
return