#!/usr/bin/env python

import csv
import pandas as pd
from sqlalchemy import create_engine

url = 'https://www.eia.gov/dnav/pet/xls/PET_PRI_SPT_S1_D.xls'
df = pd.read_excel(url,  sheetname=2, header=2, index_col=None)

df = df.drop(['U.S. Gulf Coast Conventional Gasoline Regular Spot Price FOB (Dollars per Gallon)'],  axis=1)

df['sp_name_id'] = '2'
df['sp_region_id'] = '3'
df['unit_id'] = '1'

df.rename(index=str, columns={"New York Harbor Conventional Gasoline Regular Spot Price FOB (Dollars per Gallon)":"price"}, inplace=True)
df['Date'] = df.Date.map(lambda x: x.strftime('%Y-%m-%d'))

engine = create_engine("mysql://root:jGdttGR75!4T@127.0.0.1/nymex")
df.to_sql(name='spot_price',con=engine,if_exists='append', index=False)