def get_metagame_comp(date: str, length: int, mtg_format: str) -> List[Tuple[str, int]]:
"""Retrieves the metagame composition, at the archetype level, for the given format starting at 'date' - 'length'
days until 'date'
:param date: date at which query ends
:param length: number of days before given date to start query
:param mtg_format: MTG format to search under
:return: list of archetype name and its metagame percentage
"""
query = au.load_query('query.sql').format(date=date, length=length, format=mtg_format)
return au.generic_search(query)
def get_deck_prices(mtg_format: str, archetype: str) -> pd.DataFrame:
# get raw data
query = au.load_query('query.sql').format(format=mtg_format,
archetype=archetype)
results = au.generic_search(query)
# data frame with correct column types
data_frame = pd.DataFrame(results, columns=['date', 'price'])
data_frame['price'] = pd.to_numeric(data_frame['price'])
data_frame['date'] = pd.to_datetime(data_frame['date'])
# set date as index, interpolate missing dates & prices
data_frame = data_frame.set_index(pd.DatetimeIndex(data_frame['date']))
data_frame = data_frame.resample('D').asfreq()
data_frame['date'] = data_frame.index
data_frame['price'] = data_frame['price'].interpolate('time')
return data_frame
def get_price_and_reprint_info(card: str, paper_prices: bool) -> pd.DataFrame:
"""Retrieves a DataFrame of all the prices associated with the given card, for all associated printings with
scrapped prices
:param card: name of card
:param paper_prices: to retrieve paper or online prices
:return: DataFrame of all pricing info associated with the given card in the database
"""
paper = 't' if paper_prices else 'f'
query = u.load_query('query.sql').format(card=card, paper=paper)
sql_data = u.generic_search(query)
data_frame = pd.DataFrame(
sql_data,
columns=['price_date', 'price', 'set', 'release_date', 'rarity'])
data_frame['price_date'] = pd.to_datetime(
data_frame['price_date']) # set as date type
data_frame['release_date'] = pd.to_datetime(
data_frame['release_date']) # set as date type
data_frame['price'] = pd.to_numeric(data_frame['price'].apply(
func=lambda x: x.replace('$', ''))) # money to float
return data_frame
'FROM events.event_entry e JOIN events.entry_card c ON c.entry_id = e.entry_id '
'HAVING q > 4 ORDER BY u, q DESC')
# Decks from mtgtop8 that have mainboards sizes greater than 60
bad_mainboards = ('SELECT e.url, SUM(c.quantity) q '
'FROM events.event_entry e JOIN events.entry_card c ON c.entry_id = e.entry_id '
'WHERE c.mainboard = \'t\' '
'GROUP BY e.entry_id HAVING SUM(c.quantity) > 60 ORDER BY q DESC')
# Decks from mtgtop8 that have mainboards sizes greater than 60
bad_sideboards = ('SELECT e.url, SUM(c.quantity) q '
'FROM events.event_entry e JOIN events.entry_card c ON c.entry_id = e.entry_id '
'WHERE c.mainboard = \'f\' '
'GROUP BY e.entry_id HAVING SUM(c.quantity) > 15 ORDER BY q DESC')
# Decks FROM mtgtop8 that have have a mislabelled card, labelled as "Unknown Card"
error_decks = ('WITH ids AS ('
'SELECT DISTINCT(e.entry_id) id '
'FROM events.entry_card e '
'WHERE e.card = \'Unknown Card\') '
'SELECT DISTINCT(e.url) '
'FROM events.event_entry e, ids '
'WHERE e.entry_id = ids.id')
# decks from mtgtop8 that don't have a card present in
missing_cards = ('')
if __name__ == '__main__':
for i in generic_search(bad_sideboards):
print(i)
import analysis.utility as u
"""Collection of queries showing errors / malformed data in the database"""
if __name__ == '__main__':
for i in u.generic_search(u.load_query('untracked_sets.sql')):
print(i)