06 Pull To View

In [9]:

    

import pandas as pd


    

In [ ]:

    




    

In [10]:

    

import medareda_lib

def get_conn():
    return medareda_lib.get_conn()


    

In [11]:

    

def get_max_pulled_data_row():
    '''Get the number of the last data pulled to view'''
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("SELECT row FROM vViewpoint where dtable = 'dPrice'")
    max_processed_data_row = cur.fetchall()[0][0]
    #print 'max_processed_data_row',max_processed_data_row
    conn.commit()
    cur.close()
    conn.close()
    #print 'max_processed_data_row', max_processed_data_row
    return max_processed_data_row

#get_max_processed_data_row()


    

In [12]:

    

def get_rows_to_pull(max_processed_data_row):
    conn = get_conn()
    cur = conn.cursor()
    sql = "SELECT * FROM dPrice where dPriceId > %s" %max_processed_data_row
    cur.execute(sql)
    rows_to_process = cur.fetchall()
    #print 'rows_to_process',rows_to_process
    conn.commit()
    cur.close()
    conn.close()
    return rows_to_process


#get_rows_to_process(0)


    

In [13]:

    

def pull_to_view():  
    #print '............'
    max_pulled_data_row = get_max_pulled_data_row()
    rows_to_pull = get_rows_to_pull(max_pulled_data_row)
    if not rows_to_pull:
        #print 'no data to pull'
        return
    #print 'rows_to_pull',rows_to_pull
    
    max_dPrice_id = rows_to_pull[len(rows_to_pull)-1][0]
    #print 'max_dPrice_id',max_dPrice_id
    
    
    update_data = []
    for row_to_process in rows_to_pull: #df_d2vprice: #empty

        #print '# in a cursor update the view and the counter'
        #print row_to_process 
        insert_row_data = {}        
    
        #prices = df_d2vprice[['iDate','symbol','price','combined_rate']]
        iDate = row_to_process[3]
        symbol = row_to_process[6]
        price = row_to_process[7] # bid
        combined_rate = row_to_process[8]
        
        #print iDate, symbol, roll_ave
        insert_row_data['iDate'] = iDate
        insert_row_data['symbol'] = symbol
        insert_row_data['price'] = price
        insert_row_data['combined_rate'] = combined_rate
        
        update_data.append(insert_row_data)
        # get max id
        #max_dPrice_id = df_d2vprice['dPriceId'].max()
        #print 'max_dPrice_id', max_dPrice_id

        
    if update_data:
        #print 'update_vPrice', update_data
        connV = get_conn()
        cV = connV.cursor()
        cV.executemany('INSERT INTO vPrice (date, symbol, price, combinedrate) VALUES (%(iDate)s, %(symbol)s, %(price)s, %(combined_rate)s )', update_data)

        cV.execute("UPDATE vViewpoint SET row = %s WHERE dtable = 'dPrice'" %max_dPrice_id) #)) #, 'dPrice'))

        connV.commit()
        connV.close()


    

In [14]:

    

while True:
    pull_to_view()


    

    




---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
<ipython-input-14-0ac7b2e55bc9> in <module>()
      1 while True:
----> 2     pull_to_view()

<ipython-input-13-2fefdba284f9> in pull_to_view()
      3 def pull_to_view():
      4     #print '............'
----> 5     max_pulled_data_row = get_max_pulled_data_row()
      6     rows_to_pull = get_rows_to_pull(max_pulled_data_row)
      7     if not rows_to_pull:

<ipython-input-11-f671bc6cdda7> in get_max_pulled_data_row()
      6     max_processed_data_row = cur.fetchall()[0][0]
      7     #print 'max_processed_data_row',max_processed_data_row
----> 8     conn.commit()
      9     cur.close()
     10     conn.close()

DatabaseError: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]: