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()