notebook.community

Edit and run

Pull To View





In [81]:



    


import sqlite3
import pandas as pd



    











In [82]:



    


import psycopg2
import urlparse



    











In [88]:



    


DATABASE_URL= r"postgres://zpptclkw:_mnlCBXoH7PlxS6vGLC0lYfn3gEw5rpY@qdjjtnkv.db.elephantsql.com:5432/zpptclkw"
#username = "zpptclkw"
#password = "_mnlCBXoH7PlxS6vGLC0lYfn3gEw5rpY"
#hostname = "qdjjtnkv.db.elephantsql.com"
#port = 8887


urlparse.uses_netloc.append("postgres")
#url = urlparse.urlparse(os.environ["DATABASE_URL"])
#url = DATABASE_URL
url = urlparse.urlparse(DATABASE_URL)

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



    











In [89]:



    


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



    











In [91]:



    


# 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()
    
    # also update esql
    print 'prices:', prices
   
    
    curpg = connpg.cursor()
    
    #curpg.executemany('INSERT INTO vPrice (date, symbol, price) VALUES (?,?,?)', prices)
    
    for price in prices:
        print 'PRICE', price
        idate, isymbol, iprice = price
        print idate, isymbol, iprice
        sql = "INSERT INTO vPrice (date, symbol, price) VALUES ('%s','%s',%s)" %(idate,isymbol,iprice)
        print sql
        curpg.execute(sql)
        #curpg.execute('select * from vPrice')
        #cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
        #curpg.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (100, "abc'def"))
  
    curpg.execute("SELECT count(*) FROM vPrice;")
    print 'PG', curpg.fetchall()

    print connpg
    print curpg
    
    connpg.commit()
    curpg.close()
    #connpg.close()



    


















    






   dPriceId  iPriceId                       iDate                  pStartDate  \
0       164       164  2015-06-11 00:17:16.065599  2015-06-11 00:17:47.817469   
1       165       165  2015-06-11 00:17:18.351726  2015-06-11 00:17:47.869438   
2       166       166  2015-06-11 00:17:20.538550  2015-06-11 00:17:47.917550   
3       167       167  2015-06-11 00:17:22.740352  2015-06-11 00:17:47.970384   
4       168       168  2015-06-11 00:17:24.926125  2015-06-11 00:17:48.017534   
5       169       169  2015-06-11 00:17:27.128395  2015-06-11 00:17:48.068605   
6       170       170  2015-06-11 00:17:29.786153  2015-06-11 00:17:48.120536   
7       171       171  2015-06-11 00:17:31.973788  2015-06-11 00:17:48.172355   
8       172       172  2015-06-11 00:17:34.161859  2015-06-11 00:17:48.219288   
9       173       173  2015-06-11 00:17:36.527514  2015-06-11 00:17:48.268252   

                     pEndDate                          date  symbol   bid  \
0  2015-06-11 00:17:47.855798  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   
1  2015-06-11 00:17:47.907763  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   
2  2015-06-11 00:17:47.955894  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   
3  2015-06-11 00:17:48.005235  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   
4  2015-06-11 00:17:48.054704  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   
5  2015-06-11 00:17:48.106919  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   
6  2015-06-11 00:17:48.158830  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   
7  2015-06-11 00:17:48.208243  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   
8  2015-06-11 00:17:48.257571  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   
9  2015-06-11 00:17:48.303482  2015-06-11 04:17:00 UTC+0000  GBPUSD  1.57   

   roll_ave   ask  
