regarding running code and insert ticks into sqlite

Hi I managed to get this code by searching on internet forums.

Basically I am very new to python. So I am finding difficulty to create a code that ...

1) Get livestreaming data from alice_blue api 2) store the data into a database (e.g. sqlite3)

So to get live stream start which thing should be called ? (for example if I defined x= 3, & then if i have to print x i write print(x). In your code after defining event_handler.. , open_callback, alice.subscribe, alice.start_websocket which thing is to start the webstreaming ?)

I managed to get sqlite3 insert code from forums. I tried to add it at multiple places such as running it seperately, defining with event_handler.. but it does not insert data into database. also does not show any error.

Please help me solve issues.

code to generate access token and alice object

from alice_blue import *
import datetime
import time
import pandas as pd
import sqlite3

username = 'xxxxxx'
password = 'xxxxxxxxxx'
twoFA='a'

client_id = ''
client_secret = ''
redirect_url= 'https://ant.aliceblueonline.com/plugin/callback'

#access_token = AliceBlue.login_and_get_access_token(username=username, password=password, twoFA=twoFA,  api_secret=client_secret, redirect_url=redirect_url, app_id=client_id)
access_token = '' #(this is the generated access_token stored to speed up things & not spamming server)

alice = AliceBlue(username=username, password=password, access_token=access_token, master_contracts_to_download=['NSE'])

creating database and tables

db = sqlite3.connect('F:/aliceblue/alice_1.db')

tokens = ("AARTIIND", "ACC", "ADANIENT", "ADANIPORTS", "ALKEM", "AMARAJABAT", "AMBUJACEM", "APLLTD", "APOLLOHOSP", "APOLLOTYRE", "ASHOKLEY", "ASIANPAINT", "AUBANK", "AUROPHARMA", "AXISBANK", "BAJAJ-AUTO", "BAJAJFINSV", "BAJFINANCE", "BALKRISIND", "BANDHANBNK", "BANKBARODA", "BATAINDIA", "BEL", "BERGEPAINT", "BHARATFORG", "BHARTIARTL", "BHEL", "BIOCON", "BOSCHLTD", "BPCL", "BRITANNIA", "CADILAHC", "CANBK", "CHOLAFIN", "CIPLA", "COALINDIA", "COFORGE", "COLPAL", "CONCOR", "CUB", "CUMMINSIND", "DABUR", "DEEPAKNTR", "DIVISLAB", "DLF", "DRREDDY", "EICHERMOT", "ESCORTS", "EXIDEIND", "FEDERALBNK", "GAIL", "GLENMARK", "GMRINFRA", "GODREJCP", "GODREJPROP", "GRANULES", "GRASIM", "GUJGASLTD", "HAVELLS", "HCLTECH", "HDFC", "HDFCAMC", "HDFCBANK", "HDFCLIFE", "HEROMOTOCO", "HINDALCO", "HINDPETRO", "HINDUNILVR", "IBULHSGFIN", "ICICIBANK", "ICICIGI", "ICICIPRULI", "IDEA", "IDFCFIRSTB", "IGL", "INDIGO", "INDUSINDBK", "INDUSTOWER", "INFY", "IOC", "IRCTC", "ITC", "JINDALSTEL", "JSWSTEEL", "JUBLFOOD", "KOTAKBANK", "L&TFH", "LALPATHLAB", "LICHSGFIN", "LT", "LTI", "LTTS", "LUPIN", "M&M", "M&MFIN", "MANAPPURAM", "MARICO", "MARUTI", "MCDOWELL-N", "MFSL", "MGL", "MINDTREE", "MOTHERSUMI", "MPHASIS", "MRF", "MUTHOOTFIN", "NAM-INDIA", "NATIONALUM", "NAUKRI", "NAVINFLUOR", "NESTLEIND", "NMDC", "NTPC", "ONGC", "PAGEIND", "PEL", "PETRONET", "PFC", "PFIZER", "PIDILITIND", "PIIND", "PNB", "POWERGRID", "PVR", "RAMCOCEM", "RBLBANK", "RECLTD", "RELIANCE", "SAIL", "SBILIFE", "SBIN", "SHREECEM", "SIEMENS", "SRF", "SRTRANSFIN", "SUNPHARMA", "SUNTV", "TATACHEM", "TATACONSUM", "TATAMOTORS", "TATAPOWER", "TATASTEEL", "TCS", "TECHM", "TITAN", "TORNTPHARM", "TORNTPOWER", "TRENT", "TVSMOTOR", "UBL", "ULTRACEMCO", "UPL", "VEDL", "VOLTAS", "WIPRO", "ZEEL")

def create_tables(tokens):
    c=db.cursor()
    for i in tokens:
        c.execute("CREATE TABLE IF NOT EXISTS '{}' (ts datetime,price real(15,5), volume integer)".format(i))
    try:
        db.commit()
    except:
        db.rollback()

