import auth
import api
import sql
from tools import date_index, time_series, seconds

from pandas import DataFrame
from pandas import read_sql_query
from sqlalchemy import create_engine,  MetaData
from sqlalchemy.sql import select

#|Connect to SQL database and create SQLAlchemy engine and MetaData
def dbconnect():	
	engine_str = auth.mysql()	
	eng = create_engine(engine_str)	
	conn = eng.connect()	
	meta = MetaData(eng)
	return eng, conn, meta

#|Pull trade data from SQL table and convert to DataFrame
def trades_df(table_name, exchange='', start ='', end=''):		
	eng, conn, meta = dbconnect()	
	table = sql.tables(meta, table_name)	
	arg = {'exchange':exchange, 'start':start, 'end':end}	
	select = sql.sqlselect(table, arg)
	select = select.statement()		
	df = read_sql_query(select, eng) 
	df = date_index(df)
	return df

#|Append DataFrame to SQL table via "INSERT OR IGNORE" command
def add_to_db(df, table):
	df = df.to_dict('records')		
	ins = table.insert().prefix_with('IGNORE')	
	ins.execute(df)	

#|Import BitcoinCharts trade history CSV into SQL database
#|Before running, place CSV in 'modules' folder and rename file to exchange name
def import_bcharttrades(exchange):
	eng, conn, meta = dbconnect()	
	table = sql.tables(meta, 'bcharttrades')	
	df = DataFrame()
	path = '%s.csv' % exchange
	df = df.from_csv(path)
	df.columns = ['price','amount']
	df['timestamp'] = df.index
	df['exchange'] = exchange	
	add_to_db(df, table)

#|"Ping" exchange API for trade data and import into SQL database
def trades_ping(exchange, limit=50):
	eng, conn, meta = dbconnect()
	table = sql.tables(meta, 'trades')	
	df, ping = api.trades(exchange, limit)
	add_to_db(df, table)

#|Convert and add trade history to SQL database
def trades_to_pricedb(df, typ, exchange, source):
	table_name = "pricehistory" + str(typ)
	eng, conn, meta = dbconnect()
	table = sql.tables(meta, table_name)
	ts = time_series(df, seconds(typ=typ))
	ts['exchange'] = exchange
	ts['source'] = source	
	add_to_db(ts, table)

#|Return price history DataFrame using exchange/source filters
def price_db(typ, exchange, source):
	table_name = 'pricehistory' + str(typ)
	eng, conn, meta = dbconnect()
	tbl = sql.tables(meta, table_name)
	sel = select([tbl]).where(tbl.c.exchange == exchange)
	sel = sel.where(tbl.c.source == source)
	result = conn.execute(sel)
	result = result.fetchall()
	df = DataFrame(result, columns=('timestamp','price','amount','high','low',
					'open','exchange','source'))
	df = date_index(df)
	return df



@@ -112,42 +112,8 @@
 ame%0A
-from pandas import read_sql_query%0A
 from
@@ -554,36 +554,34 @@
  dbconnect()%09%0A%09t
-a
 bl
-e
  = sql.tables(me
@@ -599,27 +599,83 @@
 ame)
-%09%0A%09arg = %7B'
+%0A%09sel = select(%5Btbl%5D)%09%0A%09if exchange %3C%3E '':%0A%09%09sel = sel.where(tbl.c.
 exchange
 ':ex
@@ -674,18 +674,20 @@
 ange
-':
+ == 
 exchange
 , 's
@@ -686,90 +686,191 @@
 ange
-, 'start':start, 'end':end%7D%09%0A
+)%0A%09if start %3C%3E '':%0A%09%09start = dateconv(start)%0A%09
 %09sel
-ect
  = s
-ql.sqlselect
+el.where
 (t
-a
 bl
-e, arg)%0A%09select = 
+.c.timestamp %3E= start)%0A%09if end %3C%3E '':%0A%09%09end = dateconv(end)%0A%09%09sel = sel.where(tbl.c.timestamp %3C= end)%0A%09print '
 select
-.
+ 
 stat
@@ -878,48 +878,75 @@
 ment
-()%09%09%0A%09df = read_sql_query(select, eng) %0A
+ crafted'%0A%09result = conn.execute(sel)%0A%09result = result.fetchall()%0A#
 %09df 
@@ -970,18 +970,22 @@
 %09return 
-df
+result
 %0A%0A#%7CAppe
@@ -2650,9 +2650,194 @@
 urn df%0A%0A
+#%7CConvert datetime sting (format: mm/dd/yy) to timestamp%0Adef dateconv(date):%0A%09date = datetime.strptime(date, %22%25m/%25d/%25y%22)%09%09%0A%09timestamp = int(mktime(date.timetuple()))%09%0A%09return timestamp%0A
 %0A

replace pandas read_sql_query (testing)