CODEWITHSUNDEEP
pythonmysqlpyodbc
Cenderze
Cenderze

Reputation: 1212

Getting my queries' results into a string so it can be printed

I'm having a problem with the following:

I have the function process_query as below:

def process_query(query, parameters):

    print query

    print parameters

    if(parameters is None):

        cursor1.execute(query)

        return cursor1.fetchall()

    else:

        cursor1.execute(query, parameters)

        return cursor1.fetchall()

which I call in the following function:

def recieving_sumrpt():

    theList=[]

    theList=cursor1.execute('SELECT DISTINCT SSN FROM Employee').fetchall()  

    print theList

    templist=[['SSN','Lname','FName','MInit','Known Hours','Unknown hours','Overtime']]

    for i in theList:

        basics= process_query('SELECT SSN, LName, FName, MInit FROM Employee WHERE SSN=?', (i)) 

        templist.append(basics)

        known_hours = process_query('SELECT sum(Coalesce(Hours,0)) FROM  Works_On WHERE ESSN=?', (i))

        print templist, known_hours

        templist.append(known_hours[0])
        count=cursor1.execute('SELECT Distinct ESSN FROM Works_On WHERE ISNULL(Hours) AND ESSN=?', (i)).rowcount

        if count >=1:

            templist.append('Yes')

        else:
            templist.append('No')

the trouble is when I print templist I have the following appearance:

[['SSN', 'Lname', 'FName', 'MInit', 'Known Hours', 'Unknown hours', 'Overtime'],

 [('123456789', 'Smith', 'John', 'B')], [(Decimal('40.0'), )], 'No', 'Yes', 

[('333445555', 'Wong', 'Franklin', 'T')], [(Decimal('40.0'), )], 'No', 'Yes', 

[('453453453', 'English', 'Joyce', 'A')], [(Decimal('40.0'), )], 'No', 'Yes', 

[('666884444', 'Narayan', 'Ramesh', 'K')], [(Decimal('40.0'), )], 'No', 'Yes', 

[('888665555', 'Borg', 'James', 'E')], [(Decimal('0.0'), )], 'Yes', 'Yes', 

[('987654321', 'Wallace', 'Jennifer', 'S')], [(Decimal('35.0'), )], 'No', 'Yes', 

[('987987987', 'Jabbar', 'Ahmad', 'V')], [(Decimal('40.0'), )], 'No', 'Yes', 

[('999887777', 'Zelaya', 'Alicia', 'J')], [(Decimal('40.0'), )], 'No', 'Yes']

Which I believe causes problems later when I want to print out my results:

def printing(list):

    for i in list:

        print '%-10s %-10s %-10s %-10s %-15s %-10s %-15s' % (i[0],i[1],i[2],i[3],i[4], i[5], i[6])

as this gives the error: "list index out of range"

which I believe stems from the fact that I do not have the following apperance:

['999887777', 'Zelaya', 'Alicia', 'J', Decimal('40.0'), 'No', 'Yes']

For each row. (Could be wrong though, this is my first python lab).

The output of the printing function is merely: 

SSN        Lname      FName      MInit      Known Hours     Unknown hours Overtime

And nothing more.

How can I make all this into something that can be printed? And is it possible for the decimal('40.0') appearance in the list to be removed? I've tried using append(float(known_hours[0]) but this gave me an error saying that known_hours must be a number or a string. Also, I haven't really figured out how to print yet; is there any problem with that function, or the process_query function as I have configured it quite a lot from the original, given, function?

Upvotes: 0

Views: 90

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

list = [['SSN', 'Lname', 'FName', 'MInit', 'Known Hours', 'Unknown hours', 'Overtime'],
        [('123456789', 'Smith', 'John', 'B')],
        [(Decimal('40.0'), )],
        'No',
        'Yes', 
        [('333445555', 'Wong', 'Franklin', 'T')], 
        [(Decimal('40.0'), )]
]

def printing(list):

    for i in list:

        print '%-10s %-10s %-10s %-10s %-15s %-10s %-15s' % (i[0],i[1],i[2],i[3],i[4], i[5], i[6])

It is most evident that the list items don't have 7 entries... As a matter of fact, while formatting properly the data you could see the content of the list is not even consistent. There are lists, list of tuples, stings, etc...

I will try to put you on the right track to clean-up the mess:

def recieving_sumrpt():
    theList = cursor1.execute('SELECT DISTINCT SSN FROM Employee').fetchall()  
    # You get a list of tupple containing each one SSN

    templist=[['SSN','Lname','FName','MInit','Known Hours','Unknown hours','Overtime']]
    # ok: a list of list of 7 strings.

    for i in theList: # for each SSN
        # here i contains a tuple of *1* element
        basics= process_query('SELECT SSN, LName, FName, MInit FROM Employee WHERE SSN=?', i) 
        # basics contains now a list on *one* tupple of *4* items

        templist.append(basics)

    ...

As you will see in the above comments, at this point, I think you are already on the wrong track. My guess is you want consistent list. Either with 4 or 7 items since you intend to display the result as some kind of table.

A last word: maybe the main difficulty here is that you didn't understand that fetch all return a "list of tupple". Each item in the list for each row. An the tuple contain one item for each column. As an example, while querying the SSN, you should obtain something like that:

[(1,), (2,), (3,)]
^ ^     
 \ \ each "row" is a tuple of 1 item because I selected only one *column*
  \
   \ a list of 3 items because there was 3 rows in my test table

Upvotes: 1

Related Questions