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.
