Question

I want to create a d3 map visualization with timeseries data which pulls data from a mysql table. The map will change data every second which would jump from data which contain x datetime to data which contain x+1 datetime (for example January 1 data to January 2). How can I do this? I already know how to set up a Flask API using Python which connects my visual to the database. I just need the SQL Query to change every second so that the plots in the map will change every second as well. Thanks!

Answer 1

So to get the datetime data from the database you first need to understand how the data is stored in database.

If the data is stored in DATETIME FORMAT so it should be like this YYYY-MM-DD HH:MI:SS.

If the data is stored in DATE Format it should be like this YYYY-MM-DD.

Since you asked for the data day wise, I am assuming it is stored in DATE format so to get the data you can make a function of python to get data and you could use the python time library to update the visual every second and python datetime library to iterate over dates.

Let me show you the query with an example to get data from the db every second.

import datetime

import mysql.connector

import time

# Connectivity to the database

db = mysql.connector.connect(

    host = 'localhost'

    user='user123'

    passwd = 'passwd123'

    database = 'mydb'

)

mycursor = db.cursor()

# Specify your begining date

startdate = datetime.date(2019,1,2)

# Next value for the while loop

next = startdate + datetime.date.resolution

# Specify your end date

enddate = datetime.date(2099,12,30)

while next <= enddate:

    currentdate = startdate

    # Executing the query

    mycursor.execute("""

        SELECT * FROM table WHERE date = %s

        """, (currentdate))

    

    # These are the values for the specific date

    value = mycursor.fetchone()

    print(value)

    # Changing the date to the next date

    currentdate = next

    next = currentdate + datetime.date.resolution

    time.sleep(1)

   

So Let me explain the code a bit, to iterate over the dates we have used the DATETIME module of python.

You need to specify the starting and ending date of your dataset and then it'll do the rest. The 'values' variable gets the data you need. You just need to specify the db, the credentials, start time, end time and query. The TIME module is used to get the data every one second. Just implement the logic in your code and it will be working like a charm.

THANK YOU.