Pulling data from MySQL into Google Charts using Django

by Kodey

Pulling data from MySQL into Django is pretty straightforward. This article will show you some code samples of exactly that.

In the below, you’ll see I have a load of DB queries. Each query is handled in a specific way and then a value is returned. Those values are populated into the Python dictionary at the very bottom of the view definition.

def home(request): 
    mydb = mysql.connector.connect(
    host="your db host",
    user="username",
    password="password",
    database="database name"
    )
    mycursor = mydb.cursor()
    #
    #DAY OF WEEK
    #
    mycursor.execute("SELECT day, ic FROM (SELECT day, avg(interactionCount) as ic, case when day = 'Mon' then 1 when day = 'Tue' then 2 when day = 'Wed' then 3 when day = 'Thu' then 4 when day ='Fri' then 5 when day = 'Sat' then 6 when day = 'Sun' then 7 end as day_num FROM tw_posts group by day)t order by day_num asc")
    posts = [['day', 'ic']]
    for x in mycursor:
        day = str(x[0])
        ic = float(x[1])
        inner = [day, ic]
        posts.append(inner)
    #
    #TIME OF DAY
    #
    mycursor.execute("SELECT hour, avg(interactionCount) as ic FROM tw_posts group by hour order by hour asc")
    hours = [['hour', 'ic']]
    for x in mycursor:
        hour = str(x[0])
        ic = float(x[1])
        hourx = [hour, ic]
        hours.append(hourx)
    #
    #DAY OF WEEK DAY NAME
    #
    mycursor.execute("select day from (SELECT day, avg(interactionCount) as ic FROM tw_posts group by day)t order by ic desc limit 1")
    for x in mycursor:
        day = str(x[0])
   
    mycursor.execute("select day from (SELECT day, avg(interactionCount) as ic FROM tw_posts group by day)t order by ic asc limit 1")
    for x in mycursor:
        worstday = str(x[0])
    #
    #HOUR OF WEEK DAY NAME
    #
    mycursor.execute("select hour from (SELECT hour, avg(interactionCount) as ic FROM tw_posts group by hour)t order by ic desc limit 1")
    for x in mycursor:
        besthour = str(x[0])
    mycursor.execute("select hour from (SELECT hour, avg(interactionCount) as ic FROM tw_posts group by hour)t order by ic asc limit 1")
    for x in mycursor:
        worsthour = str(x[0])
    #
    #follower count
    #
    mycursor.execute("select currentTime, followersCount from tw_profile where handle = 'Lewis Hamilton'")
    followers = []
    for x in mycursor:
        date = str(x[0])
        count = str(x[1])
        followers.append([date, count])
    #
    #Max RT
    #
    mycursor.execute("select CreatedAt, favoriteCount, RetweetCount, day, hour from tw_posts order by interactionCount desc limit 1")
    for x in mycursor:
        created = str(x[0])
        favorite = str(x[1])
        retweet = str(x[2])
        mostpopday = str(x[3])
        mostpophour = str(x[4])
   
   #
   #AVG Tweet
   #
    mycursor.execute("select avg(favoriteCount) as avg_fav, avg(RetweetCount) as avgrt from tw_posts")
    for x in mycursor:
        avgfav = int(x[0])
        avgrt = int(x[1])
    #
    #most posted day
    #
    mycursor.execute("select day, count(*) as countday from tw_posts group by day order by countday desc limit 1")
    for x in mycursor:
        mostposted = str(x[0])
    #
    #Media Influence
    #
    mycursor.execute("select avg(fav_media) as fm, avg(rt_media) as rm, avg(rt_nomedia) as rnm, avg(fav_nomedia) as fnm from (select case when media_count >0 then avg(FavoriteCount) end as fav_media, case when media_count >0 then avg(RetweetCount) end as rt_media, case when media_count =0 then avg(RetweetCount) end as rt_nomedia, case when media_count =0 then avg(FavoriteCount) end as fav_nomedia from tw_posts where ProfileName = 'LewisHamilton' group by media_count)x")
    for x in mycursor:
        fav_media = int(x[0])
        fav_no_media = int(x[3])
        rt_media = int(x[1])
        rt_no_media = int(x[2])
    return render(request, 'kodey/home.html', {'data': posts, 'fav_media': fav_media, 'fav_no_media': fav_no_media, 'rt_media': rt_media, 'rt_no_media': rt_no_media, 'mostposted': mostposted, 'avgfav': avgfav, 'avgrt': avgrt, 'hours': hours, 'day': day, 'mostpopday': mostpopday, 'mostpophour': mostpophour, 'mostpopular_date': created, 'mostpopularrt': retweet, 'mostpopularfav': favorite, 'worstday': worstday, 'besthour': besthour, 'worsthour': worsthour, 'followers': followers})

Then, we pass the data into the Google chart – here you can see {{ hours|safe }} – hours being one of the variables passed from the view and safe meaning that there are no more characters that need escaping in the HTML.

<pre class="wp-block-syntaxhighlighter-code"> <!-- GOOGLE CHART HOURS -->
         <a href="https://www.gstatic.com/charts/loader.js">https://www.gstatic.com/charts/loader.js</a>
             <script type="text/javascript">
    google.charts.load('current', {packages: ['corechart', 'bar']});
google.charts.setOnLoadCallback(drawBasic);
function drawBasic() {
      var data3 = google.visualization.arrayToDataTable(
	      {{ hours|safe }}
      );
      var options = {
            chartArea: {
        left: 50,
        top: 5,
        right: 5,
        bottom: 50,
        width: 500,
        height: 300
    },
        hAxis: {
          title: 'Average Interactions',
          minValue: 0
        },
      };
      var hours = new google.visualization.BarChart(document.getElementById('hours_div'));
      hours.draw(data3, options);</pre>

With this method, I’ve made some pretty cool visualizations based on Twitter data. Here, I have created two bar charts based on interactions per day and per hour. I’ve then done a text summary of those bar charts in the middle, also injecting data from the database.

Tags:  
Kodey