def __init__(self): self.dbh = SqliteWrapper() self.dbh.connect(dbfile) #print('-------------------------------------') #print(dbtable_stocks_create) self.dbh.execute(dbtable_stocks_create)
class Concepts: def __init__(self): self.dbh = SqliteWrapper() self.dbh.connect(dbfile) print('-------------------------------------') print(dbtable_concepts_create) self.dbh.execute(dbtable_concepts_create) def __del__(self): self.dbh.close() def insertConcept(self, concept, name, url,stocks): con = self.getConcept(concept) if con: print(concept + 'is existed') return try: sql = "INSERT INTO " + dbtable_concepts + "(concept,name,url,stocks) VALUES('%s','%s','%s','%s') " %(concept,name,url,stocks) #print('-------------------------------------') #print(sql) self.dbh.execute (sql ) print("add: %s, %s, %s, %s" % (concept,name,url,stocks)) except Exception as e: print(e) def getConcepts(self): try: sql = "select * from " + dbtable_concepts all = self.dbh.select(sql) return all except Exception as e: print(e) return None def getConcept(self,concept): sql = "select * from %s where concept='%s' " %(dbtable_concepts,concept) all = self.dbh.select(sql) return all def getConceptByStock(self,stock): sql = "select name,url from " + dbtable_concepts + " where stocks like " + "'%" + stock + "%'" all = self.dbh.select(sql) links_str = '' for a in all: name = a[0] link = a[1] if links_str == '': links_str = "<a href=%s>%s</a>" % (link,name) else: links_str += ",<a href=%s>%s</a>" % (link,name) return links_str def close(self): self.dbh.close()
class Stocks: def __init__(self): self.dbh = SqliteWrapper() self.dbh.connect(dbfile) #print('-------------------------------------') #print(dbtable_stocks_create) self.dbh.execute(dbtable_stocks_create) def __del__(self): self.dbh.close() def findStockBySymbolAndDate(self,symbol,dateV): sql = "SELECT * FROM " + dbtable_stocks + " WHERE symbol = '%s' and dateV = date('%s')" % (symbol,dateV) all = self.dbh.select(sql) stock = None if(len(all)): s = all[0] stock = Stock(s[0],s[1],s[2],s[3],s[4],s[5],s[6],s[7],s[8],s[9],s[10]) return stock def findStocksBySymbol(self,symbol): all = self.dbh.select("SELECT * FROM " + dbtable_stocks + " WHERE symbol = '%s' order by dateV " % symbol) #pprint(all) stockList = [] for s in all: stock = Stock(s[0],s[1],s[2],s[3],s[4],s[5],s[6],s[7],s[8],s[9],s[10]) stockList.append(stock) return stockList def findGreatThan9(self): sql = "select symbol,count(dateV) as c from stocks where prev_close_to_close > 9 group by symbol order by c desc;" all = self.dbh.select(sql) return all def findGreatThan9ForLatestDays(self,num): sql = "select symbol,count(dateV) as c from stocks where prev_close_to_close > 9 and dateV > date('now','-%i days') group by symbol order by c desc;" %num all = self.dbh.select(sql) return all def getLatestClose(self,symbol): sql = "select close,dateV from stocks where symbol='%s' order by dateV desc " % symbol all = self.dbh.select(sql) if(len(all) > 0 ): return all[0][0],all[0][1] else: return 0,None def getLatestDate(self): sql = "select distinct dateV from stocks order by dateV desc;" all = self.dbh.select(sql) if(len(all) > 0 ): return all[0][0] else: return None def getOldDate(self,days_ago): sql = "select distinct dateV from stocks order by dateV desc;" all = self.dbh.select(sql) if(len(all) > days_ago ): return all[days_ago][0] else: return None def findGreaterCounts(self,rate): sql = "select count(symbol),dateV from stocks where prev_close_to_close > %f group by dateV order by dateV" %rate all = self.dbh.select(sql) return all def findLessCounts(self, rate): sql = "select count(symbol),dateV from stocks where prev_close_to_close < %f group by dateV order by dateV" % rate all = self.dbh.select(sql) return all def findGreatThan9ForDay(self,day): sql = "select symbol,count(dateV) as c from stocks where prev_close_to_close > 9 and dateV = '%s' group by symbol order by c desc;" %day all = self.dbh.select(sql) return all def findGreatThan9ForDay2(self,day): sql = "select symbol,low_to_high,prev_close_to_close as c from stocks where prev_close_to_close > 9 and dateV = '%s' group by symbol order by c desc;" %day all = self.dbh.select(sql) return all def getzhangdie(self,symbol,start_date, end_date): if(start_date > end_date): (start_date,end_date) = (end_date,start_date) sql = "select open, close from stocks where symbol='%s' and (dateV='%s' or dateV='%s') order by dateV desc" %(symbol,start_date,end_date) all = self.dbh.select(sql) zhangdie = 0 if(len(all) == 2): end_close = all[0][1] start_open = all[1][0] zhangdie = round((end_close-start_open)*100/start_open,2) return zhangdie def insertStocks(self, symbol, stockDBHash): if stockDBHash is None: return #print('-------------------------------------') prev_close,dateV = self.getLatestClose(symbol) ks = stockDBHash.keys() for date in sorted(ks): value = stockDBHash[date] open = float(value['Open']) close = float(value['Close']) close_adj = float(value["Adj Close"]) high = float(value['High']) low = float(value['Low']) volume = float(value['Volume']) pre_close_to_close = 0 if(prev_close != 0): pre_close_to_close = round((close - prev_close) / prev_close *100,2) open_to_close = 0 if(open != 0): open_to_close = round((close -open) / open * 100,2) low_to_high = 0 if(low != 0): low_to_high = round((high - low) / low * 100,2) prev_close = close find = self.findStockBySymbolAndDate(symbol,date) if find is not None: print("existed: %s, %s" % (symbol,date) ) continue try: sql = "INSERT INTO " + dbtable_stocks + "(symbol,dateV, open, close, close_adj, high, low, volume,prev_close_to_close,open_to_close,low_to_high) VALUES('%s','%s','%f','%f','%f','%f','%f','%f','%f','%f','%f') " \ %(symbol,date, open, close, close_adj,high,low,volume,pre_close_to_close,open_to_close,low_to_high); #print("\n" + sql) self.dbh.execute (sql ) print("add: %s, %s" % (symbol,date)) except: print("Failed: " + symbol + ' ' + date ) def insertStocksSina(self, symbol, stock): if stock is None: return #print('-------------------------------------') find = self.findStockBySymbolAndDate(symbol,stock.dateV) if find is not None: print("existed: %s, %s" % (symbol,stock.dateV) ) return try: sql = "INSERT INTO " + dbtable_stocks + "(symbol, dateV, open, close, close_adj, high, low, volume,prev_close_to_close,open_to_close,low_to_high) VALUES('%s','%s','%f','%f','%f','%f','%f','%f','%f','%f','%f') " \ %(stock.symbol,stock.dateV, float(stock.open), float(stock.close), float(stock.close_adj),float(stock.high),float(stock.low),float(stock.volume),float(stock.prev_close_to_close),float(stock.open_to_close),float(stock.low_to_high)); #print("\n" + sql) self.dbh.execute (sql ) print("add: %s, %s" % (symbol,stock.dateV)) except Exception as e: print("Failed: " + symbol + ' ' + stock.dateV + 'exception:' + str(e)) def close(self): self.dbh.close()
class Symbols: def __init__(self): self.dbh = SqliteWrapper() self.dbh.connect(dbfile) #print('-------------------------------------') #print(dbtable_symbols_create) self.dbh.execute(dbtable_symbols_create) def __del__(self): self.dbh.close() def insertSymbol(self, symbol, name): try: sql = "INSERT INTO " + dbtable_symbols + "(symbol,name) VALUES('%s','%s') " %(symbol,name) #print('-------------------------------------') #print(sql) self.dbh.execute (sql ) print("add: %s, %s" % (symbol,name)) except Exception as e: print(e) def getSymbols(self): try: sql = "select symbol from " + dbtable_symbols all = self.dbh.select(sql) symbols = [] for s in all: symbols.append(s[0]) return symbols except Exception as e: print(e) return None def getName(self,symbol): sql = "select name from symbols where symbol='%s' " %symbol all = self.dbh.select(sql) if(len(all)): return all[0][0] return None def close(self): self.dbh.close()