create_tables(tokens)

till here no problem everything works fine

Now I defined sql insert function. I have called the function inside the event_handler.. function but didnt work, I defined separately and run separately but didnt work, also i defined the whole thing inside the event handler but didnt work

I tested the sqlite3 database using yfinance and yliveticker and it works like charm (using the absolutely same syntax as this code) also i have tried manipulating sqlite3 code many times so there might be some minor error like missing comma or bracket.

def insert_ticks(message):
    for tick in message:
        ts = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(tick['exchange_time_stamp']))
        symbol = tick['instrument'].symbol
        ltp = tick['ltp']
        volume = tick['volume']
        db = sqlite3.connect('F:/aliceblue/alice_1.db')
        c = db.cursor()
        tok = str(symbol)
        vals = [ts,ltp,volume]
        query = "INSERT INTO '{}'(ts,price,volume) VALUES (?,?,?)".format(tok)
        c.execute(query, vals)
        print(tok)
        print(vals)

now i tried running the following code to get the live market data. when I run the full code I get market data but I dont know specifically which function starts websocket streaming

def event_handler_quote_update(message):
    #insert_ticks(message)
    #msg = (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(message['exchange_time_stamp'])), message['instrument'].symbol, message['ltp'], message['volume'])   
    #print(msg)
    ts = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(message['exchange_time_stamp']))
    symbol = message['instrument'].symbol
    ltp = message['ltp']
    volume = message['volume']
    """
    for tick in message:
        ts = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(tick['exchange_time_stamp']))
        symbol = tick['instrument'].symbol
        ltp = tick['ltp']
        volume = tick['volume']
        db = sqlite3.connect('F:/aliceblue/alice_1.db')
        c = db.cursor()
        tok = str(symbol)
        vals = [ts,ltp,volume]
        query = "INSERT INTO '{}'(ts,price,volume) VALUES (?,?,?)".format(tok)
        c.execute(query, vals)
        print(tok)
        print(vals)
    """
    #print(ts)
    #print(symbol)
    #print(ltp)
    #print(volume)
    x = str(ts) + " " + str(symbol) + " " + str(ltp) + " " + str(volume)
    #insert_ticks(message)
    #return message
    print(x)

