Pull To View

In [6]:

    

import sqlite3
import pandas as pd


    

In [7]:

    

data_db = 'data.db'
view_db = 'view.db'


    

In [8]:

    

# While True:

# select row count from viewpoint and data

connV = sqlite3.connect(view_db)
df_dviewpoint = pd.read_sql("SELECT * from vViewpoint",connV, index_col = 'vViewpointId') # where dTable = dPrice
#vpricepos = df_dviewpoint.iloc[0,4] # need to add more where clauses
connV.close()
dPriceRow = df_dviewpoint['row'].values[0]


# select relvant data from D and write to V
connD = sqlite3.connect(data_db)
df_d2vprice = pd.read_sql("SELECT * from dPrice where dPriceId > %s" %dPriceRow, connD)
print df_d2vprice
connD.close()


if not df_d2vprice.empty:
    # in a cursor update the view and the counter

    prices = df_d2vprice[['iDate','symbol','roll_ave']]
    
    # get max id
    max_dPrice_id = df_d2vprice['dPriceId'].max()
    print 'max_dPrice_id', max_dPrice_id

    prices = prices.values.tolist()
 

    connV = sqlite3.connect(view_db)
    cV = connV.cursor()
    cV.executemany('INSERT INTO vPrice (date, symbol, price) VALUES (?,?,?)', prices)
    cV.execute("UPDATE vViewpoint SET row = %s WHERE dtable = 'dPrice'" %max_dPrice_id) #)) #, 'dPrice'))
    for row in cV.execute('SELECT * FROM vPrice'):
        print row

    for row in cV.execute('SELECT * FROM vViewpoint'):
        print row

    connV.commit()
    connV.close()


    

    




Empty DataFrame
Columns: [dPriceId, iPriceId, iDate, pStartDate, pEndDate, date, symbol, bid, roll_ave, ask]
Index: []

In [ ]:

    




    

In [ ]:

    




    

In [ ]: