def clean_up(test_table_to_drop):
"""
Drops tables created from individual tests
so no dependencies arise from sequential tests
"""
if sql.table_exists(test_table_to_drop, self.db, flavor='mysql'):
cur = self.db.cursor()
cur.execute("DROP TABLE %s" % test_table_to_drop)
cur.close()
def clean_up(test_table_to_drop):
"""
Drops tables created from individual tests
so no dependencies arise from sequential tests
"""
if sql.table_exists(test_table_to_drop, self.db, flavor='mysql'):
cur = self.db.cursor()
cur.execute("DROP TABLE %s" % test_table_to_drop)
cur.close()
def toDB(con, table, tableName):
#Drop table if it exists
if psql.table_exists(tableName, con, flavor='sqlite'):
cur = con.cursor()
sql = 'DROP TABLE "main"."{}"'.format(tableName)
cur.execute(sql)
con.commit()
#Write to db
psql.write_frame(table, tableName, con)
con.commit()
def toDB(con, table, tableName):
#Drop table if it exists
if psql.table_exists(tableName, con, flavor='sqlite'):
cur = con.cursor()
sql = 'DROP TABLE "main"."{}"'.format(tableName)
cur.execute(sql)
con.commit()
#Write to db
psql.write_frame(table, tableName , con)
con.commit()
def dropTable(self, dataFrame, sTable):
if psql.table_exists(sTable, self.conn, 'sqlite'):
psql.execute("DROP TABLE " + sTable, self.conn)
def existData(self,sensorid):
global data_cnx
cnx = sqlite3.connect(data_cnx)
e = sql.table_exists ('sensor_'+ str(id),con=cnx)
cnx.close()
return e
def calcsymbol(project,symbol):
#project=args[0]
#symbol=args[1]
#metadata=args[2]
#metadata2=MetaData(bind=engine)
#metadata2.reflect(bind=engine)
#declarative_base(metadata=metadata)
projid = project.projid
lcalcdays = project.calcdays
leffectpercent = project.effectpercent
lcauses = project.cause
symbolid = symbol.symbolid
resultid=str(projid)+'_'+str(symbolid)
tablename=resultid+'_result'
mysql_cn=MySQLdb.connect(host='localhost',user='******',passwd='david',db='david_trade')
if symbol.status != 1:#runing or completed
tradedb.update('symbol',where="symbolid =$symbolid",vars={'symbolid':symbolid}, status = 0)
causedf=calcsymbol_phraseI(project,symbol)
logger.debug("cause caculated")
segcount=project.segcount
lcalcdays=project.calcdays
backcount=project.backcount
calcdf=causedf.drop(["symbolid","effect"],axis=1)
resultdf=pd.DataFrame(index=causedf.index)
effectcolumn=causedf['effect']
for column in calcdf.columns:
resultdf=resultdf.join(calc_func(calcdf[column],effectcolumn,segcount,lcalcdays,backcount))
logger.debug('cp caculated')
causedf=causedf.join(resultdf)
causedf=causedf.reset_index()
exists = psql.table_exists(name=tablename, con=mysql_cn, flavor='mysql')
if not exists:
psql.write_frame(frame=causedf.where(pd.notnull(causedf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
else:
create = "DROP TABLE "+tablename+";"
cur = mysql_cn.cursor()
cur.execute(create)
cur.close()
psql.write_frame(frame=causedf.where(pd.notnull(causedf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
#psql.write_frame(causedf.where(pd.notnull(causedf), None),con=mysql_cn,name=tablename,if_exists='replace',flavor='mysql')
tradedb.update('symbol',where="symbolid =$symbolid",vars={'symbolid':symbolid}, status = 1)
causedf.set_index('Date',drop=True,inplace=True)
else:
#load dataframe from mysql
expression='select * from '+tablename+';'
causedf=psql.frame_query(expression, con=mysql_cn)
#generate resultdf with cp and ncp for cause
causedf.set_index('Date',drop=True,inplace=True)
resultdf=causedf
if symbol.btstatus==1:
return
tradedb.update('symbol',where="symbolid =$symbolid",vars={'symbolid':symbolid}, btstatus = 0)
effectcolumn=causedf['effect']
for column in resultdf.columns:
if (column.startswith('ncp') or column.startswith('cp'))==False:
resultdf=resultdf.drop(column,axis=1)
backtesterdf=pd.DataFrame(index=resultdf.columns,columns=[str(item) for item in gtcp_list])
if len(resultdf.columns) == 0:
return
for column in resultdf.columns:
backtesterdf.ix[column]= backtesterwrap(gtcp_list)(resultdf[column],effectcolumn)
#calc RTSR
tablename=resultid+'_backtester'
wbacktesterdf=backtesterdf.reset_index()
exists = psql.table_exists(name=tablename, con=mysql_cn, flavor='mysql')
if not exists:
psql.write_frame(frame=wbacktesterdf.where(pd.notnull(wbacktesterdf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
else:
create = "DROP TABLE "+tablename+";"
cur = mysql_cn.cursor()
cur.execute(create)
cur.close()
psql.write_frame(frame=wbacktesterdf.where(pd.notnull(wbacktesterdf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
#psql.write_frame(backtesterdf.where(pd.notnull(backtesterdf), None),con=mysql_cn,name=tablename,if_exists='append',flavor='mysql')
RTSR=round(float(len(effectcolumn[effectcolumn==True].index))/len(effectcolumn.index),3)
#correaltion
tcpser=pd.Series(backtesterdf.columns,index=backtesterdf.columns)
ratertsr=project.ratertsr
coefficient=project.coefficient
ncoefficient=project.ncoefficient
correlationdf=pd.DataFrame(index=backtesterdf.index,columns=['correlation','predictive'])
for index in backtesterdf.index:
strlist=backtesterdf.ix[index]
tcplist=[round(float(item)/100,3) for item in strlist]
sr_corr=tcpser.corr(backtesterdf.ix[index])
if index.startswith('ncp'):
corr=ncoefficient
elif index.startswith('cp'):
corr=coefficient
if np.isnan(sr_corr)==False and sr_corr>corr and backtesterdf.ix[index].min()>ratertsr+RTSR:
predictive=True
else:
predictive=False
correlationdf.ix[index]=pd.Series([sr_corr,predictive],name=index,index=['correlation','predictive'])
#mysql_cn= MySQLdb.connect(host='localhost',port=3306,user='******', passwd='david',db='david_trade')
tablename=resultid+'_correlation'
correlationdf=correlationdf.reset_index()
exists = psql.table_exists(name=tablename, con=mysql_cn, flavor='mysql')
if not exists:
psql.write_frame(frame=correlationdf.where(pd.notnull(correlationdf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
else:
create = "DROP TABLE "+tablename+";"
cur = mysql_cn.cursor()
cur.execute(create)
cur.close()
psql.write_frame(frame=correlationdf.where(pd.notnull(correlationdf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
#psql.write_frame(correlationdf.where(pd.notnull(correlationdf), None),con=mysql_cn,name=tablename,if_exists='append',flavor='mysql')
#df_mysql = psql.frame_query('select * from VIEWS;', con=mysql_cn)
tradedb.update('symbol',where="symbolid =$symbolid",vars={'symbolid':symbolid}, btstatus = 1)
mysql_cn.close()
logger.debug("backtester completed")
def existsData(self,sensorid):
global data_cnx
cx = sqlite3.connect(data_cnx)
e = sql.table_exists('sensor_'+ str(sensorid),cx,'sqlite')
cx.close()
return e
def calcbtcor(project,causedf,resultid):
if causedf is None:
return
effectcolumn=causedf['effect']
resultdf=causedf
for column in resultdf.columns:
if (column.startswith('ncp') or column.startswith('cp'))==False:
resultdf=resultdf.drop(column,axis=1)
backtesterdf=pd.DataFrame(index=resultdf.columns,columns=[str(item) for item in gtcp_list])
if len(resultdf.columns) == 0:
return
for column in resultdf.columns:
backtesterdf.ix[column]= backtesterwrap(gtcp_list)(resultdf[column],effectcolumn)
#calc RTSR
#psql.write_frame(backtesterdf.where(pd.notnull(backtesterdf), None),con=mysql_cn,name=tablename,if_exists='append',flavor='mysql')
RTSR=round(float(len(effectcolumn[effectcolumn==True].index))/len(effectcolumn.index),3)
#correaltion
tcpser=pd.Series(backtesterdf.columns,index=backtesterdf.columns)
ratertsr=project.ratertsr
coefficient=project.coefficient
ncoefficient=project.ncoefficient
correlationdf=pd.DataFrame(index=backtesterdf.index,columns=['correlation','predictive'])
for index in backtesterdf.index:
strlist=backtesterdf.ix[index]
tcplist=[round(float(item)/100,3) for item in strlist]
sr_corr=tcpser.corr(backtesterdf.ix[index])
if index.startswith('ncp'):
corr=ncoefficient
elif index.startswith('cp'):
corr=coefficient
if np.isnan(sr_corr)==False and sr_corr>corr and backtesterdf.ix[index].min()>ratertsr+RTSR:
predictive=True
else:
predictive=False
correlationdf.ix[index]=pd.Series([sr_corr,predictive],name=index,index=['correlation','predictive'])
#save backtesterdf
tablename=resultid+'_backtester'
wbacktesterdf=backtesterdf.reset_index()
try:
mysql_cn= MySQLdb.connect(host='localhost',port=3306,user='******', passwd='david',db='david_trade')
exists = psql.table_exists(name=tablename, con=mysql_cn, flavor='mysql')
if not exists:
psql.write_frame(frame=wbacktesterdf.where(pd.notnull(wbacktesterdf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
else:
create = "DROP TABLE "+tablename+";"
cur = mysql_cn.cursor()
cur.execute(create)
mysql_cn.commit()
cur.close()
psql.write_frame(frame=wbacktesterdf.where(pd.notnull(wbacktesterdf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
#save correlationdf
tablename=resultid+'_correlation'
correlationdf=correlationdf.reset_index()
exists = psql.table_exists(name=tablename, con=mysql_cn, flavor='mysql')
if not exists:
psql.write_frame(frame=correlationdf.where(pd.notnull(correlationdf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
else:
expression = "DROP TABLE "+tablename+";"
cur = mysql_cn.cursor()
cur.execute(expression)
psql.write_frame(frame=correlationdf.where(pd.notnull(correlationdf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
mysql_cn.commit()
cur.close()
mysql_cn.close()
except MySQLdb.Error,e:
print "Mysql Error %d:%s" %(e.args[0],e.args[1])
cur.close()
conn.close()
calcbtcor(proj,causedf,resultid)
except MySQLdb.Error,e:
print "error in select cause"
print "Mysql Error %d:%s" %(e.args[0],e.args[1])
else:
#save causedf and use the item[2] to calc backtester
print "update 1"
causedf=item[1]
resultid=str(projid)+'_'+str(symbolid)
tablename=resultid+'_result'
try:
mysql_cn=MySQLdb.connect(host='localhost',user='******',passwd='david',db='david_trade')
cur = mysql_cn.cursor()
exists = psql.table_exists(name=tablename, con=mysql_cn, flavor='mysql')
if not exists:
psql.write_frame(frame=causedf.where(pd.notnull(causedf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
else:
expression = "DROP TABLE "+tablename+";"
cur.execute(expression)
psql.write_frame(frame=causedf.where(pd.notnull(causedf),None), name=tablename, con=mysql_cn, if_exists='replace',flavor='mysql')
#tradedb.update('symbol',where="symbolid =$symbolid",vars={'symbolid':symbolid}, status = 1)
#update symbol expression
expression="update symbol set status=1 where symbolid="+str(symbolid)+";"
cur.execute(expression)
mysql_cn.commit()
cur.close()
mysql_cn.close()
except MySQLdb.Error,e:
print "error in update cause status"
