I'll show you a program I use quite often that is similar to what you are doing, and try to explain the logic of the decisions. Of course, there is room for disagreement, but this may be helpful (of course, if I'm wrong anywhere, please feel free to correct me):
Code:
import postgresql
from numpy import *
from pylab import *
import matplotlib.pyplot as plt
db = postgresql.open(user = '#')
a = db.prepare('''select to_char(invoicedate, ' MM YYYY') as monthly, sum(qty)
from..... ;''')
def createLines(x):
xax, yax = [], []
for i in x:
xax.append(i[0])
yax.append(i[1])
return xax, yax
mon, tot = createLines(a())
plot(tot)
xticks(arange(len(mon)), mon, rotation = 90)
grid()
plt.title('Pieces Sold')
show()
The above program simply creates a line graph showing how much an item sells per month. This is not the complete program, but it shows what's important here.
The thing with your code is that you are allowing your functions to leak all over the place, which will wreak havoc now that you are attempting to extend it. The reason I say use SQL to create the list of tuples you want is for a few reasons:
- SQL is built for data management. What would happen if you ran, say, two queries, and one list is shorter than the other? This would be interesting to program, but SQL does a perfect job of mashing and matching data. If you have SQL tuples with no information, that missing information issue will be confined to SQL, and won't create mis-matches in your program when you start combining and sorting lists. SQL also has the immensely helpful "order by" command which will automatically sort the tuples you need. If you have uneven lists and then you attempt to sort the lists in python followed by zipping them together without confirming the correct data matches, you are likely going to have a major headache and some wildly incorrect answers.
- I only want to deal with well-defined data. In this case, I have a list that looks like:
Code:
[('01 2012', Decimal(1000), ('02 2012', Decimal(1000), .......]
I know that the list is ordered, and I know the exact format of the list I am always dealing with. With this information, I can create various lists and use them in generalized functions. Suppose, I wanted to do this:
Code:
a = db.prepare('''select to_char(invoicedate, ' MM YYYY') as monthly, sum(qty)
from..... ;''')
b = db.prepare('''select to_char(invoicedate, ' MM YYYY') as monthly, sum(qty)
from..... ;''')
c = db.prepare('''select to_char(invoicedate, ' MM YYYY') as monthly, sum(qty)
from..... ;''')
so all I have to do is:
Code:
def createLines(x):
xax, yax = [], []
for i in x:
xax.append(i[0])
yax.append(i[1])
return xax, yax
mon, tot = createLines(a())
mon2, tot2 = createLines(b())
mon2, tot2 = createLines(c())
And by doing nothing more than changing the SQL commands, I can now plot regional sales history.
Since I know how know the exact format that I am dealing with, I can easily create the following:
Code:
a = db.prepare('''select to_char(invoicedate, ' MM YYYY') as monthly, sum(qty)
from..... ;''')
def createLines(x):
xax, yax = [], []
for i in x:
xax.append(i[0])
yax.append(i[1])
return xax, yax
def compare_stuff(x):
pass
def do_this(x):
pass
mon, tot = createLines(a())
mon2, tot2 = compare_stuff(a())
mon2, tot2 = do_this(a())
And if I want to create a text table:
Code:
for i in a():
print(i[0], '\t', i[1])
It's easy to extend the code to do all sorts of neat things, from graphing, to printing, to i/o, simply by keeping sure that the features of SQL are contained in the SQL part, and the list contained in the body of the program, and then keeping the functionality contained in the functions. All I do is pass lists around to each function. Keeping track of the format of one data-type in your program will be much easier, and if you have an error, you'll be able to tell where it came from. A SQL error will be much different than a list or function-call error.
