In [ ]:

    

import pandas as pd
import numpy as np
import sys
import datetime


    

In [ ]:

    

trainfile = "../data/ozone-la-exogrenous.csv"

cols = ["Date", "Month", "Exog2", "Exog3", "Exog4", "Ozone"];
    
df_train = pd.read_csv(trainfile, names = cols, sep=r',', engine='python', skiprows=1);
df_train['Time'] = df_train['Date'].apply(lambda x : datetime.datetime.strptime(x, "%Y-%m"))


    

In [ ]:

    

df_train.head()


    

In [ ]:

    

df_train_dummies = pd.get_dummies(df_train)


    

In [ ]:

    

df_train_dummies.head()


    

In [ ]:

    

lVC = df_train['Exog4'].value_counts()


    

In [ ]:

    

lVC.head(100)


    

In [ ]:

    

lVC.index[0:5].tolist()


    

In [ ]:

    

from sqlalchemy import *
#from sqlalchemy import desc, nullsfirst
import sqlalchemy
from sqlalchemy.sql import table, column, select


    

In [ ]:

    

# in-memory database
#lDSN = 'sqlite://'
#lDSN = 'mysql://user:pass@localhost/GitHubtest'
lDSN = 'postgresql:///GitHubtest'
engine = create_engine(lDSN , echo=True)
#create_engine(  , echo=True)
conn = engine.connect()


    

In [ ]:

    

df_train.to_sql("ds1" , conn, if_exists='replace')


    

In [ ]:

    

meta = MetaData()
table2 = Table('ds1', meta, autoload=True, autoload_with=engine)


    

In [ ]:

    

table2.c['Month'].type


    

In [ ]:

    

table2.c['Exog2'].type, table2.c['Exog3'].type, table2.c['Exog4'].type, table2.c['Time'].type


    

In [ ]:

    

dt1 = datetime.datetime(1955, 7, 1, 0, 0); dt2 = datetime.datetime(1965, 7, 1, 0, 0)


    

In [ ]:

    

dt1 , dt2, str(dt1), str(dt2)


    

In [ ]:

    

def getDateTimeLiteral(iValue):
    #return sqlalchemy.sql.expression.literal(iValue, sqlalchemy.types.TIMESTAMP);
    return  sqlalchemy.sql.expression.literal(str(iValue), sqlalchemy.types.String);


    

In [ ]:

    

expr = getDateTimeLiteral(dt2) -  getDateTimeLiteral(dt2)
expr2 = getDateTimeLiteral(dt2)


    

In [ ]:

    

expr.type, expr2.type


    

In [ ]:

    

stmt = select([expr, expr2])


    

In [ ]:

    

def generate_Sql(statement):
    return statement.compile(bind=engine, compile_kwargs={'literal_binds': True}).string;


    

In [ ]:

    

generate_Sql(stmt)


    

In [ ]:

    

print(str(stmt))


    

In [ ]:

    

print(str(stmt.compile(engine)))


    

In [ ]:

    

from sqlalchemy.dialects import postgresql
print(stmt.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}))


    

In [ ]:

    




    

In [ ]:

    




    

In [ ]: