Storing Live Zerodha WebSocket Ticks into MySQL Database

Let us see the WebSocket function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

def websocket_connection():

enctoken=token

userid ="XS0953"

zws = KiteTicker(api_key='kitefront', access_token='&user_id='+userid+'&enctoken='+urllib.parse.quote(enctoken), root='wss://ws.zerodha.com')

call_df = pd.read_csv('call_options.csv')

put_df = pd.read_csv('put_options.csv')

ltp_data={}

wanka=[]

def on_ticks(ws, ticks):

#print(ticks)

for x in ticks:

for y,z in x.items():

if z==13306370:

for y,z in x.items():

if y=='last_price':

#print(z, ":This is the last price")

wanka.append(z)

for sym in ticks:

ltp=sym["last_price"]

ltp_data[sym["instrument_token"]]=ltp

def on_connect(ws, response):

TokenList=[int(call_df.loc[0,["instrument_token"]].item()),

int(call_df.loc[1,["instrument_token"]].item()),

int(call_df.loc[2,["instrument_token"]].item()),

int(call_df.loc[3,["instrument_token"]].item()),

int(call_df.loc[4,["instrument_token"]].item()),

int(call_df.loc[5,["instrument_token"]].item()),

int(call_df.loc[6,["instrument_token"]].item()),

int(call_df.loc[7,["instrument_token"]].item()),

int(call_df.loc[8,["instrument_token"]].item()),

int(call_df.loc[9,["instrument_token"]].item()),

int(put_df.loc[0,["instrument_token"]].item()),

int(put_df.loc[1,["instrument_token"]].item()),

int(put_df.loc[2,["instrument_token"]].item()),

int(put_df.loc[3,["instrument_token"]].item()),

int(put_df.loc[4,["instrument_token"]].item()),

int(put_df.loc[5,["instrument_token"]].item()),

int(put_df.loc[6,["instrument_token"]].item()),

int(put_df.loc[7,["instrument_token"]].item()),

int(put_df.loc[8,["instrument_token"]].item()),

int(put_df.loc[9,["instrument_token"]].item())

]

ws.subscribe(TokenList)

ws.set_mode(ws.MODE_FULL, TokenList)

zws.on_ticks=on_ticks

zws.on_connect=on_connect

zws.connect(threaded=True)

while True:

t.sleep(0.25)

#print(ltp_data)

#print(wanka)

if len(wanka)>3:

print(wanka[-1])

connection = mysql.connector.connect(

host = "localhost",

user = "root",

password = "yourpassword",

database = "mydatabase")

cursor = connection.cursor()

sql = "insert into sample (time,price) values (%s,%s)"

val = (time.time(),wanka[-1])

cursor.execute(sql,val)

connection.commit()

for x,y in ltp_data.items():

if x==int(call_df.loc[0,["instrument_token"]].item()):

call_price_0.config(text=y)

if x==int(call_df.loc[1,["instrument_token"]].item()):

call_price_1.config(text=y)

if x==int(call_df.loc[2,["instrument_token"]].item()):

call_price_2.config(text=y)

if x==int(call_df.loc[3,["instrument_token"]].item()):

call_price_3.config(text=y)

if x==int(call_df.loc[4,["instrument_token"]].item()):

call_price_4.config(text=y)

if x==int(call_df.loc[5,["instrument_token"]].item()):

call_price_5.config(text=y)

if x==int(call_df.loc[6,["instrument_token"]].item()):

call_price_6.config(text=y)

if x==int(call_df.loc[7,["instrument_token"]].item()):

call_price_7.config(text=y)

if x==int(call_df.loc[8,["instrument_token"]].item()):

call_price_8.config(text=y)

if x==int(call_df.loc[9,["instrument_token"]].item()):

call_price_9.config(text=y)

if x==int(put_df.loc[0,["instrument_token"]].item()):

put_price_0.config(text=y)

if x==int(put_df.loc[1,["instrument_token"]].item()):

put_price_1.config(text=y)

if x==int(put_df.loc[2,["instrument_token"]].item()):

put_price_2.config(text=y)

if x==int(put_df.loc[3,["instrument_token"]].item()):

put_price_3.config(text=y)

if x==int(put_df.loc[4,["instrument_token"]].item()):

put_price_4.config(text=y)

if x==int(put_df.loc[5,["instrument_token"]].item()):

put_price_5.config(text=y)

if x==int(put_df.loc[6,["instrument_token"]].item()):

put_price_6.config(text=y)

if x==int(put_df.loc[7,["instrument_token"]].item()):

put_price_7.config(text=y)

if x==int(put_df.loc[8,["instrument_token"]].item()):

put_price_8.config(text=y)

if x==int(put_df.loc[9,["instrument_token"]].item()):

put_price_9.config(text=y)