Retrieving live data from MySQL Database

In one of my posts, I have mentioned how to collect live data into your MySQL database. In this post, I’ll explain you how to retireve this live data from your MySQL database and then use it for calculations.

“mydatabase” has a table called “ticker”. This table has two columns. The first column is “time” and the second column is “price”.

The values of the “time” column are timestamps and the values of the price column are live prices.

Step 1 : Let us being by importing the necessary modules.

from threading import *

import mysql.connector

import time

Step 2 : Creating an empty list.

Here we have created an empty list, which we can populate with live data later on by appending.

ltp=[]

Step 3 : Writing a function to make database connection and then retrieve the data

A very important thing to note here is that we care making the database connection inside the function. That’s because it has to be live. If you don’t do it like this, you will be getting stale data.

On order to fetch live data, you will have to connect to your database each and every time a new data is being posted.

Also, note that retrieving data from cursor.fetchall() is not very simple, as it produces a list of tuples. Hence, I had to create multiple variables to fetch this data.

def price_update():

for i in range(20):

connection = mysql.connector.connect(

host = "localhost",

user = "root",

password="yourpassword",

database = "mydatabase"

)

cursor = connection.cursor()

sql="select price from ticker where time=(select max(time) from ticker)"

cursor.execute(sql)

price_list=cursor.fetchall()

price_tuple = price_list[0]

price = price_tuple[0]

ltp.append(price)

time.sleep(0.5)

i=i+1

You’ll notice that I’have used an SQL sub-query to get the last record. For that purpose I first created a column that was timestamped and then I used the max() function of SQL to get the last entered record.

Step 4 : Writing a function to use the retrieved data

Here, I have used “ltp[-1]” , to get the last item of the list.

def buy_sell():

for i in range (10):

time.sleep(1)

print(ltp[-1])

Step 5 : Enabling threading

If you do not employ threading, then one function will wait for the other function to complete & hence, the purpose of getting live data will be defeated. Hence, thread all your functions.

def threading_price_update():

t_price_update=Thread(target=price_update)

t_price_update.start()

def threading_buy_sell():

t_buy_sell=Thread(target=buy_sell)

t_buy_sell.start()

threading_price_update()

threading_buy_sell()

The Entire Code