cur = conn_pg.cursor() curLite = conn_lite.cursor() table = '景氣指標及燈號-綜合指數' columns = list( pd.read_sql_query("SELECT * FROM '{}' limit 1".format(table), conn_lite)) date_columns = [] varchar_columns = ['年月', '年', '月'] real_columns = list( filter(lambda x: x not in (date_columns + varchar_columns), columns)) types = {'date': date_columns, 'str': varchar_columns, 'float': real_columns} cols_dist = ['年月'] rows1 = cytoolz.compose(utils.to_dict, utils.as_type(types), sqlc.s_dist_lite(conn_lite, table))(cols_dist) rows2 = cytoolz.compose(utils.to_dict, utils.as_type(types), sqlc.s_dist_pg(conn_pg, table))(cols_dist) rows = utils.diff(rows1, rows2) @cytoolz.curry def transform(dtypes: dict, df: pd.DataFrame) -> pd.DataFrame: df = df.replace('--', 0).replace('NaN', 0).fillna(0) df = utils.as_type(dtypes, df) return df def read_insert(row: list) -> List: return cytoolz.compose(dftosql.i_pg_batch(conn_pg, table), transform(types),
conn_pg = conn_local_pg('tse') conn_lite = conn_local_lite('tse.sqlite3') cur = conn_pg.cursor() curLite = conn_lite.cursor() table = '除權息計算結果表' columns = list(pd.read_sql_query("SELECT * FROM '{}' limit 1".format(table), conn_lite)) date_columns = ['年月日'] varchar_columns = ['證券代號', '證券名稱'] real_columns = list(filter(lambda x: x not in (date_columns + varchar_columns), columns)) types = {'date': date_columns, 'str': varchar_columns, 'float': real_columns} cols_dist = ['年月日'] rows1 = cytoolz.compose(utils.to_dict, utils.as_type(types), sqlc.s_dist_lite(conn_lite, table))(cols_dist) rows2 = cytoolz.compose(utils.to_dict, utils.as_type(types), sqlc.s_dist_pg(conn_pg, table))(cols_dist) rows = utils.diff(rows1, rows2) @cytoolz.curry def transform(dtypes: dict, df: pd.DataFrame) -> pd.DataFrame: df = df.rename(columns={'股票代號': '證券代號', '股票名稱': '證券名稱'}) df = utils.as_type(dtypes, df) return df def read_insert(row: list) -> List: return cytoolz.compose(dftosql.i_pg_batch(conn_pg, table), transform(types), sqlc.s_where_lite(conn_lite, table))(row)
def __days_lite(conn_lite, col: str, table: str) -> Set[dt.datetime]: ser = pd.to_datetime(sqlc.s_dist_lite(conn_lite, table, [col])[col]) return {x.to_pydatetime() for x in ser}