示例#1
0
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)
示例#2
0
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)
示例#3
0
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),
示例#4
0
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]
示例#5
0
    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)
示例#6
0
文件： mysum.py 项目： litefunc/web 
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
示例#7
0
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)
示例#8
0
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