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}
