Python3 + Matplotlib + Sqlite3

by: davidjackson1955, 9 years ago


I'm trying to follow you tutorial for SQLite and Matplotlib. but am running some problems.
Sql query produces: Date, Group, spots,wolf,  I'm lookiing for a bar chart.
Any help would be appreciated.

Here is the Pyghon code :


  mport sqlite3
import matplotlib as plt
import numpy as np

conn = sqlite3.connect('aavso.sqlite')
c = conn.cursor()
sql = "SELECT moyr,sum(grp),sum(spot),sum(wolf) FROM sunspots GROUP BY moyr ORDER BY PK_UID ASC"
# month_year = 'January 2015'

graphArray = []

# def readData():
for row in c.execute(sql):

    startingInfo = str(row).replace(')','').replace('(','').replace('u'','').replace("'","")
    splitInfo = startingInfo.split(',')
    graphArrayAppend = splitInfo[0]+','+splitInfo[1]
    graphArray.append(graphArrayAppend)

fig = plt.figure()
rect = fig.patch

ax1 = fig.add_subplot(1,1,1, axisbg='white')
plt.show()






You must be logged in to post. Please login or register an account.



What are the errors so far? I don't have access to your database, so I haven't run the code.

What I do see is you're appending and stuff, and you're calling for plt.show, but you never actually plot anything with plt.plot().

Also, just in case anyone sees this later:

import matplotlib as plt

should be
import matplotlib.pyplot as plt




-Harrison 9 years ago
Last edited 9 years ago

You must be logged in to post. Please login or register an account.


The query  returns the sum of four fields; Month/Year, Group, Spot, Wolf:
(If you want I can send you a copy of my db or csv file?)

('November 2014', 77, 144, 914)
('December 2014', 48, 86, 566)
('January 2015', 72, 208, 928)

What I'm looking for is a simple graph with month/year along the X axis and one of the other three columns along the Y axis. The errors I'm getting our:

Traceback (most recent call last):
  File "graph_sum.py3", line 20, in <module>
    fig = plt.figure()
AttributeError: 'module' object has no attribute 'figure'


-davidjackson1955 9 years ago
Last edited 9 years ago

You must be logged in to post. Please login or register an account.


Sure, do that. I am about to go to bed, but there are a lot of little issues with the code. I recognize this code is from my tutorial from SQLite, which is pretty old... and has some pretty poor code. The whole conversion to str and then sep nonsense is toxic, you definitely don't want to be doing any of that, sorry that this tutorial is still up.

I forgot about that series til now, deeefinitely need to re-do it. Send me all the required parts, along with your current code, and I will get it working and explain what you need to do.

As for the specific error with fig=plt.figure(), at least at a glance, I am not seeing why that is throwing an error. That part really shouldn't be causing an error.

That said, as long as you aren't planning to have multiple axis, you really don't need to define fig and all that, can just straight plt.bar, example: https://pythonprogramming.net/bar-chart-histogram-matplotlib-tutorial/

For the rest, just shoot me an email harrison@pythonprogramming.net, will get you situated.


-Harrison 9 years ago

You must be logged in to post. Please login or register an account.


The question is how do we pass the results from an SQL query to Matplotlib "x" and "y"  axles?

-davidjackson1955 9 years ago

You must be logged in to post. Please login or register an account.

Aright, so the major issues were:

import matplotlib as plt
should be 
import matplotlib.pyplot as plt


Next, you were never actually plotting anything, then the row handling for the data was just plain bad (my code, I know... but yikes), then finally we needed to handle the date data to be graphed. 

import sqlite3
import matplotlib.pyplot as plt
#import numpy as np
# used to basically convert any str of a date to a typical datestamp obj
from dateutil import parser

conn = sqlite3.connect('aavso.sqlite')
c = conn.cursor()
sql = "SELECT moyr,sum(grp),sum(spot),sum(wolf) FROM sunspots GROUP BY moyr ORDER BY PK_UID ASC"

for row in c.execute(sql):
    # using the dateutil parser to convert a str
    # date time to an actual datetime.datetime object
    # which matplotlib wants.
    date_data = parser.parse(row[0])
    value = row[1]
    plt.bar(date_data,value)
    
plt.show()


At least on my end, that works. You may need to acquire dateutil, 
pip install dateutil


-Harrison 9 years ago

You must be logged in to post. Please login or register an account.


That script is a lot clearner that the one you had in that old tutorial :)

-davidjackson1955 9 years ago

You must be logged in to post. Please login or register an account.

Since you mentioned Pandas in an email, here's a Pandas version, along with matplotlib styles and some comments regarding methods used.:

import sqlite3
import pandas as pd
from dateutil import parser
import matplotlib.pyplot as plt
# not necessary, but easy on the eyes:
from matplotlib import style
style.use('ggplot')

# connection object: 
conn = sqlite3.connect('aavso.sqlite')
#query
sql = "SELECT moyr,sum(grp),sum(spot),sum(wolf) FROM sunspots GROUP BY moyr ORDER BY PK_UID ASC"
# read sql input with pandas:
# more info: https://pythonprogramming.net/input-output-data-analysis-python-pandas-tutorial/
df = pd.read_sql(sql, conn)

# maps a function.
# more info: https://pythonprogramming.net/rolling-apply-mapping-functions-data-analysis-python-pandas-tutorial/
df['moyr'] = list(map(parser.parse,df['moyr']))
plt.plot(df['moyr'], df['sum(grp)'])
plt.show()


Unfortunately, I couldn't get a bar without losing the dates. Most likely, would need to convert the dates to the matplotlib datetypes, but.....meh.

-Harrison 9 years ago

You must be logged in to post. Please login or register an account.


Thanks for both of the scripts. The line graph works just fine.

-davidjackson1955 9 years ago

You must be logged in to post. Please login or register an account.