def _day_metrics(order_book_id: str, latest_date=None):
innercode = orderbookid_map().get(order_book_id)
if innercode is None:
raise RuntimeError("order_book_id %s can not get corresponding inner "
"code from pgenius database." % order_book_id)
condition = (Day.inner_code_ == innercode) & (Day.filter_conditions_())
if latest_date is not None:
condition &= (day_fd.trd_date > latest_date)
src_conn = get_source_connect()
with MySQLDictCursorWrapper(src_conn) as cursor:
cursor.execute(
*query.fields(
Day.metrics()
).tables(
day_fd
).where(
condition
).order_by(
Day.trade_date.desc()
).select()
)
ret = cursor.fetchall()
src_conn.close()
return ret
def get_innercode_map():
sql, params = _get_code_map(stk_code.inner_code, stk_code.stockcode)
src_conn = get_source_connect()
with MySQLDictCursorWrapper(src_conn) as cursor:
cursor.execute(sql, params)
result = cursor.fetchall()
code_map = {}
for r in result:
code_map[r["inner_code"]] = r["stockcode"]
src_conn.close()
return code_map
def _update_by_mtime(self):
src_conn = get_source_connect()
src_cursor = src_conn.cursor(dictionary=True)
for table, clazz in QUARTER_TABLES_MAP.items():
mtimes = self._table_mtime_map[table]
while len(mtimes) != 0:
select_sql, select_param = query.fields(
clazz.metrics()).tables(table).where(
clazz.filter_conditions_()
& table.mtime.contains(mtimes.pop()['modified_time'])
).select()
src_cursor.execute(select_sql, select_param)
self._exec_update(src_cursor.fetchall())
src_cursor.close()
src_conn.close()
def _all_mtime(start_date):
ret = {}
src_conn = get_source_connect()
for table, clazz in QUARTER_TABLES_MAP.items():
if start_date is None:
sql = 'select distinct date_format(mtime, \'%Y-%m-%d\') as ' \
'modified_time from {0} order by mtime ' \
'asc;'.format(clazz.name_(), start_date)
else:
sql = 'select distinct date_format(mtime, \'%Y-%m-%d\') as ' \
'modified_time from {0} where mtime >= \'{1}\' ' \
'order by mtime asc;'.format(clazz.name_(), start_date)
with MySQLDictCursorWrapper(src_conn) as src_cursor:
src_cursor.execute(sql)
ret[table] = src_cursor.fetchall()
src_conn.close()
return ret
def _closing_price(order_book_id):
innercode = orderbookid_map().get(order_book_id)
if innercode is None:
raise RuntimeError("order_book_id %s can not get corresponding inner "
"code from pgenius database." % order_book_id)
src_conn = get_source_connect()
with MySQLDictCursorWrapper(src_conn) as cursor:
cursor.execute(
*query.fields(
stk_market.tradedate,
stk_market.tclose
).tables(
stk_market
).where(
(stk_market.inner_code == innercode) &
(stk_market.isvalid == 1)
).order_by(
stk_market.tradedate.desc()
).select()
)
ret = cursor.fetchall()
src_conn.close()
return ret
def _first_update(self):
src_conn = get_source_connect()
src_cursor = src_conn.cursor(dictionary=True)
comcodes = comecode_map()
for comcode in comcodes:
print(datetime.datetime.now(), comcode)
merged_records = {}
for table, clazz in QUARTER_TABLES_MAP.items():
select_sql, select_param = query.fields(
clazz.metrics()).tables(
table).where((clazz.comcode == comcode)
& clazz.filter_conditions_()).select()
src_cursor.execute(select_sql, select_param)
records = src_cursor.fetchall()
for record in records:
enddate = record.get('end_date')
kept_record = merged_records.get((comcode, enddate))
if kept_record is None:
merged_records[(comcode, enddate)] = record
else:
kept_record.update(record)
self._exec_update(merged_records.values(), duplicate_update=False)
src_cursor.close()
src_conn.close()