Exemplo n.º 1
0
def season_records():
    # check today to appoint duration
    yesterday = datetime.date.today() - datetime.timedelta(1)
    year = yesterday.strftime("%Y")
    month = yesterday.strftime("%m")
    season_dict = {'3': '01', '6': '02', '9': '03', '12': '04'}
    season = season_dict[month]
    # set moongosdb and postgres connection
    client = mon.mongo_connection('linode1', 'mongo')
    conn_pos = pos.postgres_connection('linode1', 'postgres', 'stock')
    cursor_pos = pos.make_cursor(connection=conn_pos)
    # stock id
    for item in allStockID.all_stock_id():
        stock_id = item['_id']
        # create table if not exists with table name like month+stock_id
        sql = f"""
        create table if not exists season{stock_id} (
        ID varchar(10) primary key,
        duration char(6),
        sum_volumn decimal,
        avg_price decimal,
        avg_open decimal,
        avg_high decimal,
        avg_low decimal,
        avg_close decimal,
        avg_change decimal,
        sum_trade decimal
        );
        """
        pos.createTable(connection=conn_pos, cursor=cursor_pos, sql=sql)
        dur_records = duration_records.get_season_record_mongo(
            stock_id, year, season)
        if dur_records:
            # print('dur', dur_records)
            docs = compute_records.compute_records(dur_records)
            # 將計算好的結果存入postgres
            print(f"{stock_id}: {year}, season: {season}")
            print(docs)
            query = f"""
            INSERT INTO season{stock_id}
            (ID, duration, sum_volumn, sum_trade, avg_price, avg_open, avg_high, avg_low, avg_close, avg_change)
            VALUES
            ('{stock_id+year+season}', '{year+season.zfill(2)}', {docs['sumVolume']}, {docs['sumTrades']}, 
            {docs['avgPrice']}, {docs['avgOpen']}, {docs['avgHigh']},{docs['avgLow']}, {docs['avgClose']}, {docs['avgChange']})
            """
            if not conn_pos:
                conn_pos = pos.postgres_connection('linode1', 'postgres',
                                                   'stock')
                cursor_pos = pos.make_cursor(connection=conn_pos)
            pos.insertTable(connection=conn_pos,
                            cursor=cursor_pos,
                            query=query,
                            exceptionfile='season')
    pos.close_connection(connection=conn_pos)
    mon.close_connection(client=client)
Exemplo n.º 2
0
def season_records():
    # check today to appoint duration
    yesterday = datetime.date.today() - datetime.timedelta(1)
    year = yesterday.strftime("%Y")
    # set moongosdb and postgres connection
    client = mon.mongo_connection('linode1', 'mongo')
    conn_pos = pos.postgres_connection('linode1', 'postgres', 'stock')
    cursor_pos = pos.make_cursor(connection=conn_pos)
    # stock id
    for item in allStockID.all_stock_id():
        stock_id = item['_id']
        for item in allStockID.all_stock_id():
            stock_id = item['_id']
        # create table if not exists with table name like month+stock_id
        sql = f"""
        create table if not exists year{stock_id} (
        ID varchar(8) primary key,
        duration char(4),
        sum_volumn decimal,
        avg_price decimal,
        avg_open decimal,
        avg_high decimal,
        avg_low decimal,
        avg_close decimal,
        avg_change decimal,
        sum_trade decimal
        );
        """
        pos.createTable(connection=conn_pos, cursor=cursor_pos, sql=sql)
        # iterately insert data into table
        for year in range(2010, 2021):
            dur_records = duration_records.get_year_record_mongo(
                stock_id, year)
            if dur_records:
                docs = compute_records.compute_records(dur_records)
                # 將計算好的結果存入postgres
                print(f"{stock_id}: {year}")
                print(docs)
                query = f"""
                INSERT INTO year{stock_id}
                (ID, duration, sum_volumn, sum_trade, avg_price,avg_open, avg_high, avg_low, avg_close, avg_change)
                VALUES
                ('{stock_id+year}', '{year}', {docs['sumVolume']}, {docs['sumTrades']},
                {docs['avgPrice']}, {docs['avgOpen']}, {docs['avgHigh']},{docs['avgLow']}, {docs['avgClose']}, {docs['avgChange']})
                """
                if not conn_pos:
                    conn_pos = pos.postgres_connection('linode1', 'postgres',
                                                       'stock')
                    cursor_pos = pos.make_cursor(connection=conn_pos)
                pos.insertTable(connection=conn_pos,
                                cursor=cursor_pos,
                                query=query,
                                exceptionfile='year')
    pos.close_connection(connection=conn_pos)
    mon.close_connection(client=client)
Exemplo n.º 3
0
def month_records():
    # set moongosdb and postgres connection
    client = mon.mongo_connection('linode1', 'mongo')
    conn_pos = pos.postgres_connection('linode1', 'postgres', 'stock')
    cursor_pos = pos.make_cursor(connection=conn_pos)
    # stock id
    for item in allStockID.all_stock_id():
        stock_id = item['_id']
        # create table if not exists with table name like month+stock_id
        sql = f"""
        create table if not exists month{stock_id} (
        ID varchar(10) primary key,
        duration char(6),
        sum_volumn decimal,
        avg_price decimal,
        avg_open decimal,
        avg_high decimal,
        avg_low decimal,
        avg_close decimal,
        avg_change decimal,
        sum_trade decimal
        );
        """
        pos.createTable(connection=conn_pos, cursor=cursor_pos, sql=sql)
        # iterately insert data into table
        for year in range(2010, 2021):
            for month in range(1, 13):
                dur_records = duration_records.get_month_record_mongo(
                    stock_id, year, month)
                if dur_records:
                    docs = compute_records.compute_records(dur_records)
                    # 將計算好的結果存入postgres
                    print(f"{stock_id}: {str(year)+str(month).zfill(2)}")
                    print(docs)
                    query = f"""
                    INSERT INTO month{stock_id}
                    (ID, duration, sum_volumn, sum_trade, avg_price, avg_open, avg_high, avg_low, avg_close, avg_change)
                    VALUES
                    ('{stock_id+str(year)+str(month).zfill(2)}', '{str(year)+str(month).zfill(2)}', {docs['sumVolume']}, {docs['sumTrades']}, 
                    {docs['avgPrice']}, {docs['avgOpen']}, {docs['avgHigh']},{docs['avgLow']}, {docs['avgClose']}, {docs['avgChange']})
                    """
                    if not conn_pos:
                        conn_pos = pos.postgres_connection(
                            'linode1', 'postgres', 'stock')
                        cursor_pos = pos.make_cursor(connection=conn_pos)
                    pos.insertTable(connection=conn_pos,
                                    cursor=cursor_pos,
                                    query=query,
                                    exceptionfile='oldmonth')
    pos.close_connection(connection=conn_pos)
    mon.close_connection(client=client)