07b View

In [1]:

    

#%matplotlib inline

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.animation as animation
import datetime


    

In [2]:

    

import psycopg2
import urlparse


    

In [3]:

    

view_db = 'view.db'


    

In [4]:

    

DATABASE_URL= r"postgres://zpptclkw:_mnlCBXoH7PlxS6vGLC0lYfn3gEw5rpY@qdjjtnkv.db.elephantsql.com:5432/zpptclkw"
urlparse.uses_netloc.append("postgres")
url = urlparse.urlparse(DATABASE_URL)

conn = psycopg2.connect(database=url.path[1:],
  user=url.username,
  password=url.password,
  host=url.hostname,
  port=url.port
)


    

In [5]:

    

fig1 = plt.figure()
fig1.suptitle('GBP/USD price')
fig1.autofmt_xdate()

ax1 = fig1.add_subplot(1,1,1)

global g_conn
g_conn = psycopg2.connect(database=url.path[1:],
        user=url.username,
        password=url.password,
        host=url.hostname,
        port=url.port
        )

print g_conn


    

    




<connection object at 0x7ff60ce55a00; dsn: 'dbname=zpptclkw user=zpptclkw password=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx host=qdjjtnkv.db.elephantsql.com port=5432', closed: 0>

In [6]:

    

c = g_conn.cursor()
print c
    #df = pd.read_sql("SELECT count(*), status from iPrice group by status",conn) #,conn,parse_dates={'date':'%Y-%m-%d'})
    #results = c.execute('SELECT  date as "[timestamp]", price from vPrice ') 
results = c.execute('SELECT * from vPrice ') 
c.close()
print results


    

    




<cursor object at 0x112f14718; closed: 0>
None

In [7]:

    

DATABASE_URL= r"postgres://zpptclkw:_mnlCBXoH7PlxS6vGLC0lYfn3gEw5rpY@qdjjtnkv.db.elephantsql.com:5432/zpptclkw"
urlparse.uses_netloc.append("postgres")
url = urlparse.urlparse(DATABASE_URL)

connpg = psycopg2.connect(database=url.path[1:],
  user=url.username,
  password=url.password,
  host=url.hostname,
  port=url.port
)


if 1:
    curpg = connpg.cursor()
    curpg.execute("SELECT * FROM vPrice;")
    print 'PG', curpg.fetchall()
    
    connpg.commit()
    curpg.close()
    connpg.close()


    

    