instrument_list = [alice.get_instrument_by_symbol('NSE', 'AARTIIND'), alice.get_instrument_by_symbol('NSE', 'ACC'), alice.get_instrument_by_symbol('NSE', 'ADANIENT'), alice.get_instrument_by_symbol('NSE', 'ADANIPORTS'), alice.get_instrument_by_symbol('NSE', 'ALKEM'), alice.get_instrument_by_symbol('NSE', 'AMARAJABAT'), alice.get_instrument_by_symbol('NSE', 'AMBUJACEM'), alice.get_instrument_by_symbol('NSE', 'APLLTD'), alice.get_instrument_by_symbol('NSE', 'APOLLOHOSP'), alice.get_instrument_by_symbol('NSE', 'APOLLOTYRE'), alice.get_instrument_by_symbol('NSE', 'ASHOKLEY'), alice.get_instrument_by_symbol('NSE', 'ASIANPAINT'), alice.get_instrument_by_symbol('NSE', 'AUBANK'), alice.get_instrument_by_symbol('NSE', 'AUROPHARMA'), alice.get_instrument_by_symbol('NSE', 'AXISBANK'), alice.get_instrument_by_symbol('NSE', 'BAJAJ-AUTO'), alice.get_instrument_by_symbol('NSE', 'BAJAJFINSV'), alice.get_instrument_by_symbol('NSE', 'BAJFINANCE'), alice.get_instrument_by_symbol('NSE', 'BALKRISIND'), alice.get_instrument_by_symbol('NSE', 'BANDHANBNK'), alice.get_instrument_by_symbol('NSE', 'BANKBARODA'), alice.get_instrument_by_symbol('NSE', 'BATAINDIA'), alice.get_instrument_by_symbol('NSE', 'BEL'), alice.get_instrument_by_symbol('NSE', 'BERGEPAINT'), alice.get_instrument_by_symbol('NSE', 'BHARATFORG'), alice.get_instrument_by_symbol('NSE', 'BHARTIARTL'), alice.get_instrument_by_symbol('NSE', 'BHEL'), alice.get_instrument_by_symbol('NSE', 'BIOCON'), alice.get_instrument_by_symbol('NSE', 'BOSCHLTD'), alice.get_instrument_by_symbol('NSE', 'BPCL'), alice.get_instrument_by_symbol('NSE', 'BRITANNIA'), alice.get_instrument_by_symbol('NSE', 'CADILAHC'), alice.get_instrument_by_symbol('NSE', 'CANBK'), alice.get_instrument_by_symbol('NSE', 'CHOLAFIN'), alice.get_instrument_by_symbol('NSE', 'CIPLA'), alice.get_instrument_by_symbol('NSE', 'COALINDIA'), alice.get_instrument_by_symbol('NSE', 'COFORGE'), alice.get_instrument_by_symbol('NSE', 'COLPAL'), alice.get_instrument_by_symbol('NSE', 'CONCOR'), alice.get_instrument_by_symbol('NSE', 'CUB'), alice.get_instrument_by_symbol('NSE', 'CUMMINSIND'), alice.get_instrument_by_symbol('NSE', 'DABUR'), alice.get_instrument_by_symbol('NSE', 'DEEPAKNTR'), alice.get_instrument_by_symbol('NSE', 'DIVISLAB'), alice.get_instrument_by_symbol('NSE', 'DLF'), alice.get_instrument_by_symbol('NSE', 'DRREDDY'), alice.get_instrument_by_symbol('NSE', 'EICHERMOT'), alice.get_instrument_by_symbol('NSE', 'ESCORTS'), alice.get_instrument_by_symbol('NSE', 'EXIDEIND'), alice.get_instrument_by_symbol('NSE', 'FEDERALBNK'), alice.get_instrument_by_symbol('NSE', 'GAIL'), alice.get_instrument_by_symbol('NSE', 'GLENMARK'), alice.get_instrument_by_symbol('NSE', 'GMRINFRA'), alice.get_instrument_by_symbol('NSE', 'GODREJCP'), alice.get_instrument_by_symbol('NSE', 'GODREJPROP'), alice.get_instrument_by_symbol('NSE', 'GRANULES'), alice.get_instrument_by_symbol('NSE', 'GRASIM'), alice.get_instrument_by_symbol('NSE', 'GUJGASLTD'), alice.get_instrument_by_symbol('NSE', 'HAVELLS'), alice.get_instrument_by_symbol('NSE', 'HCLTECH'), alice.get_instrument_by_symbol('NSE', 'HDFC'), alice.get_instrument_by_symbol('NSE', 'HDFCAMC'), alice.get_instrument_by_symbol('NSE', 'HDFCBANK'), alice.get_instrument_by_symbol('NSE', 'HDFCLIFE'), alice.get_instrument_by_symbol('NSE', 'HEROMOTOCO'), alice.get_instrument_by_symbol('NSE', 'HINDALCO'), alice.get_instrument_by_symbol('NSE', 'HINDPETRO'), alice.get_instrument_by_symbol('NSE', 'HINDUNILVR'), alice.get_instrument_by_symbol('NSE', 'IBULHSGFIN'), alice.get_instrument_by_symbol('NSE', 'ICICIBANK'), alice.get_instrument_by_symbol('NSE', 'ICICIGI'), alice.get_instrument_by_symbol('NSE', 'ICICIPRULI'), alice.get_instrument_by_symbol('NSE', 'IDEA'), alice.get_instrument_by_symbol('NSE', 'IDFCFIRSTB'), alice.get_instrument_by_symbol('NSE', 'IGL'), alice.get_instrument_by_symbol('NSE', 'INDIGO'), alice.get_instrument_by_symbol('NSE', 'INDUSINDBK'), alice.get_instrument_by_symbol('NSE', 'INDUSTOWER'), alice.get_instrument_by_symbol('NSE', 'INFY'), alice.get_instrument_by_symbol('NSE', 'IOC'), alice.get_instrument_by_symbol('NSE', 'IRCTC'), alice.get_instrument_by_symbol('NSE', 'ITC'), alice.get_instrument_by_symbol('NSE', 'JINDALSTEL'), alice.get_instrument_by_symbol('NSE', 'JSWSTEEL'), alice.get_instrument_by_symbol('NSE', 'JUBLFOOD'), alice.get_instrument_by_symbol('NSE', 'KOTAKBANK'), alice.get_instrument_by_symbol('NSE', 'L&TFH'), alice.get_instrument_by_symbol('NSE', 'LALPATHLAB'), alice.get_instrument_by_symbol('NSE', 'LICHSGFIN'), alice.get_instrument_by_symbol('NSE', 'LT'), alice.get_instrument_by_symbol('NSE', 'LTI'), alice.get_instrument_by_symbol('NSE', 'LTTS'), alice.get_instrument_by_symbol('NSE', 'LUPIN'), alice.get_instrument_by_symbol('NSE', 'M&M'), alice.get_instrument_by_symbol('NSE', 'M&MFIN'), alice.get_instrument_by_symbol('NSE', 'MANAPPURAM'), alice.get_instrument_by_symbol('NSE', 'MARICO'), alice.get_instrument_by_symbol('NSE', 'MARUTI'), alice.get_instrument_by_symbol('NSE', 'MCDOWELL-N'), alice.get_instrument_by_symbol('NSE', 'MFSL'), alice.get_instrument_by_symbol('NSE', 'MGL'), alice.get_instrument_by_symbol('NSE', 'MINDTREE'), alice.get_instrument_by_symbol('NSE', 'MOTHERSUMI'), alice.get_instrument_by_symbol('NSE', 'MPHASIS'), alice.get_instrument_by_symbol('NSE', 'MRF'), alice.get_instrument_by_symbol('NSE', 'MUTHOOTFIN'), alice.get_instrument_by_symbol('NSE', 'NAM-INDIA'), alice.get_instrument_by_symbol('NSE', 'NATIONALUM'), alice.get_instrument_by_symbol('NSE', 'NAUKRI'), alice.get_instrument_by_symbol('NSE', 'NAVINFLUOR'), alice.get_instrument_by_symbol('NSE', 'NESTLEIND'), alice.get_instrument_by_symbol('NSE', 'NMDC'), alice.get_instrument_by_symbol('NSE', 'NTPC'), alice.get_instrument_by_symbol('NSE', 'ONGC'), alice.get_instrument_by_symbol('NSE', 'PAGEIND'), alice.get_instrument_by_symbol('NSE', 'PEL'), alice.get_instrument_by_symbol('NSE', 'PETRONET'), alice.get_instrument_by_symbol('NSE', 'PFC'), alice.get_instrument_by_symbol('NSE', 'PFIZER'), alice.get_instrument_by_symbol('NSE', 'PIDILITIND'), alice.get_instrument_by_symbol('NSE', 'PIIND'), alice.get_instrument_by_symbol('NSE', 'PNB'), alice.get_instrument_by_symbol('NSE', 'POWERGRID'), alice.get_instrument_by_symbol('NSE', 'PVR'), alice.get_instrument_by_symbol('NSE', 'RAMCOCEM'), alice.get_instrument_by_symbol('NSE', 'RBLBANK'), alice.get_instrument_by_symbol('NSE', 'RECLTD'), alice.get_instrument_by_symbol('NSE', 'RELIANCE'), alice.get_instrument_by_symbol('NSE', 'SAIL'), alice.get_instrument_by_symbol('NSE', 'SBILIFE'), alice.get_instrument_by_symbol('NSE', 'SBIN'), alice.get_instrument_by_symbol('NSE', 'SHREECEM'), alice.get_instrument_by_symbol('NSE', 'SIEMENS'), alice.get_instrument_by_symbol('NSE', 'SRF'), alice.get_instrument_by_symbol('NSE', 'SRTRANSFIN'), alice.get_instrument_by_symbol('NSE', 'SUNPHARMA'), alice.get_instrument_by_symbol('NSE', 'SUNTV'), alice.get_instrument_by_symbol('NSE', 'TATACHEM'), alice.get_instrument_by_symbol('NSE', 'TATACONSUM'), alice.get_instrument_by_symbol('NSE', 'TATAMOTORS'), alice.get_instrument_by_symbol('NSE', 'TATAPOWER'), alice.get_instrument_by_symbol('NSE', 'TATASTEEL'), alice.get_instrument_by_symbol('NSE', 'TCS'), alice.get_instrument_by_symbol('NSE', 'TECHM'), alice.get_instrument_by_symbol('NSE', 'TITAN'), alice.get_instrument_by_symbol('NSE', 'TORNTPHARM'), alice.get_instrument_by_symbol('NSE', 'TORNTPOWER'), alice.get_instrument_by_symbol('NSE', 'TRENT'), alice.get_instrument_by_symbol('NSE', 'TVSMOTOR'), alice.get_instrument_by_symbol('NSE', 'UBL'), alice.get_instrument_by_symbol('NSE', 'ULTRACEMCO'), alice.get_instrument_by_symbol('NSE', 'UPL'), alice.get_instrument_by_symbol('NSE', 'VEDL'), alice.get_instrument_by_symbol('NSE', 'VOLTAS'), alice.get_instrument_by_symbol('NSE', 'WIPRO'), alice.get_instrument_by_symbol('NSE', 'ZEEL')]
socket_opened = False
#print(x)

def open_callback():
    global socket_opened
    socket_opened = True

try:
    alice.start_websocket(subscribe_callback=event_handler_quote_update, socket_open_callback=open_callback, run_in_background=True)
    while not socket_opened:
        pass
    alice.subscribe(instrument_list, LiveFeedType.COMPACT)
    # sleep(7200)
except KeyboardInterrupt:
    print("Unsubscribe")
    alice.unsubscribe(instrument_list, LiveFeedType.COMPACT)

krishnavelu / alice_blue

regarding running code and insert ticks into sqlite #288