Storing and Retrieving Tick Data using MySQL and Python

We want that a single table should store the price values of multiple instrument tokens.

So, we will create a table that will have four columns:

Let us first create a table.

mysql> create table ticks(

-> id int auto_increment primary key,

-> time datetime default current_timestamp,

-> last_price float(24),

-> instrument_token int(255));

Now, let us populate this with tick data.

Now the table looks like this.

Now, what if we want to retrieve the latest price of a particular instrument token.

We will execute the following sql command.

1	mysql> select last_price from ticks where time =(select max(time) from ticks where instrument_token=11230);

Here, we get the latest price of the instrument_token 11230.

Now, let’s see how this can be done with python.

import mysql.connector

import time

import datetime

connection = mysql.connector.connect(

host = "localhost",

user = "root",

password="yourpassword",

database = "mydatabase"

)

cursor = connection.cursor()

sql="select last_price from ticks where time =(select max(time) from ticks where instrument_token=11230)"

cursor.execute(sql)

price_list=cursor.fetchall()

price_tuple = price_list[0]

price = price_tuple[0]

print(price)