0      1.57  1.57  
1      1.57  1.57  
2      1.57  1.57  
3      1.57  1.57  
4      1.57  1.57  
5      1.57  1.57  
6      1.57  1.57  
7      1.57  1.57  
8      1.57  1.57  
9      1.57  1.57  
max_dPrice_id 173
(1, u'2015-05-30 09:15:39.485955', u'GBPUSD', 1.52975)
(2, u'2015-05-30 09:15:48.069422', u'GBPUSD', 1.52975)
(3, u'2015-05-30 09:15:55.417848', u'GBPUSD', 1.52975)
(4, u'2015-05-30 09:16:02.850010', u'GBPUSD', 1.52975)
(5, u'2015-05-30 09:16:10.257276', u'GBPUSD', 1.52975)
(6, u'2015-05-30 09:16:17.711932', u'GBPUSD', 1.52975)
(7, u'2015-05-30 09:16:25.004063', u'GBPUSD', 1.52975)
(8, u'2015-05-30 09:16:42.124534', u'GBPUSD', 1.52975)
(9, u'2015-05-30 09:17:01.656726', u'GBPUSD', 1.52975)
(10, u'2015-05-30 09:17:08.906095', u'GBPUSD', 1.52975)
(11, u'2015-05-30 09:22:54.787782', u'GBPUSD', 1.52975)
(12, u'2015-05-30 09:23:02.034814', u'GBPUSD', 1.52975)
(13, u'2015-05-30 09:23:09.305855', u'GBPUSD', 1.52975)
(14, u'2015-05-30 09:23:18.193639', u'GBPUSD', 1.52975)
(15, u'2015-05-30 09:23:25.822885', u'GBPUSD', 1.52975)
(16, u'2015-05-30 09:23:33.119151', u'GBPUSD', 1.52975)
(17, u'2015-05-30 09:23:41.924820', u'GBPUSD', 1.52975)
(18, u'2015-05-30 09:23:49.815168', u'GBPUSD', 1.52975)
(19, u'2015-05-30 09:23:57.143688', u'GBPUSD', 1.52975)
(20, u'2015-05-30 09:24:04.340398', u'GBPUSD', 1.52975)
(21, u'2015-05-30 11:15:42.775625', u'GBPUSD', 1.52975)
(22, u'2015-05-30 11:15:51.429303', u'GBPUSD', 1.52975)
(23, u'2015-05-30 11:15:58.712359', u'GBPUSD', 1.52975)
(24, u'2015-05-30 11:16:09.155449', u'GBPUSD', 1.52975)
(25, u'2015-05-30 11:16:16.865008', u'GBPUSD', 1.52975)
(26, u'2015-05-30 11:16:24.171908', u'GBPUSD', 1.52975)
(27, u'2015-05-30 11:16:31.859444', u'GBPUSD', 1.52975)
(28, u'2015-05-30 11:16:39.179710', u'GBPUSD', 1.52975)
(29, u'2015-05-30 11:16:46.792799', u'GBPUSD', 1.52975)
(30, u'2015-05-30 11:16:54.023519', u'GBPUSD', 1.52975)
(31, u'2015-05-30 13:46:54.273525', u'GBPUSD', 1.52975)
(32, u'2015-05-30 13:47:01.472458', u'GBPUSD', 1.52975)
(33, u'2015-05-30 13:47:08.713723', u'GBPUSD', 1.52975)
(34, u'2015-05-30 13:47:15.938801', u'GBPUSD', 1.52975)
(35, u'2015-05-30 13:47:23.146648', u'GBPUSD', 1.52975)
(36, u'2015-05-30 13:47:33.785998', u'GBPUSD', 1.52975)
(37, u'2015-05-30 13:47:40.990189', u'GBPUSD', 1.52975)
(38, u'2015-05-30 13:47:48.197351', u'GBPUSD', 1.52975)
(39, u'2015-05-30 13:47:55.403796', u'GBPUSD', 1.52975)
(40, u'2015-05-30 13:48:02.596469', u'GBPUSD', 1.52975)
(41, u'2015-05-30 21:48:07.157042', u'GBPUSD', 1.58)
(42, u'2015-05-30 21:48:14.391972', u'GBPUSD', 1.58)
(43, u'2015-05-30 21:48:21.587565', u'GBPUSD', 1.58)
(44, u'2015-05-30 21:48:28.938522', u'GBPUSD', 1.58)
(45, u'2015-05-30 21:48:36.136510', u'GBPUSD', 1.58)
(46, u'2015-05-30 21:48:43.399918', u'GBPUSD', 1.58)
(47, u'2015-05-30 21:48:50.689877', u'GBPUSD', 1.58)
(48, u'2015-05-30 21:48:57.887230', u'GBPUSD', 1.58)
(49, u'2015-05-30 21:49:05.094767', u'GBPUSD', 1.58)
(50, u'2015-05-30 21:49:12.313697', u'GBPUSD', 1.58)
(51, u'2015-05-30 21:51:19.345621', u'GBPUSD', 1.58)
(52, u'2015-05-30 21:51:26.553439', u'GBPUSD', 1.58)
(53, u'2015-05-30 21:51:33.751243', u'GBPUSD', 1.58)
(54, u'2015-05-30 21:51:41.409000', u'GBPUSD', 1.58)
(55, u'2015-05-30 21:51:48.601796', u'GBPUSD', 1.58)
(56, u'2015-05-30 21:51:55.955306', u'GBPUSD', 1.58)
(57, u'2015-05-30 21:52:03.184354', u'GBPUSD', 1.58)
(58, u'2015-05-30 21:52:10.406812', u'GBPUSD', 1.58)
(59, u'2015-05-30 21:52:17.660329', u'GBPUSD', 1.58)
(60, u'2015-05-30 21:52:25.296025', u'GBPUSD', 1.58)
(61, u'2015-05-30 22:03:28.929400', u'GBPUSD', 1.56)
(62, u'2015-05-30 22:03:36.148708', u'GBPUSD', 1.56)
(63, u'2015-05-30 22:03:43.415970', u'GBPUSD', 1.56)
(64, u'2015-05-30 22:03:50.618136', u'GBPUSD', 1.56)
(65, u'2015-05-30 22:03:57.832394', u'GBPUSD', 1.56)
(66, u'2015-05-30 22:04:05.019615', u'GBPUSD', 1.56)
(67, u'2015-05-30 22:04:12.249548', u'GBPUSD', 1.56)
(68, u'2015-05-30 22:04:19.456289', u'GBPUSD', 1.56)
(69, u'2015-05-30 22:04:26.781701', u'GBPUSD', 1.56)
(70, u'2015-05-30 22:04:34.022525', u'GBPUSD', 1.56)
(71, u'2015-05-30 22:07:21.315731', u'GBPUSD', 1.57)
(72, u'2015-05-30 22:07:23.636617', u'GBPUSD', 1.57)
(73, u'2015-05-30 22:07:25.841850', u'GBPUSD', 1.57)
(74, u'2015-05-30 22:07:28.533778', u'GBPUSD', 1.57)
(75, u'2015-05-30 22:07:30.730320', u'GBPUSD', 1.57)
(76, u'2015-05-30 22:07:32.990576', u'GBPUSD', 1.57)
(77, u'2015-05-30 22:07:35.206611', u'GBPUSD', 1.57)
(78, u'2015-05-30 22:07:37.410346', u'GBPUSD', 1.57)
(79, u'2015-05-30 22:07:39.621225', u'GBPUSD', 1.57)
(80, u'2015-05-30 22:07:41.934068', u'GBPUSD', 1.57)
(81, u'2015-05-31 12:56:14.802119', u'GBPUSD', 1.57)
(82, u'2015-05-31 12:56:17.784678', u'GBPUSD', 1.57)
(83, u'2015-05-31 12:56:20.458574', u'GBPUSD', 1.57)
(84, u'2015-05-31 12:56:23.491281', u'GBPUSD', 1.57)
(85, u'2015-05-31 12:56:26.282440', u'GBPUSD', 1.57)
(86, u'2015-05-31 12:56:28.843547', u'GBPUSD', 1.57)
(87, u'2015-05-31 12:56:31.887319', u'GBPUSD', 1.57)
(88, u'2015-05-31 12:56:35.977834', u'GBPUSD', 1.57)
(89, u'2015-05-31 12:56:38.610789', u'GBPUSD', 1.57)
(90, u'2015-05-31 12:56:41.489488', u'GBPUSD', 1.57)
(91, u'2015-05-31 12:57:02.496060', u'GBPUSD', 1.52975)
(92, u'2015-05-31 12:57:05.574001', u'GBPUSD', 1.52975)
(93, u'2015-05-31 12:57:07.972803', u'GBPUSD', 1.52975)
(94, u'2015-05-31 12:57:10.676370', u'GBPUSD', 1.52975)
(95, u'2015-05-31 12:57:14.182081', u'GBPUSD', 1.52975)
(96, u'2015-05-31 12:57:16.922992', u'GBPUSD', 1.52975)
(97, u'2015-05-31 12:57:21.500784', u'GBPUSD', 1.52975)
(98, u'2015-05-31 12:57:23.696928', u'GBPUSD', 1.52975)
(99, u'2015-05-31 12:57:25.965072', u'GBPUSD', 1.52975)
(100, u'2015-06-03 19:01:09.988231', u'GBPUSD', 1.5318)
(101, u'2015-06-03 19:01:12.251887', u'GBPUSD', 1.5317500000000002)
(102, u'2015-06-03 19:01:14.526627', u'GBPUSD', 1.53185)
(103, u'2015-06-03 19:01:16.693214', u'GBPUSD', 1.53205)
(104, u'2015-06-03 19:01:18.923633', u'GBPUSD', 1.53205)
(105, u'2015-06-03 19:01:21.122329', u'GBPUSD', 1.53185)
(106, u'2015-06-03 19:01:23.374512', u'GBPUSD', 1.5319)
(107, u'2015-06-03 19:01:25.577699', u'GBPUSD', 1.5318)
(108, u'2015-06-03 19:01:27.767089', u'GBPUSD', 1.5317500000000002)
(109, u'2015-06-03 19:01:30.417573', u'GBPUSD', 1.5317)
(110, u'2015-06-03 19:04:36.932651', u'GBPUSD', 1.57)
(111, u'2015-06-03 19:04:40.837993', u'GBPUSD', 1.57)
(112, u'2015-06-03 19:04:43.015796', u'GBPUSD', 1.57)
(113, u'2015-06-03 19:04:45.205543', u'GBPUSD', 1.57)
(114, u'2015-06-10 22:24:33.437872', u'GBPUSD', 1.57)
(115, u'2015-06-10 22:24:35.904918', u'GBPUSD', 1.57)
(116, u'2015-06-10 22:24:38.097365', u'GBPUSD', 1.57)
(117, u'2015-06-10 22:24:40.314584', u'GBPUSD', 1.57)
(118, u'2015-06-10 22:24:42.959084', u'GBPUSD', 1.57)
(119, u'2015-06-10 22:24:48.005668', u'GBPUSD', 1.57)
(120, u'2015-06-10 22:24:50.210198', u'GBPUSD', 1.57)
(121, u'2015-06-10 22:24:52.546179', u'GBPUSD', 1.57)
(122, u'2015-06-10 22:24:54.785114', u'GBPUSD', 1.57)
(123, u'2015-06-10 22:24:57.015239', u'GBPUSD', 1.57)
(124, u'2015-06-10 22:29:38.420379', u'GBPUSD', 1.57)
(125, u'2015-06-10 22:29:41.682313', u'GBPUSD', 1.57)
(126, u'2015-06-10 22:29:44.944513', u'GBPUSD', 1.57)
(127, u'2015-06-10 22:29:47.150397', u'GBPUSD', 1.57)
(128, u'2015-06-10 22:29:49.348754', u'GBPUSD', 1.57)
(129, u'2015-06-10 22:29:51.563111', u'GBPUSD', 1.57)
(130, u'2015-06-10 22:29:53.813746', u'GBPUSD', 1.57)
(131, u'2015-06-10 22:29:56.003534', u'GBPUSD', 1.57)
(132, u'2015-06-10 22:29:58.211360', u'GBPUSD', 1.57)
(133, u'2015-06-10 22:30:00.421931', u'GBPUSD', 1.57)
(134, u'2015-06-10 22:32:58.760385', u'GBPUSD', 1.57)
(135, u'2015-06-10 22:33:01.248488', u'GBPUSD', 1.57)
(136, u'2015-06-10 22:33:03.500709', u'GBPUSD', 1.57)
(137, u'2015-06-10 22:33:05.704323', u'GBPUSD', 1.57)
(138, u'2015-06-10 22:33:08.737873', u'GBPUSD', 1.57)
(139, u'2015-06-10 22:33:10.998805', u'GBPUSD', 1.57)
(140, u'2015-06-10 22:33:13.230788', u'GBPUSD', 1.57)
(141, u'2015-06-10 22:33:15.461484', u'GBPUSD', 1.57)
(142, u'2015-06-10 22:33:19.293582', u'GBPUSD', 1.57)
(143, u'2015-06-10 22:33:21.514379', u'GBPUSD', 1.57)
(144, u'2015-06-10 22:38:21.667847', u'GBPUSD', 1.57)
(145, u'2015-06-10 22:38:23.861756', u'GBPUSD', 1.57)
(146, u'2015-06-10 22:38:26.085651', u'GBPUSD', 1.57)
(147, u'2015-06-10 22:38:28.845479', u'GBPUSD', 1.57)
(148, u'2015-06-10 22:38:31.054219', u'GBPUSD', 1.57)
(149, u'2015-06-10 22:38:33.269950', u'GBPUSD', 1.57)
(150, u'2015-06-10 22:38:35.493231', u'GBPUSD', 1.57)
(151, u'2015-06-10 22:38:37.712068', u'GBPUSD', 1.57)
(152, u'2015-06-10 22:38:39.942610', u'GBPUSD', 1.57)
(153, u'2015-06-10 22:38:42.139516', u'GBPUSD', 1.57)
(154, u'2015-06-11 00:03:11.293206', u'GBPUSD', 1.57)
(155, u'2015-06-11 00:03:13.493631', u'GBPUSD', 1.57)
(156, u'2015-06-11 00:03:15.676744', u'GBPUSD', 1.57)
(157, u'2015-06-11 00:03:17.846871', u'GBPUSD', 1.57)
(158, u'2015-06-11 00:03:20.059772', u'GBPUSD', 1.57)
(159, u'2015-06-11 00:03:22.306707', u'GBPUSD', 1.57)
(160, u'2015-06-11 00:03:24.559226', u'GBPUSD', 1.57)
(161, u'2015-06-11 00:03:28.105289', u'GBPUSD', 1.57)
(162, u'2015-06-11 00:03:32.524055', u'GBPUSD', 1.57)
(163, u'2015-06-11 00:03:34.718032', u'GBPUSD', 1.57)
(164, u'2015-06-11 00:17:16.065599', u'GBPUSD', 1.57)
(165, u'2015-06-11 00:17:18.351726', u'GBPUSD', 1.57)
(166, u'2015-06-11 00:17:20.538550', u'GBPUSD', 1.57)
(167, u'2015-06-11 00:17:22.740352', u'GBPUSD', 1.57)
(168, u'2015-06-11 00:17:24.926125', u'GBPUSD', 1.57)
(169, u'2015-06-11 00:17:27.128395', u'GBPUSD', 1.57)
(170, u'2015-06-11 00:17:29.786153', u'GBPUSD', 1.57)
(171, u'2015-06-11 00:17:31.973788', u'GBPUSD', 1.57)
(172, u'2015-06-11 00:17:34.161859', u'GBPUSD', 1.57)
(173, u'2015-06-11 00:17:36.527514', u'GBPUSD', 1.57)
(1, u'dPrice', 173)
(2, u'dPrice', 173)
prices: [[u'2015-06-11 00:17:16.065599', u'GBPUSD', 1.57], [u'2015-06-11 00:17:18.351726', u'GBPUSD', 1.57], [u'2015-06-11 00:17:20.538550', u'GBPUSD', 1.57], [u'2015-06-11 00:17:22.740352', u'GBPUSD', 1.57], [u'2015-06-11 00:17:24.926125', u'GBPUSD', 1.57], [u'2015-06-11 00:17:27.128395', u'GBPUSD', 1.57], [u'2015-06-11 00:17:29.786153', u'GBPUSD', 1.57], [u'2015-06-11 00:17:31.973788', u'GBPUSD', 1.57], [u'2015-06-11 00:17:34.161859', u'GBPUSD', 1.57], [u'2015-06-11 00:17:36.527514', u'GBPUSD', 1.57]]
PRICE [u'2015-06-11 00:17:16.065599', u'GBPUSD', 1.57]
2015-06-11 00:17:16.065599 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:16.065599','GBPUSD',1.57)
PRICE [u'2015-06-11 00:17:18.351726', u'GBPUSD', 1.57]
2015-06-11 00:17:18.351726 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:18.351726','GBPUSD',1.57)
PRICE [u'2015-06-11 00:17:20.538550', u'GBPUSD', 1.57]
2015-06-11 00:17:20.538550 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:20.538550','GBPUSD',1.57)
PRICE [u'2015-06-11 00:17:22.740352', u'GBPUSD', 1.57]
2015-06-11 00:17:22.740352 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:22.740352','GBPUSD',1.57)
PRICE [u'2015-06-11 00:17:24.926125', u'GBPUSD', 1.57]
2015-06-11 00:17:24.926125 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:24.926125','GBPUSD',1.57)
PRICE [u'2015-06-11 00:17:27.128395', u'GBPUSD', 1.57]
2015-06-11 00:17:27.128395 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:27.128395','GBPUSD',1.57)
PRICE [u'2015-06-11 00:17:29.786153', u'GBPUSD', 1.57]
2015-06-11 00:17:29.786153 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:29.786153','GBPUSD',1.57)
PRICE [u'2015-06-11 00:17:31.973788', u'GBPUSD', 1.57]
2015-06-11 00:17:31.973788 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:31.973788','GBPUSD',1.57)
PRICE [u'2015-06-11 00:17:34.161859', u'GBPUSD', 1.57]
2015-06-11 00:17:34.161859 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:34.161859','GBPUSD',1.57)
PRICE [u'2015-06-11 00:17:36.527514', u'GBPUSD', 1.57]
2015-06-11 00:17:36.527514 GBPUSD 1.57
INSERT INTO vPrice (date, symbol, price) VALUES ('2015-06-11 00:17:36.527514','GBPUSD',1.57)
PG [(26L,)]
<connection object at 0x7f905cc99880; dsn: 'dbname=zpptclkw user=zpptclkw password=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx host=qdjjtnkv.db.elephantsql.com port=5432', closed: 0>
<cursor object at 0x10f073ed8; closed: 0>

















In [92]:



    


connpg.close()



    











In [ ]:



    






    











In [93]:



    


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 0x10f073de0; closed: 0>
None

















In [102]:



    


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()

    print connpg
    print curpg
    
    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)]
<connection object at 0x7f905cdabd10; dsn: 'dbname=zpptclkw user=zpptclkw password=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx host=qdjjtnkv.db.elephantsql.com port=5432', closed: 0>
<cursor object at 0x10f073ed8; closed: 0>

















In [ ]:



    






    











In [ ]:

Content source: mhallett/MeDaReDa

Similar notebooks:

notebook.community | gallery | about