PG [(1, datetime.datetime(2015, 6, 10, 22, 38, 35, 493231), 'GBPUSD', 1.57), (2, datetime.datetime(2015, 6, 10, 22, 38, 35, 493231), 'GBPUSD', 1.57), (3, datetime.datetime(2015, 6, 10, 22, 38, 37, 712068), 'GBPUSD', 1.57), (4, datetime.datetime(2015, 6, 10, 22, 38, 35, 493231), 'GBPUSD', 1.57), (5, datetime.datetime(2015, 6, 10, 22, 38, 37, 712068), 'GBPUSD', 1.57), (6, datetime.datetime(2015, 6, 10, 22, 38, 35, 493231), 'GBPUSD', 1.57), (7, datetime.datetime(2015, 6, 10, 22, 38, 37, 712068), 'GBPUSD', 1.57), (8, datetime.datetime(2015, 6, 10, 22, 38, 39, 942610), 'GBPUSD', 1.57), (9, datetime.datetime(2015, 6, 10, 22, 38, 42, 139516), 'GBPUSD', 1.57), (10, datetime.datetime(2015, 6, 11, 0, 3, 11, 293206), 'GBPUSD', 1.57), (11, datetime.datetime(2015, 6, 11, 0, 3, 13, 493631), 'GBPUSD', 1.57), (12, datetime.datetime(2015, 6, 11, 0, 3, 15, 676744), 'GBPUSD', 1.57), (13, datetime.datetime(2015, 6, 11, 0, 3, 17, 846871), 'GBPUSD', 1.57), (14, datetime.datetime(2015, 6, 11, 0, 3, 20, 59772), 'GBPUSD', 1.57), (15, datetime.datetime(2015, 6, 11, 0, 3, 22, 306707), 'GBPUSD', 1.57), (16, datetime.datetime(2015, 6, 11, 0, 3, 24, 559226), 'GBPUSD', 1.57), (17, datetime.datetime(2015, 6, 11, 0, 17, 16, 65599), 'GBPUSD', 1.57), (18, datetime.datetime(2015, 6, 11, 0, 17, 18, 351726), 'GBPUSD', 1.57), (19, datetime.datetime(2015, 6, 11, 0, 17, 20, 538550), 'GBPUSD', 1.57), (20, datetime.datetime(2015, 6, 11, 0, 17, 22, 740352), 'GBPUSD', 1.57), (21, datetime.datetime(2015, 6, 11, 0, 17, 24, 926125), 'GBPUSD', 1.57), (22, datetime.datetime(2015, 6, 11, 0, 17, 27, 128395), 'GBPUSD', 1.57), (23, datetime.datetime(2015, 6, 11, 0, 17, 29, 786153), 'GBPUSD', 1.57), (24, datetime.datetime(2015, 6, 11, 0, 17, 31, 973788), 'GBPUSD', 1.57), (25, datetime.datetime(2015, 6, 11, 0, 17, 34, 161859), 'GBPUSD', 1.57), (26, datetime.datetime(2015, 6, 11, 0, 17, 36, 527514), 'GBPUSD', 1.57), (27, datetime.datetime(2015, 6, 10, 22, 38, 35, 493231), 'GBPUSD', 1.57), (28, datetime.datetime(2015, 6, 11, 0, 17, 16, 65599), 'GBPUSD', 1.57), (29, datetime.datetime(2015, 6, 11, 0, 17, 18, 351726), 'GBPUSD', 1.57), (30, datetime.datetime(2015, 6, 11, 0, 17, 20, 538550), 'GBPUSD', 1.57), (31, datetime.datetime(2015, 6, 11, 0, 17, 22, 740352), 'GBPUSD', 1.57), (32, datetime.datetime(2015, 6, 11, 0, 17, 24, 926125), 'GBPUSD', 1.57), (33, datetime.datetime(2015, 6, 11, 0, 17, 27, 128395), 'GBPUSD', 1.57), (34, datetime.datetime(2015, 6, 11, 0, 17, 29, 786153), 'GBPUSD', 1.57), (35, datetime.datetime(2015, 6, 11, 0, 17, 31, 973788), 'GBPUSD', 1.57), (36, datetime.datetime(2015, 6, 11, 0, 17, 34, 161859), 'GBPUSD', 1.57), (37, datetime.datetime(2015, 6, 11, 0, 17, 36, 527514), 'GBPUSD', 1.57), (38, datetime.datetime(2015, 6, 11, 0, 17, 16, 65599), 'GBPUSD', 1.57), (39, datetime.datetime(2015, 6, 11, 0, 17, 18, 351726), 'GBPUSD', 1.57), (40, datetime.datetime(2015, 6, 11, 0, 17, 20, 538550), 'GBPUSD', 1.57), (41, datetime.datetime(2015, 6, 11, 0, 17, 22, 740352), 'GBPUSD', 1.57), (42, datetime.datetime(2015, 6, 11, 0, 17, 24, 926125), 'GBPUSD', 1.57), (43, datetime.datetime(2015, 6, 11, 0, 17, 27, 128395), 'GBPUSD', 1.57), (44, datetime.datetime(2015, 6, 11, 0, 17, 29, 786153), 'GBPUSD', 1.57), (45, datetime.datetime(2015, 6, 11, 0, 17, 31, 973788), 'GBPUSD', 1.57), (46, datetime.datetime(2015, 6, 11, 0, 17, 34, 161859), 'GBPUSD', 1.57), (47, datetime.datetime(2015, 6, 11, 0, 17, 36, 527514), 'GBPUSD', 1.57), (48, datetime.datetime(2015, 6, 15, 21, 1, 29, 57919), 'GBPUSD', 1.5604), (49, datetime.datetime(2015, 6, 15, 21, 1, 31, 701780), 'GBPUSD', 1.5603), (50, datetime.datetime(2015, 6, 15, 21, 1, 33, 920653), 'GBPUSD', 1.56045), (51, datetime.datetime(2015, 6, 15, 21, 1, 36, 134273), 'GBPUSD', 1.56035), (52, datetime.datetime(2015, 6, 15, 21, 1, 38, 370079), 'GBPUSD', 1.5604), (53, datetime.datetime(2015, 6, 15, 21, 1, 40, 605032), 'GBPUSD', 1.5604), (54, datetime.datetime(2015, 6, 15, 21, 1, 42, 911401), 'GBPUSD', 1.5605), (55, datetime.datetime(2015, 6, 15, 21, 1, 45, 195735), 'GBPUSD', 1.56035), (56, datetime.datetime(2015, 6, 15, 21, 1, 47, 577025), 'GBPUSD', 1.5605), (57, datetime.datetime(2015, 6, 15, 21, 1, 49, 831474), 'GBPUSD', 1.5605)]

