def getIDS():
session = Session()
stocks = session.query(Stock).all()
list = []
for i in stocks:
list.append(i.id)
return list
def getIDList():
session = Session()
stocks = session.query(Stock).all()
IDlist = []
for stock in stocks:
IDlist.append(stock.klid)
return IDlist
def getTrendValues(id, days):
session = Session()
stockTrending = session.query(Trend).filter(Trend.stock_id == id).all()
if stockTrending:
#lets transfer the time to UTC+0 to get more accurate day to day data
#time is stored in UTC-7
#for i in stockTrending:
# i.time = i.time + timedelta(hours=7)
#get latest day number that has trending record
latestDate = stockTrending[-1]
latestDate = latestDate.time
Lyear = latestDate.year
Lmonth = latestDate.month
LDay = latestDate.day
#time is stored in UTC-7
fdate = datetime.strptime(str(Lyear)+str(Lmonth)+str(LDay), "%Y%m%d") #make it a date object
#lets include utc-7 time difference to date object.
fdate = fdate - timedelta(hours=7)
#lets get earlier day
sdate = fdate - timedelta(hours=24)
#empty list for storing trending data
trendList = []
#lets get trend data from specified range. starts at 0
for i in range(int(days)+1):
#date object includes utc-7 time difference
fdate = fdate - timedelta(hours=i*24)
sdate = sdate - timedelta(hours=i*24)
#lets query db for 24 datapoints where we can calculate days mean value
trending = session.query(Trend).filter(and_
(Trend.stock_id == id, Trend.time >= sdate, Trend.time < fdate)
).all()
if trending:
totalValue = 0
for j in trending:
totalValue += j.value
#if there are missing data points for that day, dividing it always with 24 guarantees accuracy
mean = totalValue/24
trendList.append(mean)
#lets reserve list item order, so that newest value is last
return trendList[::-1]
def CheckAllTheStockThatDoesntHaveTrendData():
session = Session()
counter = 0
for id in range(0, 137):
trend = session.query(Trend).filter(Trend.stock_id == id).first()
print '\n\n'
print trend
print '\n\n'
if not trend:
counter += 1
print counter
def addTrend(dict, klid):
session = Session()
stock = session.query(Stock).filter(Stock.klid == klid).first()
treData = session.query(Trend).filter(and_(Trend.stock_id == stock.id, Trend.time == dict['time'])).first()
#check that there are no data from that day and time
if not treData:
with transaction.manager:
trend = Trend(stock_id = stock.id, time = dict['time'], value = dict['value'])
session.add(trend)
session.commit()
def stockData(list, id):
session = Session()
stock = session.query(Stock).filter(Stock.klid == id).first()
time = datetime.strptime(list[0], "%d.%m.%Y").date() #make it a date object
stockData = session.query(Stock_data).filter(and_(Stock_data.stock_id == stock.id, Stock_data.date == time)).first()
#if there is a record from that day we can exit this class
if stockData:
return False
with transaction.manager:
newData = Stock_data(stock_id = stock.id, date = time, open = float(list[1]), high_sell = float(list[2]),
lowest_sell = float(list[3]), close = float(list[4]), volume = float(list[5]))
session.add(newData)
session.commit()
return True
def getnamesbyid(id):
session = Session()
stock = session.query(Stock).filter(Stock.id == id).first()
return stock.name
def addStock(dict, cat):
'''
What is given to this class
{'name': name, "rise": change, 'price': price, 'buy': buy, 'sell': sell,
'lowest sell': ssell, 'high': high, 'volume': volume, 'exchange': exchange, 'time': time, 'klid': klid}
'''
session = Session()
stock = session.query(Stock).filter(Stock.klid == dict['klid']).all()
#if stock isn't in the db lets add it there
if not stock:
#because python didn't like Finnish letters, we can change them back
if "'O'" in cat:
cat = cat.replace("'O'", u"Ö")
if "'o'" in cat:
cat = cat.replace("'o'", u"ö")
#lets see if there is already a same named category
category = session.query(Category).filter(Category.category == cat).first()
#if not lets create a category
if not category:
with transaction.manager:
cate = Category(category = cat)
session.add(cate)
session.commit()
#get category's id
cateid = cate.id
else:
#get category's id
cateid = category.id
with transaction.manager:
name = dict['name']
#because python didn't like finnish letters, we can change them back
if "'a'" in name:
name = name.replace("'a'", u"ä")
if "'o'" in name:
name = name.replace("'o'", u"ö")
if "'OA'" in name:
name = name.replace("'OA'", u"Å")
#create new stock record
newStock = Stock(name = name, klid = dict['klid'], category_id = cateid)
session.add(newStock)
session.commit()
#add exchange information for the stock
exc = Exchange(stock_id = newStock.id, exchange = dict['exchange'])
session.add(exc)
session.commit()
def getStockname(klid):
session = Session()
stock = session.query(Stock).filter(Stock.klid == klid).first()
return stock.name