Python Forum
Create a Pie Chart using matplotlib from SQL query
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Create a Pie Chart using matplotlib from SQL query
#1
I wanted to be able to do the following:
  • Define a list to feed into an SQL query
  • Save the results of the Query into a list
  • Use the original list contents as a variable for labels to feed into a pie chart
  • Use the results of the SQL Query result list to pass values to the pie chart as values to calculate percentae

I should note that this may seem ugly and there is a lot of opportunity for cleanup. My code and approach to coding works but it is inefficient. So I appreciate any constructive feedback.

Below is what I came up with:
#!/usr/bin/python
#############################################################
############ IMPORT STATEMENTS                    ###########
#############################################################
import matplotlib        #Import seperate from the matplotlib we will use for the plot
matplotlib.use('Agg')    #Bypass setting the environmental variable for XDisplay
import MySQLdb           # will be used to query the database
import matplotlib.pyplot as plt    #import matplotlib to use for plot
import time #used for date
#############################################################
############ END IMPORT STATEMENTS                 ##########
#############################################################

PORcode = ['german shepherds', #PORcode is a list of text we want to pump into the SQL query later
'blacklabs',
'beagle',
'rotweiler']

def SQLGather(): #define a function to handle the SQL query
todaysdate = time.strftime("%d%m%Y")
db = MySQLdb.connect("localhost","USERNAME","PASSWORD","DATABASE" ) #pass parameters to connect to database and define function as db to call later
cursor = db.cursor()# prepare a cursor object using cursor() method
itemlist =[] #create an empty list that is able to be appended
for c in PORcode: #repeat the command for each item in list. basically for the POR codes in PORcode, write them to the variable itemlist. they will be called later to get the labels for the plot
itemlist.append(c) #append each item to the list
mylist = []#create an empty list that is able to be appended
for items in PORcode: #repeat the below commands in the loop for each list member 
   cursor.execute("select count(CLASSIFICATION) from TABLE where CLASSIFICATION='%s'" % items) #query the database where every item of PORcode will be pumped in for a seperate query
   result = cursor.fetchall() #cursor.fetchall shows all results from the querys in the iterative loop.
   for row in result: #iterate through the result of each SQL query and perform the following commands in the iterative loop
    l= map(int,row) #the count of the occurrence in the database will be a long. we need to turn it into an integer.
    mylist.append(l) #append all results of the SQL query to mylist
   ted= items,l[0] #define a variable that includes the item name, and then their number of occurrence
db.close()
labels = itemlist #this is the item list from earlier we will use to make labels. each item in the list PORcode.
counts = mylist #the results of each sql query count
fig1, ax1 = plt.subplots(figsize=(15,15)) #set the size of the plot
ax1.pie(counts,  autopct='%1.1f%%', shadow=True, startangle=90) #tell what to plot, how to show percent, if we want a shadow, and where to start the chart
ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.
plt.legend(labels,loc='upper center', bbox_to_anchor=(0.5, 1.13),
         ncol=2, fancybox=True, shadow=True) #set up the legend using labels, set the location, bbox to anchor can be used to move the box, set columns, and then som formatting
plt.savefig('PORpiechart%s.png' % todaysdate) #save my file instead of showing
SQLGather() #call the function to run
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020