In [ ]:

    

fig1 = plt.figure()
fig1.suptitle('GBP/USD price')
fig1.autofmt_xdate()

ax1 = fig1.add_subplot(1,1,1)

def animate1(i):
    print '--------------------', i

    DATABASE_URL= r"postgres://zpptclkw:_mnlCBXoH7PlxS6vGLC0lYfn3gEw5rpY@qdjjtnkv.db.elephantsql.com:5432/zpptclkw"
    urlparse.uses_netloc.append("postgres")
    url = urlparse.urlparse(DATABASE_URL)

    connpg = psycopg2.connect(database=url.path[1:],
      user=url.username,
      password=url.password,
      host=url.hostname,
      port=url.port
    )

    curpg = connpg.cursor()
    curpg.execute('SELECT date as "[timestamp]", price FROM vPrice;')
    results =  curpg.fetchall()
    print results[0]
    
    connpg.commit()
    curpg.close()
    connpg.close()
    
    print i
    
    data = []
    x = []
    y = []
    #conn = sqlite3.connect(view_db, detect_types=sqlite3.PARSE_COLNAMES)
    
    #c = conn_pg.cursor()
    #print c
    #df = pd.read_sql("SELECT count(*), status from iPrice group by status",conn) #,conn,parse_dates={'date':'%Y-%m-%d'})
    #results = c.execute('SELECT  date as "[timestamp]", price from vPrice ') 
    #results = c.execute('SELECT * from vPrice ') 
    #print results
 
    for row in results:
        print row[0], row[1]
        print type(row[0])
        #d = row[0]
        #dt = datetime.datetime.strptime(d, '%Y-%m-%d %H:%M:%S.%fZ')
        
        x.append(row[0])
        y.append(row[1])
        #break

    ax1.clear()
    ax1.plot(x,y)
    ax1.legend(loc='upper left')


    
ani1 = animation.FuncAnimation(fig1,animate1, interval=1000)
plt.show()


    

In [ ]:

    




    

In [17]:

    

if 1:
    DATABASE_URL= r"postgres://zpptclkw:_mnlCBXoH7PlxS6vGLC0lYfn3gEw5rpY@qdjjtnkv.db.elephantsql.com:5432/zpptclkw"
    urlparse.uses_netloc.append("postgres")
    url = urlparse.urlparse(DATABASE_URL)

    conn_pg = psycopg2.connect(database=url.path[1:],
      user=url.username,
      password=url.password,
      host=url.hostname,
      port=url.port
    )
    c = conn_pg.cursor()
    print c
    #df = pd.read_sql("SELECT count(*), status from iPrice group by status",conn) #,conn,parse_dates={'date':'%Y-%m-%d'})
    #results = c.execute('SELECT  date as "[timestamp]", price from vPrice ') 
    results = c.execute('SELECT * from vPrice ')  
    print results
    conn_pg.close()


    

    




<cursor object at 0x1136a0810; closed: 0>
None

In [ ]:

    




    

In [ ]: