import pandas as pd
import numpy as np
import csv
from datetime import datetime
from datetime import date
import calendar
import requests
import pyodbc
import os
import pickle
import copy
import warnings
warnings.filterwarnings('ignore')
def get_login():
server = ''
db = ''
uid = ''
passwd = ''
with open("C:\\Users\\chris\\Documents\\GitHub\\Jupyter-Notebooks\\azure.txt") as f:
lines = f.read().split("\n")
for i in lines:
if i.split("=")[0] == "server":
server = i.split("=")[1]
elif i.split("=")[0] == "db":
db = i.split("=")[1]
elif i.split("=")[0] == "uid":
uid = i.split("=")[1]
elif i.split("=")[0] == "passwd":
passwd = i.split("=")[1]
return (server, db, uid, passwd)
def header(table):
if table == "Matches":
return ["Match_ID","Draft_ID","P1","P1_Arch","P1_Subarch","P2","P2_Arch","P2_Subarch","P1_Roll","P2_Roll",
"Roll_Winner","P1_Wins","P2_Wins","Match_Winner","Format","Limited_Format","Match_Type","Date"]
elif table == "Games":
return ["Match_ID","P1","P2","Game_Num","PD_Selector","PD_Choice","On_Play","On_Draw","P1_Mulls",
"P2_Mulls","Turns","Game_Winner"]
elif table == "Plays":
return ["Match_ID","Game_Num","Play_Num","Turn_Num","Casting_Player","Action","Primary_Card","Target1",
"Target2","Target3","Opp_Target","Self_Target","Cards_Drawn","Attackers","Active_Player","Nonactive_Player"]
elif table == "Drafts":
return ["Draft_ID","Hero","Player_2","Player_3","Player_4","Player_5","Player_6","Player_7","Player_8",
"Match_Wins","Match_Losses","Format","Date"]
elif table == "Picks":
return ["Draft_ID","Card","Pack_Num","Pick_Num","Pick_Ovr","Avail_1","Avail_2","Avail_3","Avail_4","Avail_5",
"Avail_6","Avail_7","Avail_8","Avail_9","Avail_10","Avail_11","Avail_12","Avail_13","Avail_14"]
return []
def invert_join(ad):
# Input: List[List[Matches],List[Games],List[Plays]]
# Output: List[List[Matches],List[Games],List[Plays]]
def swap_cols(data,header,col_a,col_b):
# Input: List[Matches or Games],List[Headers],String,String
# Output: List[Matches]
for index,i in enumerate(header):
if i == col_a:
a = index
elif i == col_b:
b = index
data[a], data[b] = data[b], data[a]
def invert_matchdata(data):
# Input: List[Matches]
# Output: List[Matches]
swap_cols(data,header("Matches"),"P1","P2")
swap_cols(data,header("Matches"),"P1_Arch","P2_Arch")
swap_cols(data,header("Matches"),"P1_Subarch","P2_Subarch")
swap_cols(data,header("Matches"),"P1_Roll","P2_Roll")
swap_cols(data,header("Matches"),"P1_Wins","P2_Wins")
cols_to_invert = ["Match_Winner","Roll_Winner"]
for i in cols_to_invert:
for index,j in enumerate(header("Matches")):
if j == i:
a = index
if data[a] == "P1":
data[a] = "P2"
elif data[a] == "P2":
data[a] = "P1"
def invert_gamedata(data):
# Input: List[Games]
# Output: List[Games]
swap_cols(data,header("Games"),"P1","P2")
swap_cols(data,header("Games"),"P1_Mulls","P2_Mulls")
swap_cols(data,header("Games"),"On_Play","On_Draw")
cols_to_invert = ["PD_Selector","Game_Winner"]
for i in cols_to_invert:
for index,j in enumerate(header("Games")):
if j == i:
a = index
if data[a] == "P1":
data[a] = "P2"
elif data[a] == "P2":
data[a] = "P1"
ad_inverted = copy.deepcopy(ad)
for i in ad_inverted[0]:
invert_matchdata(i)
for i in ad_inverted[1]:
invert_gamedata(i)
ad_inverted[0] += ad[0]
ad_inverted[1] += ad[1]
return ad_inverted
def upload_data(cursor, table):
global added
global updated
def get_indexes(pkey_index, row_length):
return (pkey_index, [x for x in range(row_length) if x not in pkey_index])
for i in table:
added = 0
updated = 0
if i == 'Drafts':
pkey_index, value_index = get_indexes(pkey_index=[0], row_length=len(header(table='Drafts')))
columns = header(table='Drafts')
table_name = 'mtgo_drafts'
rows = DRAFTS_TABLE
elif i == 'Picks':
pkey_index, value_index = get_indexes(pkey_index=[0, 4], row_length=len(header(table='Picks')))
columns = header(table='Picks')
table_name = 'mtgo_picks'
rows = PICKS_TABLE
elif i == 'Matches':
pkey_index, value_index = get_indexes(pkey_index=[0], row_length=len(header(table='Matches')))
columns = header(table='Matches')
table_name = 'mtgo_matches'
rows = ALL_DATA[0]
elif i == 'Matches_Inverted':
pkey_index, value_index = get_indexes(pkey_index=[0, 2], row_length=len(header(table='Matches')))
columns = header(table='Matches')
table_name = 'mtgo_matches_inverted'
rows = ALL_DATA_INVERTED[0]
elif i == 'Games':
pkey_index, value_index = get_indexes(pkey_index=[0, 3], row_length=len(header(table='Games')))
columns = header(table='Games')
table_name = 'mtgo_games'
rows = ALL_DATA[1]
elif i == 'Games_Inverted':
pkey_index, value_index = get_indexes(pkey_index=[0, 3, 2], row_length=len(header(table='Games')))
columns = header(table='Games')
table_name = 'mtgo_games_inverted'
rows = ALL_DATA_INVERTED[1]
elif i == 'Plays':
pkey_index, value_index = get_indexes(pkey_index=[0, 1, 2], row_length=len(header(table='Plays')))
columns = header(table='Plays')
table_name = 'mtgo_plays'
rows = ALL_DATA[2]
for row in rows:
insert_str = f'INSERT INTO {table_name} ('
for index,i in enumerate(columns):
insert_str += f'{i}'
if index != len(row)-1:
insert_str += ', '
insert_str += ') VALUES ('
for i in range(len(row)):
insert_str += '?'
if i != len(row)-1:
insert_str += ', '
insert_str += ')'
#print(insert_str)
#print(tuple(row))
update_str = f'UPDATE {table_name} SET '
for index,i in enumerate(value_index):
update_str += columns[i] + ' = '
if type(row[i]) == str:
update_str += "'" + row[i].replace("'", "''") + "'"
else:
update_str += str(row[i])
if index != len(value_index)-1:
update_str += ', '
update_str += ' WHERE '
for index,i in enumerate(pkey_index):
update_str += columns[i] + ' = '
if type(row[i]) == str:
update_str += "'" + row[i] + "'"
else:
update_str += str(row[i])
if index != len(pkey_index)-1:
update_str += ' AND '
#print(update_str)
try:
cursor.execute(insert_str, tuple(row))
added += 1
except:
cursor.execute(update_str)
updated += 1
print(added, updated)
curr_path = os.getcwd()
os.chdir(path='c:\\Users\\chris\\Documents\\GitHub\\MTGO-Tracker\\save\\')
ALL_DATA = pickle.load(open("ALL_DATA","rb"))
DRAFTS_TABLE = pickle.load(open("DRAFTS_TABLE","rb"))
PICKS_TABLE = pickle.load(open("PICKS_TABLE","rb"))
ALL_DATA_INVERTED = invert_join(ALL_DATA)
os.chdir(curr_path)
# Connect to the database
server, db, uid, passwd = get_login()
conn_str = 'DRIVER={SQL Server};SERVER=tcp:' + f'{server};PORT=1433;DATABASE={db};UID={uid};PWD={passwd}'
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
# Iterate over each row and upload to database.
# ['Drafts', 'Picks', 'Matches', 'Matches_Inverted', 'Games', 'Games_Inverted', 'Plays']
upload_data(cursor=cursor, table=['Plays'])
conn.commit()
conn.close()