#! /usr/bin/python
import psycopg2
import sys
from sqlalchemy import create_engine
import Quandl
import pandas
import numpy
import re
import time


futures_code='ICE/CTH2016'
futures=(futures_code.split('/'))[1]

#ticker_month, year
splitted_futures=re.split('(\d+)',futures)

ticker = (splitted_futures[0])[:-1]
month = (splitted_futures[0])[-1]
year = int((splitted_futures[1]))
df = Quandl.get(futures_code,authtoken='MY_TOKEN')
#data.head()
print list(df.columns.values)
df['Ticker']=ticker
df['Month']=month
df['Year']= year
df['Futures']= year
cols = ['Open', 'High', 'Low', 'Settle', 'Volume',
'Prev. Day Open Interest', 'EFP Volume', 'EFS Volume', 'Block Volume',
'Ticker', 'Month', 'Year']
df=df[cols]
print df

#postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...]
engine = create_engine('postgresql://seasis:310793@localhost:5432/seasis')
con = None
start_time = time.time()
try:
    print 'connecting'
    con = psycopg2.connect(dbname='seasis', user='seasis',
                           host='localhost', password='310793')
    cur = con.cursor()
    print 'cursor created'

    cur.execute('CREATE TABLE IF NOT EXISTS ct('
                'open NUMERIC (20,10), '
                'high NUMERIC (20,10), '
                'low NUMERIC (20,10), '
                'settle NUMERIC (20,10), '
                'volume INTEGER, '
                'prev_day_open_interest INTEGER, '
                'efp_volume INTEGER, '
                'efs_volume INTEGER, '
                'block_volume INTEGER, '
                'ticker VARCHAR, '
                'month VARCHAR, '
                'year SMALLINT)')
    print("--- %s seconds ---" % (time.time() - start_time))
    print 'table created'
    df.to_sql("ct", engine, if_exists='append')
    print("--- %s seconds ---" % (time.time() - start_time))
    print 'added to db'
    con.commit()
except psycopg2.DatabaseError, e:
    print 'Error %s' % e
    sys.exit(1)
finally:

    if con:
        con.close()
print 'end'
print("--- %s seconds ---" % (time.time() - start_time))