G4jPXu4g

1import random
2
3from enum import Enum
4
5import pymysql
6import pymysql.cursors
7
8import stats
9
10MYSQL_HOST = 'localhost'
11MYSQL_DB = 'tictactoe'
12MYSQL_USER = 'tictactoe'
13MYSQL_PASS = 'password123'
14
15INITIALIZE_TABLE_SQL		 = ("CREATE TABLE IF NOT EXISTS move_records ( "
16								"`board` VARCHAR(27) NOT NULL, "
17								"`move_column` VARCHAR(1) NOT NULL, " # TODO: enum
18								"`move_row` VARCHAR(1) NOT NULL, " # TODO: enum
19								"`wins` INT UNSIGNED NOT NULL DEFAULT 0, "
20								"`losses` INT UNSIGNED NOT NULL DEFAULT 0, "
21								"`draws` INT UNSIGNED NOT NULL DEFAULT 0, "
22								"PRIMARY KEY (`board`, `move_column`, `move_row`), " # ~90% of queries will use these unique parameters
23								"INDEX (`board`)" # the other ~10% of queries will be SELECT statements with `board` in a WHERE clause
24								" )ENGINE=InnoDB")
25
26INCREMENT_MOVE_WINS_SQL		 = ("INSERT INTO move_records(`board`, `move_column`, `move_row`, `wins`) "
27								"VALUES (%(board)s, %(move_column)s, %(move_row)s, 1) "
28								"ON DUPLICATE KEY UPDATE `wins` = (`wins` + 1)")
29
30INCREMENT_MOVE_LOSSES_SQL	 = ("INSERT INTO move_records(`board`, `move_column`, `move_row`, `losses`) "
31								"VALUES (%(board)s, %(move_column)s, %(move_row)s, 1) "
32								"ON DUPLICATE KEY UPDATE `losses` = (`losses` + 1)")
33
34INCREMENT_MOVE_DRAWS_SQL	 = ("INSERT INTO move_records(`board`, `move_column`, `move_row`, `draws`) "
35								"VALUES (%(board)s, %(move_column)s, %(move_row)s, 1) "
36								"ON DUPLICATE KEY UPDATE `draws` = (`draws` + 1)")
37
38GET_MOVES_FOR_BOARD_SQL		 = "SELECT `move_column`, `move_row`, `wins`, `losses`, `draws` FROM move_records WHERE `board` = %(board)s"
39
40class DatabaseManager:
41
42	def __init__(self):
43		self._initialize_database()
44
45	def _initialize_database(self):
46		'''Create necessary database tables if they don't already exist'''
47		self.connection = pymysql.connect(host = MYSQL_HOST,
48										user = MYSQL_USER,
49										password = MYSQL_PASS,
50										db = MYSQL_DB,
51										autocommit = True,
52										cursorclass = pymysql.cursors.DictCursor)
53
54		with self.connection.cursor() as cursor:
55			cursor.execute(INITIALIZE_TABLE_SQL)
56
57		# TODO: database sanity checks
58
59	def increment_move_record(self, board_string, move_column, move_row, result):
60		'''Increment a record in the database for the given board, column, and row for the given result'''
61		values = {
62			'board': board_string,
63			'move_column': move_column,
64			'move_row': move_row,
65		}
66
67		query = {
68			Result.WIN:		 INCREMENT_MOVE_WINS_SQL,
69			Result.LOSS:	 INCREMENT_MOVE_LOSSES_SQL,
70			Result.DRAW:	 INCREMENT_MOVE_DRAWS_SQL
71		}[result] # get the correct incrementing SQL query based on the result
72
73		with self.connection.cursor() as cursor:
74			cursor.execute(query, values) # execute the incrementing query TODO: catch exceptions, return success
75
76	def retrieve_move_records(self, board):
77		'''Retrieve all win-loss-draw records associated with moves related to the given Board-state'''
78		# return format: {(column, row): (wins, losses, draws), ...} # NOTE: this could be a list of tuples?
79		values = {
80			'board': board.to_board_string()
81		}
82
83		with self.connection.cursor() as cursor:
84			results = cursor.execute(GET_MOVES_FOR_BOARD_SQL, values)
85			return {(result['move_column'], result['move_row']): (result['wins'], result['losses'], result['draws']) for result in cursor.fetchall()} # TODO: catch exceptions, return success
86
87	def close(self):
88		self.connection.close() # close the underlying database connection