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"