import datetime import json import time import pandas as pd from fastapi import FastAPI, Header, Response from fastapi.middleware.cors import CORSMiddleware from typing import Optional import uvicorn import mysql.connector from pydantic import BaseModel import requests from flask import request import jsonwebtoken as jwt import asyncio from time import perf_counter import concurrent.futures import multiprocessing from decouple import Config, RepositoryEnv env=Config(RepositoryEnv('../.env')) PORT = env.get('Flask_PORT') DB_Name = env.get('DB_Name') USER = env.get('DB_User') PASSWORD = env.get('DB_Password') HOST = env.get('HOST') MySecret = env.get('MySecret') algorithm = env.get('algorithm') app = FastAPI() class UpdateStocks(BaseModel): query: str class Stocks(BaseModel): InstrumentIdentifier: str Exchange: str orderType: str tradeType: str tradeStatus: str LastTradeTime: float LastTradePrice: float ActualBuyPrice: float ActualSellPrice: float SelectedBuyPrice: float SelectedSellPrice: float StopLossPrice: float TargetPrice: float candle_ID: str chart_ID: str indicator_ID: str others_ID: str image_Location: str analysis_ID: str UserID: str BoughtTime: float SoldTime: float origins = ["*"] app.add_middleware( CORSMiddleware, allow_origins=origins, allow_credentials=True, allow_methods=["GET", "PUT", "POST", "DELETE", "UPDATE"], allow_headers=["*"], ) dataTableName = "stockdata" conn = None mycursor = None def connect(): """ Connect to MySQL database """ global conn try: if conn is not None and conn.is_connected(): print('Connected to MySQL database') return conn else: conn = mysql.connector.connect( host=HOST, user=USER, password=PASSWORD, database=DB_Name ) except Exception as e: print(e) return f"MySql Connection error: {str(e)}" finally: if conn is not None and conn.is_connected(): return conn endpoint = "https://nimblerest.lisuns.com:4532" apikey = "4e9766d5-6f38-4364-99e7-d74d98c7590d" dataBaseName= "lit_datapipeline." conn = connect(); mycursor = conn.cursor(); @app.get("/flask-api") async def read_root(): return {"hi": "world"} def fetch_data_local(*, update: bool = False, json_cache: str): if update: json_data = None else: try: with open(json_cache, "r") as file: json_data = json.load(file) print("Fetched from local file") except(FileNotFoundError, json.JSONDecodeError) as ex: print(f"No loca cache found {ex}") json_data = None if not json_data: print("Fetch data from database") stocks = pd.read_sql_query( f"select * from {dataBaseName}masterStocks where Exchange='NSE' order by InstrumentIdentifier", conn) myjsons = [] print(stocks) for index in range(0, stocks.__len__()): myjsons.append(stocks["InstrumentIdentifier"][index]) json_data = {"instruments": myjsons} with open(json_cache, "w") as file: json.dump({"instruments": myjsons}, file) return json_data @app.get("/flask-api/GetMasterStocks") async def GetMasterStocks(instrument: str = None, Authorization: str = Header(None)): print(instrument) try: conn = connect(); token = jwt.decode(Authorization.split(" ")[1], key=MySecret, algorithms=algorithm); myjsons = fetch_data_local(update=False, json_cache="masterStocks.json"); return myjsons except Exception as ex: print(ex) return Response("GetMasterStocks:" + str(ex)); def GetLiveStocksCount(fromTime: int, instrumentData: str): try: conn = connect(); start = perf_counter() stocks = pd.read_sql_query( f"select count(*) from {dataBaseName}stockdata where InstrumentIdentifier = " f"'{instrumentData}' and tradeStatus != 'closed'", conn) stop = perf_counter() return stocks except Exception as ex: print(ex) return None def GetStocksRealTimeDB(instrumentData: str): try: conn = connect(); start = perf_counter() stocks = pd.read_sql_query( f"select * from {dataBaseName}all_stocks_details where InstrumentIdentifier = " f"'{instrumentData}'", conn) stop = perf_counter() return stocks except Exception as ex: print(ex) return Response("GetStocksRealTimeDB:" + str(ex)); @app.get("/flask-api/getTopLosers") def getTopLosers(rowCount: int = 10, Authorization: str = Header(None)): try: conn = connect(); token = jwt.decode(Authorization.split(" ")[1], key=MySecret, algorithms=algorithm); start = perf_counter() stockPerformance = pd.read_sql_query(f"select * from {dataBaseName}all_stocks_details where PRICECHANGEPERCENTAGE < -1 order by PRICECHANGEPERCENTAGE DESC LIMIT {rowCount}", conn) myjsons = [] for item in range(0, stockPerformance.__len__()): myjson = {"InstrumentIdentifier": stockPerformance["INSTRUMENTIDENTIFIER"][item], "LastTradePrice": stockPerformance["LASTTRADEPRICE"][item], "changePercent": stockPerformance["PRICECHANGEPERCENTAGE"][item] } myjsons.append(myjson) stop = perf_counter() return myjsons except Exception as ex: print(ex) return Response("getTopLosers:" + str(ex)); @app.get("/flask-api/getTopGainers") def getTopGainers(rowCount: int = 10, Authorization: str = Header(None)): try: conn = connect(); token = jwt.decode(Authorization.split(" ")[1], key=MySecret, algorithms=algorithm); start = perf_counter() stockPerformance = pd.read_sql_query( f"select * from {dataBaseName}all_stocks_details order by PRICECHANGEPERCENTAGE DESC LIMIT {rowCount}", conn) myjsons = [] for item in range(0, stockPerformance.__len__()): myjson = {"InstrumentIdentifier": stockPerformance["INSTRUMENTIDENTIFIER"][item], "LastTradePrice": stockPerformance["LASTTRADEPRICE"][item], "changePercent": stockPerformance["PRICECHANGEPERCENTAGE"][item] } myjsons.append(myjson) stop = perf_counter() return myjsons except Exception as ex: print(ex) return Response("getTopGainers:" + str(ex)); @app.get("/flask-api/getWeeklyStocks") def getWeeklyStocks(weekName: str = None, Authorization: str = Header(None), userid: str = None): try: conn = connect(); token = jwt.decode(Authorization.split(" ")[1], key=MySecret, algorithms=algorithm); start = perf_counter() weekNameTemp = "" if weekName is not None: weekNameTemp = f"where weekName='{weekName}' and userid='{userid}'" weeklystocks = pd.read_sql_query(f"select * from {dataBaseName}weeklydata {weekNameTemp}", conn) myjsons = [] for item in range(0, weeklystocks.__len__()): myjson = {"InstrumentIdentifier": weeklystocks["InstrumentIdentifier"][item], "orderDate": weeklystocks["orderDate"][item], "orderDateFormat": datetime.datetime.fromtimestamp( int(weeklystocks["orderDate"][item]) / 1000).strftime('%d %B, %Y %H:%M:%S'), "ExitDate": weeklystocks["ExitDate"][item], "ExitDateFormat": datetime.datetime.fromtimestamp( int(weeklystocks["ExitDate"][item]) / 1000).strftime('%d %B, %Y %H:%M:%S'), "LastTradePrice": weeklystocks["EntryPrice"][item], "targetPrice": weeklystocks["targetPrice"][item], "ExitPrice": weeklystocks["ExitPrice"][item], "EntryPrice": weeklystocks["EntryPrice"][item], "StopLossPrice": weeklystocks["StopLossPrice"][item], "WeekName": weeklystocks["WeekName"][item], "orderType": weeklystocks["orderType"][item], "tradeStatus": weeklystocks["tradeStatus"][item] } myjsons.append(myjson) stop = perf_counter() return myjsons except Exception as ex: print(ex) return Response("getWeeklyStocks:" + str(ex)); def GetStocksRealTime(fromTime: int, instrumentData: str): try: fromTime = int(fromTime / 1000) urlData = f"{endpoint}/GetHistory/?accessKey={apikey}&exchange=NSE&instrumentIdentifier={instrumentData}" \ f"&periodicity=TICK&from={fromTime}&xml=false" start = perf_counter() resp = requests.get(urlData) stop = perf_counter() return resp.content except Exception as ex: print(ex) return None @app.post("/flask-api/UpdateStocks") async def UpdateStocks(query: UpdateStocks, Authorization: str = Header(None)): print(query) conn = connect(); mycursor = conn.cursor() for quries in query: for i in quries[1].split("|"): try: token = jwt.decode(Authorization.split(" ")[1], key=MySecret, algorithms=algorithm); mycursor.execute(i) conn.commit() except Exception as ex: print(ex); return Response("UpdateStocks:" + str(ex)); @app.get("/flask-api/GetStocks") async def GetStocksNew(userid: str = None, instrument: str = None, orderType: str = None, Authorization: str = Header(None)): print(instrument) try: conn = connect(); mycursor = conn.cursor() token = jwt.decode(Authorization.split(" ")[1], key=MySecret, algorithms=algorithm); countFilter = " where tradeStatus != 'closed' " if userid is not None and userid != "": countFilter = countFilter + f" and userid = '{userid}'" userid = f" where userid = '{userid}'" else: userid = "" stocks = pd.read_sql_query( f"select (select count(*) FROM {dataBaseName}stockdata {countFilter}) as counts," f"InstrumentIdentifier,stockid,tradeType,Exchange,LastTradeTime,LastTradePrice,SelectedBuyPrice," f"SelectedSellPrice,ActualBuyPrice,ActualSellPrice,StopLossPrice,TargetPrice,tradeStatus,orderType," f"BoughtTime,SoldTime,candle_ID,chart_ID,indicator_ID,others_ID,image_Location,analysis_ID,UserID from " f"{dataBaseName}stockdata {userid} order by LastTradeTime", conn) if instrument is not None: if userid == "": userid = f" where InstrumentIdentifier ='{instrument}'" else: userid = userid + f" and InstrumentIdentifier ='{instrument}'" stocks = pd.read_sql_query( f"select (select count(*) FROM {dataBaseName}stockdata {countFilter}) as counts," f"InstrumentIdentifier,stockid,tradeType,Exchange,LastTradeTime,LastTradePrice,SelectedBuyPrice," f"SelectedSellPrice,ActualBuyPrice,ActualSellPrice,StopLossPrice,TargetPrice,tradeStatus,orderType," f"BoughtTime,SoldTime,candle_ID,chart_ID,indicator_ID,others_ID,image_Location,analysis_ID," f"UserID from {dataBaseName}stockdata {userid} order by LastTradeTime", conn) if orderType is not None: if userid == "": userid = f" where tradeStatus in ('{orderType}', 'amo')" else: userid = userid + f" and tradeStatus in ('{orderType}', 'amo')" stocks = pd.read_sql_query( f"select (select count(*) FROM {dataBaseName}stockdata {countFilter}) as counts, " f"InstrumentIdentifier,stockid,tradeType,Exchange,LastTradeTime,LastTradePrice,SelectedBuyPrice," f"SelectedSellPrice,ActualBuyPrice,ActualSellPrice,StopLossPrice,TargetPrice,tradeStatus,orderType," f"BoughtTime,SoldTime,candle_ID,chart_ID,indicator_ID,others_ID,image_Location,analysis_ID," f"UserID from {dataBaseName}stockdata {userid} order by LastTradeTime", conn) print(stocks); myjsons = [] mytimestamp = datetime.datetime.now(); targetEndTime = datetime.datetime(mytimestamp.year, mytimestamp.month, mytimestamp.day, 15, 30, 30) targetStartTime = datetime.datetime(mytimestamp.year, mytimestamp.month, mytimestamp.day, 9, 15, 15) for index in range(0, stocks.__len__()): realData = GetStocksRealTimeDB(stocks["InstrumentIdentifier"][index]) profitLoss = 0; EntryPrice = 0; found = False; LTP = 0; LTT = 0; ExitDate = 0 for items in range(0, realData.__len__()): print(items) LTP = realData["LASTTRADEPRICE"][items]; LTT = realData["LASTTRADETIME"][items]; if stocks["tradeType"][index] == "Buy": ExitDate = (datetime.datetime.now() + datetime.timedelta(days=14)).timestamp(); EntryPrice = stocks["SelectedBuyPrice"][index]; profitLoss = realData["LASTTRADEPRICE"][items] - stocks["SelectedBuyPrice"][index]; if 1 <= mytimestamp.isoweekday() <= 5 and (targetStartTime <= mytimestamp <= targetEndTime): if stocks["tradeStatus"][index] in ("open", "amo") and stocks["SelectedBuyPrice"][index] > \ realData["LASTTRADEPRICE"][items]: profitLoss = realData["LASTTRADEPRICE"][items] - stocks["SelectedBuyPrice"][index]; EntryPrice = realData["LASTTRADEPRICE"][items]; sqlupQuery = r"update {1} set tradeStatus='live',ActualBuyPrice ={0}, BoughtTime={3}, LastTradePrice={0}, LastTradeTime={3} where stockid = '{2}'" \ .format(realData["LASTTRADEPRICE"][items], f"{dataBaseName}stockdata", stocks["stockid"][index], realData["LASTTRADETIME"][items]) mycursor.execute(sqlupQuery) conn.commit() found = True; ExitDate = (float(realData["LASTTRADETIME"][items]) + datetime.timedelta(days=14)).timestamp(); if stocks["tradeStatus"][index] == "live" and ( (stocks["StopLossPrice"][index] > realData["LASTTRADEPRICE"][items]) or ( stocks["TargetPrice"][index] < realData["LASTTRADEPRICE"][items])): stocks["tradeStatus"][index] = "closed" profitLoss = realData["LASTTRADEPRICE"][items] - stocks["ActualBuyPrice"][index]; sqlupQuery = r"update {1} set tradeStatus='closed',ActualSellPrice ={0}, SoldTime={3}, LastTradePrice={0}, LastTradeTime={3} where stockid = '{2}'" \ .format(realData["LASTTRADEPRICE"][items], f"{dataBaseName}stockdata", stocks["stockid"][index], realData["LASTTRADETIME"][items]) mycursor.execute(sqlupQuery) conn.commit() found = True; date_time = datetime.datetime.fromtimestamp(float(realData["BoughtTime"][items]) / 1000) sqlins = r"insert into " + dataBaseName + "weeklydata (InstrumentIdentifier, weeklyid, orderType, WeekName, StopLossPrice, targetPrice, EntryPrice, ExitPrice, orderDate, ExitDate, userid) Values ('{0}', '{1}', '{2}', '{3}', {4}, {5}, {6}, {7}, {8}, {9}, '{10}')".format(stocks["InstrumentIdentifier"][index], time.time(), stocks["orderType"][index], "Week " + str(int(date_time.strftime("%V")) - 27), stocks["StopLossPrice"][index], stocks["TargetPrice"][index], realData["LastTradePrice"][items], stocks["TargetPrice"][index], stocks["BoughtTime"][index], stocks["LastTradeTime"][index], stocks["UserID"][index]) mycursor.execute(sqlins) conn.commit() ExitDate = (float(stocks["BoughtTime"][index]) + datetime.timedelta(days=14)).timestamp(); if stocks["tradeType"][index] == "Sell": ExitDate = (datetime.datetime.now() + datetime.timedelta(days=14)).timestamp(); EntryPrice = stocks["SelectedSellPrice"][index]; profitLoss = stocks["SelectedSellPrice"][index] - realData["LASTTRADEPRICE"][items]; if 1 <= mytimestamp.isoweekday() <= 5 and (targetStartTime <= mytimestamp <= targetEndTime): if stocks["tradeStatus"][index] == "open" and stocks["SelectedSellPrice"][index] < \ realData["LASTTRADEPRICE"][items]: profitLoss = stocks["SelectedSellPrice"][index] - realData["LASTTRADEPRICE"][items]; EntryPrice = realData["LASTTRADEPRICE"][items]; sqlupQuery = r"update {1} set tradeStatus='live',ActualSellPrice ={0}, SoldTime={3}, LastTradePrice={0}, LastTradeTime={3} where stockid = '{2}'" \ .format(realData["LASTTRADEPRICE"][items], f"{dataBaseName}stockdata", stocks["stockid"][index], realData["LASTTRADETIME"][items]) mycursor.execute(sqlupQuery) conn.commit() found = True; ExitDate = (float(realData["LASTTRADETIME"][items]) + datetime.timedelta(days=14)).timestamp(); if stocks["tradeStatus"][index] == "live" and ( (stocks["StopLossPrice"][index] < realData["LASTTRADEPRICE"][items]) or ( stocks["TargetPrice"][index] > realData["LASTTRADEPRICE"][items])): profitLoss = stocks["ActualSellPrice"][index] - realData["LASTTRADEPRICE"][items]; sqlupQuery = r"update {1} set tradeStatus='closed',ActualBuyPrice ={0}, BoughtTime={3}, LastTradePrice={0}, LastTradeTime={3} where stockid = '{2}'" \ .format(realData["LASTTRADEPRICE"][items], f"{dataBaseName}stockdata", stocks["stockid"][index], realData["LASTTRADETIME"][items]) mycursor.execute(sqlupQuery) conn.commit() found = True; date_time = datetime.datetime.fromtimestamp(float(realData["SoldTime"][items]) / 1000) sqlins = r"insert into " + dataBaseName + "weeklydata (InstrumentIdentifier, weeklyid, orderType, WeekName, StopLossPrice, targetPrice, EntryPrice, ExitPrice, orderDate, ExitDate, userid) Values ('{0}', '{1}', '{2}', '{3}', {4}, {5}, {6}, {7}, {8}, {9}, '{10}')".format(stocks["InstrumentIdentifier"][index], time.time(), stocks["orderType"][index], "Week " + str(int(date_time.strftime("%V")) - 27), stocks["StopLossPrice"][index], stocks["TargetPrice"][index], realData["LastTradePrice"][items], stocks["TargetPrice"][index], stocks["SoldTime"][index], stocks["LastTradeTime"][index], stocks["UserID"][index]) mycursor.execute(sqlins) conn.commit() ExitDate = (float(stocks["SoldTime"][index]) + datetime.timedelta(days=14)).timestamp(); if found is False and stocks["tradeStatus"][index] != "closed": sqlupQuery = r"update {1} set LastTradePrice={0}, LastTradeTime={3} where stockid = '{2}'" \ .format(LTP, f"{dataBaseName}stockdata", stocks["stockid"][index], LTT) mycursor.execute(sqlupQuery) conn.commit() updatedStock = pd.read_sql_query( f"select (select count(*) FROM {dataBaseName}stockdata {countFilter}) as counts, " f"InstrumentIdentifier,stockid,tradeType,Exchange,LastTradeTime,LastTradePrice,SelectedBuyPrice," f"SelectedSellPrice,ActualBuyPrice,ActualSellPrice,StopLossPrice,TargetPrice,tradeStatus,orderType," f"BoughtTime,SoldTime,candle_ID,chart_ID,indicator_ID,others_ID,image_Location,analysis_ID," f"UserID from {dataBaseName}stockdata where stockid='{stocks['stockid'][index]}' order by LastTradeTime", conn) if updatedStock["tradeStatus"][0] == "live" and updatedStock["tradeType"][0] == "Sell": ExitDate = (float(updatedStock["SoldTime"][0]) + datetime.timedelta(days=14)).timestamp(); if updatedStock["tradeStatus"][0] == "live" and updatedStock["tradeType"][0] == "Buy": ExitDate = (float(updatedStock["BoughtTime"][0]) + datetime.timedelta(days=14)).timestamp(); liveStockCount = (stocks["counts"][index]) targetColor = "t-data profit loss"; if profitLoss >= 0: targetColor = "t-data profit gain" myjson = {"InstrumentIdentifier": updatedStock["InstrumentIdentifier"][0], "LastTradeTime": updatedStock["LastTradeTime"][0], "EntryPrice": EntryPrice, "liveStockCount": str(liveStockCount), "LastTradePrice": updatedStock["LastTradePrice"][0], "Exchange": updatedStock["Exchange"][0], "profitLoss": profitLoss, "targetColor": targetColor, "LoadedFrom": "", "BoughtTime": updatedStock["BoughtTime"][0], "SoldTime": updatedStock["SoldTime"][0], "orderType": updatedStock["orderType"][0], "StopLossPrice": updatedStock["StopLossPrice"][0], "TargetPrice": updatedStock["TargetPrice"][0], "stockID": updatedStock["stockid"][0], "tradeType": updatedStock["tradeType"][0], "tradeStatus": updatedStock["tradeStatus"][0], "ExitDate": datetime.datetime.fromtimestamp(int(ExitDate)).strftime('%d %B, %Y %H:%M:%S') } myjsons.append(myjson) print(myjson); return myjsons except Exception as ex: print(ex) return Response("GetStocks:" + str(ex)); @app.post("/flask-api/SaveStocks") async def SaveStocks(stock: Stocks, Authorization: str = Header(None)): # req_data = request.get_json() print(stock.InstrumentIdentifier) # mytimestamp = datetime.datetime.fromtimestamp(stock.LastTradeTime / 1000) mytimestamp = datetime.datetime.now(); if 1 <= mytimestamp.isoweekday() <= 5: sqlins = f"insert into {dataBaseName}stockdata (InstrumentIdentifier, stockid, Exchange, LastTradeTime, tradeType," \ f"LastTradePrice, ActualBuyPrice, ActualSellPrice, SelectedBuyPrice, SelectedSellPrice, StopLossPrice, " \ f"TargetPrice, tradeStatus, orderType, candle_ID, chart_ID, indicator_ID, others_ID, image_Location, " \ f"analysis_ID, UserID, BoughtTime, SoldTime) Values ('{stock.InstrumentIdentifier}', '{str(time.time())}', " \ f"'{stock.Exchange}', {float(stock.LastTradeTime)}, '{stock.tradeType}', {float(stock.LastTradePrice)}, {float(stock.ActualBuyPrice)}, " \ f"{float(stock.ActualSellPrice)}, {float(stock.SelectedBuyPrice)}, {float(stock.SelectedSellPrice)}, {float(stock.StopLossPrice)}, " \ f"{float(stock.TargetPrice)}, '{str(stock.tradeStatus)}', '{stock.orderType}', '{stock.candle_ID}', '{stock.chart_ID}', " \ f"'{stock.indicator_ID}', '{stock.others_ID}', '{stock.image_Location}', '{stock.analysis_ID}', '{stock.UserID}', {stock.BoughtTime}, {stock.SoldTime})" else: sqlins = f"insert into {dataBaseName}stockdata (InstrumentIdentifier, stockid, Exchange, LastTradeTime, tradeType," \ f"LastTradePrice, ActualBuyPrice, ActualSellPrice, SelectedBuyPrice, SelectedSellPrice, StopLossPrice, " \ f"TargetPrice, tradeStatus, orderType, candle_ID, chart_ID, indicator_ID, others_ID, image_Location, " \ f"analysis_ID, UserID, BoughtTime, SoldTime) Values ('{stock.InstrumentIdentifier}', '{str(time.time())}', " \ f"'{stock.Exchange}', {float(stock.LastTradeTime)}, '{stock.tradeType}', {float(stock.LastTradePrice)}, {float(stock.ActualBuyPrice)}, " \ f"{float(stock.ActualSellPrice)}, {float(stock.SelectedBuyPrice)}, {float(stock.SelectedSellPrice)}, {float(stock.StopLossPrice)}, " \ f"{float(stock.TargetPrice)}, 'amo', '{stock.orderType}', '{stock.candle_ID}', '{stock.chart_ID}', " \ f"'{stock.indicator_ID}', '{stock.others_ID}', '{stock.image_Location}', '{stock.analysis_ID}', '{stock.UserID}', {stock.BoughtTime}, {stock.SoldTime})" print("This is an AMO order as its outside market hours...."); datetime_str = mytimestamp.strftime("%Y - %m - %d %H : %M : %S") try: conn = connect(); mycursor = conn.cursor() token = jwt.decode(Authorization.split(" ")[1], key=MySecret, algorithms=algorithm); mycursor.execute(sqlins) conn.commit() return {"InstrumentIdentifier": stock.InstrumentIdentifier, "Exchange": stock.Exchange, "LastTradeTime": stock.LastTradeTime, "LastTradePrice": stock.LastTradePrice} except Exception as ex: print(ex) return Response("SaveStocks:" + str(ex)); if __name__ == '__main__': # print_hi('PyCharm') uvicorn.run('main:app', host='0.0.0.0', port=int(PORT))