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.