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'])
