import mysql.connector as sql
db=sql.connect(host='localhost',user='root',password='root')
if db.is_connected():
print('DB Connected')
KiteConnect offers tick WebSocket data from this ticks data we can have last_price,timestamp and volume the required thing to perform our strategies for this data kiteconnect offer as historical data which costs around 2k but from this websocket we can save our 2k per month recurring charges by storing them into mysql database and fetching them.
we exactly need 3 python files so that it each one should not mess with others and mysql to store the tick data as explained step by step below:-
Step:1
Download WampServer and Launch the WampServer then on single click you will be able to have the list where you can find mysql console please click and launch.
Step:2
show databases; to check which database are available and after that create database algo;
Step:3
use algo; database will get change and will get assigned then create table ticks(last_price float(32),date timestamp);
step:4
insert into ticks(last_price,date) values(1234.5,'2018-06-05 06:30:00'); check with them with initial storing then close the mysql console if the query get assigned
Launch python idle you will be required pymysql and mysql.connector which you can pip install them further steps required to stream websocket and fetch them as follow:-
Step:1
Prepare a file name as py_mysql.py in which yo establish a connection with import pymysql then a pymysql.connect refering it's host,user,password and databse.
Step:2
define as insert_ticks to store ticks and point out the cursor as db.cursor then in for loop state tik in ticks by stating tick['last_price'] and tick['timestamp'] within curly braces.
Step:3
in try db.commit and except exception as db.rollback() if insertion fails
Step:4
Open another file and run the kiteticker for streaming and storing database as including a main code line which is insert_tick=insert_ticks(ticks)
Step:5
Open a read sql file call mysql.connector as mysql then establish database connection as done in first file then read as pd.read_sql with select *from ticks with con=db.
Step:6
Call pd.DataFrame then set date which is data.set_index(['date']) and for last_price it will be data.ix[:,['last_price']] then resample with variable['last_price'] and done you able to fetch ohlc().
Save this below code as py_mysql.py
import pymysql
db=pymysql.connect(host='localhost',user='root',password='',database='algo')
insert_into_table='insert into ticks(last_price,date) values(%(last_price)s,%(date)s)'
def insert_ticks(ticks):
c=db.cursor()
for tick in ticks:
c.execute(insert_into_table,{'last_price':tick['last_price'],
'date':tick['timestamp']})
try:
db.commit()
except Exception:
db.rollback()
Save the below code as kite-ticker.py for websocket streaming and inserting data into sql
Save the below code as mysql_connector.py for fetch of ohlc candle data and to perform strategies
import mysql.connector as sql
import pandas as pd
import numpy as np
db=sql.connect(host='localhost',user='root',password='',database='algo')
data=pd.read_sql('select *from ticks',con=db,parse_dates=True)
data=pd.DataFrame(data)
data=data.set_index(['date'])
ticks=data.ix[:,['last_price']]
data=ticks['last_price'].resample('1min').ohlc().dropna()
print(data['close'])
Please do watch the video till the end and follow for proper understanding any doubt or any other requirements please do Contact via WhatsApp to the given number in the website :)