My python code , my application name is “upstox_new”
import requests
import pandas as pd
import cx_Oracle
from sqlalchemy import create_engine
from datetime import datetime
import schedule
import time
Read the bearer token from a file
with open(“C:\Users\raghv\PycharmProjects\Cokkie_fetch\venv\upstox.txt”, “r”) as file:
bearer_token = file.read().strip()
Connect to Oracle database and save data
cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_10")
engine = create_engine(‘oracle+cx_oracle://upstox:upstox@localhost/orcl’)
def save_url_data_to_oracle(url, table_name, headers=None):
try:
# Fetch data from URL
response = requests.get(url, headers=headers if headers else {})
response_data = response.json()
# Process data to DataFrame
rows = []
for index, values in response_data['data'].items():
try:
timestamp = datetime.strptime(values["timestamp"], '%Y-%m-%dT%H:%M:%S.%f%z').strftime('%d-%b-%Y %H:%M:%S')
except ValueError:
# Handle the case where timestamp doesn't have microseconds
timestamp = datetime.strptime(values["timestamp"], '%Y-%m-%dT%H:%M:%S%z').strftime('%d-%b-%Y %H:%M:%S')
base_row = {
"instrument_token": values.get("instrument_token", ""),
"symbol": index,
"last_price": values.get("last_price", 0),
"volume": values.get("volume", 0),
"average_price": values.get("average_price", 0),
"oi": values.get("oi", 0),
"oi_day_high": values.get("oi_day_high", 0),
"oi_day_low": values.get("oi_day_low", 0),
"last_trade_time": datetime.fromtimestamp(int(values.get("last_trade_time", "0")[:-3])).strftime('%d-%b-%Y %H:%M:%S'),
"total_buy_quantity": values.get("total_buy_quantity", 0),
"total_sell_quantity": values.get("total_sell_quantity", 0),
"open": values["ohlc"]["open"],
"high": values["ohlc"]["high"],
"low": values["ohlc"]["low"],
"close": values["ohlc"]["close"],
"timestamp": timestamp
}
# Extract depth buy and sell data
for i, depth in enumerate(values['depth']['buy'], 1):
base_row[f'buy_quantity_{i}'] = depth['quantity']
base_row[f'buy_price_{i}'] = depth['price']
base_row[f'buy_orders_{i}'] = depth['orders']
for i, depth in enumerate(values['depth']['sell'], 1):
base_row[f'sell_quantity_{i}'] = depth['quantity']
base_row[f'sell_price_{i}'] = depth['price']
base_row[f'sell_orders_{i}'] = depth['orders']
rows.append(base_row)
df = pd.DataFrame(rows)
df['last_trade_time'] = pd.to_datetime(df['last_trade_time'], format='%d-%b-%Y %H:%M:%S')
df['timestamp'] = pd.to_datetime(df['timestamp'], format='%d-%b-%Y %H:%M:%S')
for col in df.columns:
if df[col].dtype == 'object':
try:
df[col] = df[col].astype(float)
except ValueError:
# If a column can't be converted to float, it'll skip that column.
pass
# Delete existing data and insert new data into the provided table
engine.execute(f"delete from {table_name}")
df.to_sql(table_name, engine, if_exists='append', index=False)
except Exception as e:
print(f"Error occurred: {e}")
time.sleep(10) # Sleep for 10 seconds before retrying
save_url_data_to_oracle(url, table_name, headers)
headers = {
‘accept’: ‘/’,
‘Api-Version’: ‘2.0’,
‘Authorization’: f’Bearer {bearer_token}’
}
url = ‘https–
/api-v2.upstox.com/market-quote/quotes?symbol=NSE_INDEX%7CNifty%2050%2CNSE_INDEX%7CNifty%20Bank%2CNSE_INDEX%7CIndia%20VIX%2CNSE_INDEX%7CNifty%20Fin%20Service%2CNSE_INDEX%7CNifty%20Fin%20Service%2CNSE_INDEX%7CNIFTY%20MID%20SELECT%2CBSE_INDEX%7CSENSEX%2C’
#save_url_data_to_oracle(url, ‘live_index’, headers)
Execute the SQL query to fetch data
sql_query = “”"
select INSTRUMENT_KEY from banknifty_symbol
union
select INSTRUMENT_KEY from nifty_symbol
union
select INSTRUMENT_KEY from finnifty_symbol
union
select INSTRUMENT_KEY from midcapnifty_symbol
“”"
result_df = pd.read_sql(sql_query, engine)
instrument_keys = ‘,’.join(result_df[‘instrument_key’].astype(str))
#print(instrument_keys)
final_url = url + instrument_keys
print(final_url)
def job():
save_url_data_to_oracle(final_url, ‘live_data’, headers)
Schedule the job
def run_schedule():
while True:
current_time = datetime.now().time()
start_time = datetime.strptime(“9:15:00”, “%H:%M:%S”).time()
end_time = datetime.strptime(“20:30:00”, “%H:%M:%S”).time()
if start_time <= current_time <= end_time:
job()
time.sleep(1)
Start the scheduler
run_schedule()