Aliceblue stream to database

Function to store tick data to sqlite database

def insert_ticks(message):
try:
con = sqlite3.connect(dbfile)
c = con.cursor()
tok = “TOKEN”+str(message[‘token’])
vals = [time.strftime(’%Y-%m-%d %H:%M:%S’, time.localtime(message[‘exchange_time_stamp’])),message[‘ltp’], message[‘volume’]]
query = “INSERT INTO {}(ts,price,volume) VALUES (?,?,?)”.format(tok)
con.execute(query,vals)
except:
pass
try:
con.commit()
except:
con.rollback()

2 Likes

Thank bro. :slight_smile: This will help many people for sure.

Sir, Please help me i am updating in event_handler_quote_update(message):
but i am getting socket error
i am using in aliceblue only

Paste the full error? Otherwise how can anyone will tell anything

Problem solved
Thank you

1 Like

{'exchange': 'NSE', 'token': 3045, 'ltp': 196.0, 'change': 4294967226, 'exchange_time_stamp': 1603785487, 'volume': 26000881, 'instrument': Instrument(exchange='NSE', token=3045, symbol='SBIN', name='STATE BANK OF INDIA', expiry=None, lot_size=None)}
How can i read the content from instrument key. It’s namedtuple should i parse it?

Hi bro, thanks a lot for sharing this but can you guide me or share a code that explains totally how to store alice blue tick data in a sql database and how i can convert into ohlc data. I am a newbie and this will help me a lot in learning and creating my own algos

Goto http://unofficed.com/diary

Check the Kite Connect Tutorial Section. Shitton of examples.

Cool, I’ll try to learn from there meanwhile is there any particular example using Alice blue API?, just asking , i’ll understand better

Once You get the datastream out of any API. It is same data! What is important is knowhow of handling of that stream using Pandas Frame.

I was supposed to make content on Aliceblue but fuckers did not paid some of my client’s wrongly charged penalities. So wait abit.

Damn , I have learned how to handle data using pandas data frame, i know i can use resample and ohlc functions once i have the data , my main problem is extracting tick data from Alice blue API and storing it in a sql database, there are shit ton of examples for kiteconnect all over the internet but not for Aliceblue and as i am a newbie i am trying to use Aliceblue’s free API , I hope you guys can help me

#1 SQL Database

Why? Use Timeseries database. InfluxDB. SQL is pathetic for this purpose. You will get lots of example using Kite.
influxdb-python.readthedocs.io/en/latest/examples.html

#2 Content on Alice Blue

If You’re Unofficed’s client, DM me your username. I will throw you some sample code.

Otherwise, Wait slightly for Public Article rollouts. Right now, We are holding further stuff to get a bargaining chip for them to act and fix my client’s problem. Why promote those piece of shits unless They can take care of the clients who are in problem,

Thanks bro, Ill go through everything, their customer service is horrible for sure , have witnessed it first hand
Ill also check out unofficed’s services and will sign up

1 Like

We have unionized this system. So that Our issues get first priority. Like a mild version of technical kidnapping. We don’t do x unless y is done type.

Hi bro , can you help me this, what am i doing wrong, the code is running fine but nothing is being stored in the database
below is the code, hope you can help

#Alice Blue object

access_token =  AliceBlue.login_and_get_access_token(username=username, 
password=password, twoFA=twoFA,  api_secret=api_secret, app_id=app_id)

#Instruments dump

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

symbols = alice.get_instrument_by_symbol('NSE', 'Nifty Bank')

#Websocket Config

socket_opened = False
def event_handler_quote_update(message):
    
    global ticks
    ticks = message
    print(ticks)
   
def open_callback():
    global socket_opened
    socket_opened = True
    
alice.start_websocket(subscribe_callback=event_handler_quote_update,
                      socket_open_callback=open_callback,
                      run_in_background=True) 
while(socket_opened==False):
    pass


#TICK DATABASE - Storing

    def insert_ticks(message):
        try:    
            con = sqlite3.connect("C:/Users/Prakul/Desktop/tickdata.db")
            c = con.cursor()
            tok = "TOKEN"+str(message['token'])
            vals = [time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(message['exchange_time_stamp'])),message['ltp'], message['volume']]
            query = "INSERT INTO {}(ts,price,volume) VALUES (?,?,?)".format(tok)
            con.execute(query,vals)
        except:
            pass
        try:
            con.commit()
        except:
            con.rollback()

insert_ticks(symbols)

#Getting live tick data
alice.subscribe(symbols,LiveFeedType.MARKET_DATA)

Remove the try except here and run it to see if it is throwing an error.

Did that and got this error

Traceback (most recent call last):

File “”, line 15, in
insert_ticks(symbols)

File “”, line 4, in insert_ticks
tok = “TOKEN”+str(message[‘token’])

TypeError: tuple indices must be integers or slices, not str

Anything bro? any help will be appreciated

No clue. I usually figure out from internet. Try Stackoverflow.