def attempt(interval: int = 1) -> bool: from shared import database, pd_exception try: database.get_database(configuration.get_str('magic_database')) return True except pd_exception.DatabaseConnectionRefusedException: print(f'DB not accepting connections. Sleeping for {interval}.') time.sleep(interval) return False
def cache() -> None: db = database.get_database(configuration.get_str('prices_database')) now = round(time.time()) week = now - 60 * 60 * 24 * 7 month = now - 60 * 60 * 24 * 7 * 30 last_rotation = int(rotation.last_rotation().timestamp()) sql = 'SELECT MAX(`time`) FROM low_price' latest = db.value(sql) db.begin() db.execute('DELETE FROM cache') sql = """ INSERT INTO cache (`time`, name, price, low, high, week, month, season) SELECT MAX(`time`) AS `time`, name, MIN(CASE WHEN `time` = %s THEN price END) AS price, MIN(CASE WHEN `time` > %s THEN price END) AS low, MAX(CASE WHEN `time` > %s THEN price END) AS high, AVG(CASE WHEN `time` > %s AND price = 1 THEN 1 WHEN `time` > %s THEN 0 END) AS week, AVG(CASE WHEN `time` > %s AND price = 1 THEN 1 WHEN `time` > %s THEN 0 END) AS month, AVG(CASE WHEN `time` > %s AND price = 1 THEN 1 WHEN `time` > %s THEN 0 END) AS season FROM low_price GROUP BY name; """ db.execute(sql, [latest, last_rotation, last_rotation, week, week, month, month, last_rotation, last_rotation]) db.commit()
def db(): if has_request_context(): ctx = request else: ctx = g if not hasattr(ctx, 'database'): ctx.database = get_database(configuration.get('decksite_database')) return ctx.database
def setup(): db = getattr(setup, "db", None) if db is not None: db.execute("DROP TABLE IF EXISTS x") else: db = get_database(location()) db.execute('CREATE TABLE IF NOT EXISTS x (id INTEGER PRIMARY KEY, v TEXT)') return db
def info_cached(card: Card = None, name: str = None) -> Optional[PriceDataType]: if name is None and card is not None: name = card.name sql = 'SELECT `time`, low / 100.0 AS low, high / 100.0 AS high, price / 100.0 AS price, week, month, season FROM cache WHERE name = %s' db = database.get_database(configuration.get_str('prices_database')) try: return db.select(sql, [name])[0] # type: ignore except IndexError: return None
def info_cached(card=None, name=None): if name is None and card is not None: name = card.name sql = 'SELECT `time`, low / 100.0 AS low, high / 100.0 AS high, price / 100.0 AS price, week, month, season FROM cache WHERE name = %s' db = database.get_database(configuration.get('prices_database')) try: return db.execute(sql, [name])[0] except IndexError: return None
def db() -> Database: if flask.current_app: context = flask.g else: context = DATABASE if hasattr(context, 'magic_database'): return context.get('magic_database') context.magic_database = get_database(configuration.get_str('magic_database')) init() return context.get('magic_database')
def db() -> Database: if has_request_context(): ctx = request elif g: ctx = g else: ctx = Container() # Fallback context for testing. if not hasattr(ctx, 'database'): ctx.database = get_database(configuration.get_str('decksite_database')) return ctx.database
def db() -> Database: if has_request_context(): # type: ignore ctx = request elif g: ctx = g else: ctx = TEST_CONTEXT # Fallback context for testing. if not hasattr(ctx, 'database'): ctx.database = get_database( configuration.get_str('decksite_database')) # type: ignore return ctx.database # type: ignore
import datetime import itertools import sys from typing import Dict, List, Optional import ftfy from magic import multiverse, oracle, rotation from price_grabber import parser, price from shared import configuration, dtutil, fetch_tools from shared.database import get_database from shared.pd_exception import DatabaseException, TooFewItemsException DATABASE = get_database(configuration.get_str('prices_database')) def run() -> None: multiverse.init() oracle.init() fetch() price.cache() def fetch() -> None: all_prices, timestamps = {}, [] ch_urls = configuration.get_list('cardhoarder_urls') if ch_urls: for _, url in enumerate(ch_urls): s = fetch_tools.fetch(url) s = ftfy.fix_encoding(s) timestamps.append(
def db() -> Database: if not DATABASE.get('logsite_database'): DATABASE.logsite_database = get_database( configuration.get_str('logsite_database')) return DATABASE.logsite_database
default = 'DEFAULT {default}'.format(default=prop['default']) if prop['default'] is not None else '' unique = 'UNIQUE' if prop['unique'] else '' if prop['type'].startswith('VARCHAR') or prop['type'] == 'LONGTEXT': prop['type'] = 'TEXT' if prop['type'] == 'BOOLEAN': prop['type'] = 'INTEGER' return '`{name}` {type} {primary_key} {nullable} {unique} {default}'.format(name=name, type=prop['type'], primary_key=primary_key, nullable=nullable, unique=unique, default=default) elif db().is_mysql(): nullable = 'NOT NULL' if not prop['nullable'] else '' primary_key = 'PRIMARY KEY AUTO_INCREMENT' if prop['primary_key'] else '' default = 'DEFAULT {default}'.format(default=prop['default']) if prop['default'] is not None else '' unique = 'UNIQUE' if prop['unique'] else '' return '`{name}` {type} {nullable} {primary_key} {unique} {default}'.format(name=name, type=prop['type'], primary_key=primary_key, nullable=nullable, unique=unique, default=default) def foreign_key_def(name, fk): return 'FOREIGN KEY(`{name}`) REFERENCES `{table}`(`{column}`)'.format(name=name, table=fk[0], column=fk[1]) def create_table_def(name, props): sql = 'CREATE TABLE IF NOT EXISTS `{name}` (' sql += ', '.join(column_def(name, prop) for name, prop in props.items()) fk = ', '.join(foreign_key_def(name, prop['foreign_key']) for name, prop in props.items() if prop['foreign_key']) if fk: sql += ', ' + fk sql += ')' if db().is_mysql(): sql += ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' return sql.format(name=name) DATABASE = get_database(configuration.get('magic_database')) init()
def setup(): db().execute( 'CREATE TABLE IF NOT EXISTS db_version (version INTEGER UNIQUE NOT NULL)' ) version = db_version() patches = os.listdir('decksite/sql') patches.sort(key=lambda n: int(n.split('.')[0])) for fn in patches: path = os.path.join('decksite/sql', fn) n = int(fn.split('.')[0]) if version < n: print("Patching database to v{0}".format(n)) fh = open(path, 'r') sql = fh.read() for stmt in sql.split(';'): if stmt.strip() != "": db().execute(stmt) fh.close() db().execute( "INSERT INTO db_version (version) VALUES ({n})".format(n=n)) def db_version() -> int: return db().value( 'SELECT version FROM db_version ORDER BY version DESC LIMIT 1', [], 0) DATABASE = get_database(configuration.get('decksite_database')) setup()