import cytoolz.curried from typing import List import os import sys if os.getenv('MY_PYTHON_PKG') not in sys.path: sys.path.append(os.getenv('MY_PYTHON_PKG')) import syspath from common.connection import conn_local_lite, conn_local_pg import dftosql import sqlCommand as sqlc from sqliteToPostgres.update import utils 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)
import os import sys if os.getenv('MY_PYTHON_PKG') not in sys.path: sys.path.append(os.getenv('MY_PYTHON_PKG')) import syspath from common.connection import conn_local_lite, conn_local_pg import dftosql import sqlCommand as sqlc from sqliteToPostgres.update import utils # --inc-- # connect conn_lite = conn_local_lite('mops.sqlite3') conn_pg = conn_local_pg('mops') cur = conn_pg.cursor() curLite = conn_lite.cursor() table = 'ifrs前後-綜合損益表' columns = list( pd.read_sql_query("SELECT * FROM '{}' limit 1".format(table), conn_lite)) varchar_columns = ['年', '季', '公司代號', '公司名稱'] real_columns = list(filter(lambda x: x not in varchar_columns, columns)) types = {'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)
from typing import List import os import sys if os.getenv('MY_PYTHON_PKG') not in sys.path: sys.path.append(os.getenv('MY_PYTHON_PKG')) import syspath from common.connection import conn_local_lite, conn_local_pg import dftosql import sqlCommand as sqlc from sqliteToPostgres.update import utils conn_lite = conn_local_lite('bic.sqlite3') conn_pg = conn_local_pg('bic') 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),
import pandas as pd import numpy as np import os import sys if os.getenv('MY_PYTHON_PKG') not in sys.path: sys.path.append(os.getenv('MY_PYTHON_PKG')) import syspath from common.connection import conn_local_lite, conn_local_pg import sqlCommand as sqlc # connect conn_lite = conn_local_lite('TEJ.sqlite3') conn_pg = conn_local_pg('tej') # read from sqlite sql = "SELECT * FROM '{}'" tablename = 'tse_ch' ac = pd.read_sql_query(sql.format(tablename), conn_lite).replace('--', np.nan) ac.dtypes columns = list(ac) varcharColumns = ['公司代號', '公司名稱', '產業別'] realColumns = list(filter(lambda x: x not in varcharColumns, columns)) ac[varcharColumns] = ac[varcharColumns].astype(str) ac[realColumns] = ac[realColumns].astype(float) # create table columns = varcharColumns + realColumns fieldTypes = ['varchar' for col in varcharColumns] + ['real' for col in realColumns]
rows = [key for key in rows1 if key not in rows2] for row in rows: sql = 'SELECT * FROM "{}" where {}'.format(tablename, join(' and ', ['"{0}"="{1}"'.format(key, value) for key, value in row.items()])) df = pd.read_sql_query(sql, conn_lite) sqlc.insertDataPostgre(tablename, transform(date_columns, varchar_columns, real_columns, df), conn_pg) ## --- read from sqlite --- # --- report --- # --inc-- # connect conn_lite = conn_local_lite('mops.sqlite3') conn_pg = conn_local_pg('mops') cur_pg = conn_pg.cursor() cur_lite = conn_lite.cursor() # cur.execute('SELECT * FROM "ifrs前後-綜合損益表"') # conn_pg.commit() # read from sqlite sql = "SELECT * FROM '{}'" tablename = 'ifrs前後-綜合損益表' inc = pd.read_sql_query(sql.format(tablename), conn_lite).replace('--', np.nan) inc.dtypes columns = list(pd.read_sql_query("SELECT * FROM '{}' limit 1".format(tablename), conn_lite)) varchar_columns = ['年', '季', '公司代號', '公司名稱'] real_columns = list(filter(lambda x: x not in varchar_columns, columns)) inc[varchar_columns] = inc[varchar_columns].astype(str)
import pandas as pd import numpy as np import cytoolz.curried import datetime as dt import os import sys if os.getenv('MY_PYTHON_PKG') not in sys.path: sys.path.append(os.getenv('MY_PYTHON_PKG')) import syspath from common.env import PG_PWD, PG_PORT, PG_USER from common.connection import conn_local_pg import sql.pg as pg # from sql.pg import select, insert, delete tse = conn_local_pg('tse') def mysum(): df = pg.saw('mysum', {'證券代號':'5522'}).df(tse) print(df) return df
if os.getenv('MY_PYTHON_PKG') not in sys.path: sys.path.append(os.getenv('MY_PYTHON_PKG')) import syspath from common.connection import conn_local_lite, conn_local_pg import sqlCommand as sqlc import astype as ast import dftosql mode = 'create' ## --- read from sqlite --- #--- summary --- # connect conn_lite = conn_local_lite('summary.sqlite3') conn_pg = conn_local_pg('summary') # --ac-- # read from sqlite sql = "SELECT * FROM '{}'" tablename = '會計師查核報告' ac = pd.read_sql_query(sql.format(tablename), conn_lite).replace('--', np.nan) ac.dtypes columns = list(ac) varcharColumns = [ '年', '季', '公司代號', '公司簡稱', '簽證會計師事務所名稱', '簽證會計師', '簽證會計師.1', '核閱或查核日期', '核閱或查核報告類型' ] realColumns = list(filter(lambda x: x not in varcharColumns, columns)) ac[varcharColumns] = ac[varcharColumns].astype(str)
import os import sys if os.getenv('MY_PYTHON_PKG') not in sys.path: sys.path.append(os.getenv('MY_PYTHON_PKG')) import syspath from common.connection import conn_local_pg listTbSql = '''SELECT table_schema,table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'public' ORDER BY table_schema,table_name;''' for db in ['mops', 'tse', 'summary']: conn = conn_local_pg(db) cur = conn.cursor() cur.execute(listTbSql) tbs = [t[1] for t in cur.fetchall()] for t in tbs: sql = '''select * from "{}" LIMIT 1;'''.format(t) cols = list(pd.read_sql_query(sql, conn)) for col in cols: try: print(t, col) sql = '''update "{0}" set "{1}" = null where "{1}" ='NaN';'''.format( t, col) cur.execute(sql) except Exception as e: print(e) pass