def get_data(): database = DB() q = "SELECT first_name, last_name, rguru_id FROM player_ids WHERE rguru_id IS NOT NULL" ids = database.query(q) results = [] for (fname, lname, id_) in ids: q = "SELECT dk_pts FROM rguru_hitters WHERE id=%s" scores = [ float(x[0]) for x in database.query(q, (str(id_), )) if x[0] is not None ] # dont count if not enough sample size. if len(scores) < 30: continue mean = np.mean(scores) # only look at players w/ avg score > 4 if mean < 4: continue stdev = np.std(scores) results.append((fname, lname, id_, round(mean, 2), round(stdev, 2), round((mean / stdev), 2))) return sorted(results, key=lambda x: x[5], reverse=True)
def query_data(self): """Fetches all records for player id""" db = DB() q = "SELECT bat_order, AB, BB, HBP FROM rguru_hitters where id=%s" res = db.query(q, (self.id_,)) db.finish() return res
def trade_dates(ticker, min_date=None, max_date=None, as_dtime=True): """ Get a list of trade days for provided ticker """ db = DB() query = 'select distinct(date(`dtime`)) as `d` from `quotes` where `ticker` = "%s" ' % ticker if min_date is not None or max_date is not None: if min_date is not None and not isinstance(min_date, datetime.datetime): min_date = datetime.datetime.combine(min_date, datetime.time(0, 0)) if max_date is not None and not isinstance(max_date, datetime.datetime): max_date = datetime.datetime.combine(max_date, datetime.time(23, 59)) if min_date is not None and max_date is not None: query += 'and `dtime` between "%s" and "%s" ' % (min_date, max_date) elif min_date is not None: query += 'and `dtime` >= "%s" ' % min_date else: query += 'and `dtime` <= "%s"' % max_date query += 'order by `d` asc' db.cursor.execute(query) midnight = datetime.time(0, 0) for date in db.cursor: if as_dtime: yield datetime.datetime.combine(date[0], midnight) else: yield date[0] db.close() pass
def fetch_data(site, pos): db = DB() q = "SELECT {0}_pts, {0}_salary FROM rguru_{1}".format(site, pos) points = [] salaries = [] for pts, sal in db.query(q): if pts is None or pts < 0 or sal is None or sal == 0: continue points.append(float(pts)) salaries.append(float(sal)/1000) return salaries, points
def __init__(self, env): """ Parameters ---------- env : str the environment that the current instance is running """ print("[ENDPOINTS] Initializing...") # initialize libraries self._env = env self._db = DB(self._env, self._workers) self._logger = Logger(self._db, self._env) self._crypto = Crypto() # initialize Flask self._app = Flask(__name__) self._app.json_encoder = CustomJSONEncoder self._api = Api(self._app) self._app.before_request(self.detectAuthorization) self._app.after_request(self.finishRequest) for url in self._endpoints: self.addResource(self._endpoints[url], url) print("[ENDPOINTS] Done.")
def __init__(self, player_id, site='dk'): self.db = DB() self.id_ = player_id self.points = self.get_points(player_id, site)
def get_team_abs(self, date, team): db = DB() q = "SELECT name, AB, BB, HBP FROM rguru_hitters WHERE date=%s and team=%s" res = db.query(q, (date, team)) db.finish() return res
def get_dates(self): db = DB() q = "SELECT date FROM rguru_hitters WHERE team = %s" res = db.query(q, (self.team,)) db.finish() return set([x[0] for x in res])
def run(): conn = DB().getConn() while conn: timeNow = time.time() if ((round(timeNow) % 15) == 0): save_data(conn, round(timeNow))
data = result_queue.get(20) #60s no data come , seem write done if count == 10: count = 0 logger.info("insert result: %s", db.excutemany(SQLMgr.insert_stock_xg_many(), write_list)) write_list = [] count += 1 write_list.append(data) except Exception as e: logger.info("db break %s", e) break #result_queue.task_done() if __name__ == "__main__": Log("test.log") try: db = DB() all_data = db.query(SQLMgr.get_all_kline_info(Tools.get_today_str())) if all_data: for row in all_data: task_queue.put(row) print all_data worker_thread_num = 4 worker_thread_list = [] for i in range(worker_thread_num): t = threading.Thread(target=worker, args=(task_queue, result_queue)) worker_thread_list.append(t) database_thread = threading.Thread(target=db_writer, args=(result_queue, db)) database_thread.setDaemon(True) database_thread.start() #
def run(): conn = DB().getConn() while conn: save_data(conn)
def get(columns, ticker, dtime_from=None, dtime_to=None, date=None, market_hours=True, order='asc'): """ Get minutely quote values for the given time range or date :param columns: Iterable set of columns that should be obtained :param ticker: Ticker name :param dtime_from: When selecting by time range: Time range start :param dtime_to: When selecting by time range: Time range end :param date: When selecting by date: Date :param market_hours: Should the data be obtained only within market hours? :param order: Records datetime order: 'asc' - old first, 'desc' - new first :return: Iterable generator with tuples for dtime + specified columns """ if Data._db is None: Data._db = DB().db Data._cursor = Data._db.cursor() if isinstance(columns, str): # Specified indicators group: need to get all of them columns = Config.get('dbstructure.' + columns).keys() # MySQL tables that will be used and inner columns tables = [] query_columns = [] for column in columns: for table, table_indicators in Config.get('dbstructure').items(): if column in table_indicators: if len(query_columns) == 0: query_columns.append('`%s`.`dtime`' % table) query_columns.append('`%s`.`%s`' % (table, column)) if table not in tables: tables.append(table) break if len(tables) == 0: return query = 'select ' + ', '.join(query_columns) + ' from `' + '`, `'.join( tables) + '` ' query += 'where `%s`.`ticker` = "%s" ' % (tables[0], ticker) if date is not None: if isinstance(date, datetime.date): date = datetime.datetime.combine(date, datetime.time(0, 0)) elif isinstance(date, str): date = datetime.datetime.strptime(date, '%Y-%m-%d') if market_hours: dtime_from = tradetime.daystart(date) dtime_to = tradetime.dayend(date) else: dtime_from = date.replace(hour=0, minute=0) dtime_to = date.replace(hour=23, minute=59) query += 'and `%s`.`dtime` between "%s" and "%s" ' % ( tables[0], dtime_from, dtime_to) if market_hours and date is None: query += 'and time(`%s`.`dtime`) between "%s" and "%s" ' % ( tables[0], tradetime.start, tradetime.end) # Joining tables for table in tables: if table == tables[0]: continue query += 'and `%s`.`ticker` = `%s`.`ticker` ' % (table, tables[0]) query += 'and `%s`.`dtime` = `%s`.`dtime` ' % (table, tables[0]) query += 'order by `dtime` ' + order Data._cursor.execute(query) for entry in Data._cursor: yield entry Data._db.commit()
def run(): conn = DB().getConn() while conn: retry_data(conn) time.sleep(conf['retryInterval'])