Return SQL result from function as array

Question

Since the query returns more than 1 result, at the Get_results class how could i return the data_out as an array in order to iterate on the results of the query?

import psycopg2
import sys

class Get_results():
    def db_call(self,query,dbHost,dbName,dbUser,dbPass):
        try:
            con = None
            con = psycopg2.connect(host=dbHost, database=dbName,
                                   user=dbUser, password=dbPass)
            cur = con.cursor()
            cur.execute(query)
            data = cur.fetchall()
            for data_out in data:
                return data_out
        except psycopg2.DatabaseError, e:
                print 'Error %s' % e
                sys.exit(1)
        finally:
            if con:
                con.close()

 sql = " some sql "
 w = Get_results()
 for i in  w.db_call(sql, dbHost, dbName, dbUser, dbPass):
     print "The result is : " + i

For aditional info, when if i add print data right after data = cur.fetchall() i have the result:

[('The_Galaxy', 'The_Galaxy:star'),
 ('The_Galaxy', 'The_Galaxy:planet')]

Jon Clements · Accepted Answer · 2012-10-15 17:15:04Z

4

The immediate answer is to change:

for data_out in data:
    data_out result

to:

for data_out in data:
    yield data_out

But you should look at using a with statement (if the DB API supports it), and simplifying the code - this could just be done by making a generator function (a class is OTT for this)

answered Oct 15, 2012 at 17:15

Jon Clements

143k34 gold badges254 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

thclpr Over a year ago

Hi, there was a mistake on the code, i updated the line data_out result to return data_out

Jon Clements Over a year ago

@Thales Right - but either yield each row, or just return the cursor and iterate over that...

Prashant Kumar · Accepted Answer · 2012-10-15 17:37:01Z

3

import psycopg2
import sys

class Get_results():
    def db_call(self,query,dbHost,dbName,dbUser,dbPass):
        try:
            con = None
            con = psycopg2.connect(host=dbHost, database=dbName,
                                   user=dbUser, password=dbPass)
            cur = con.cursor()
            cur.execute(query)
            data = cur.fetchall()  
            resultList = []  
            for data_out in data:  
                resultList.append(data_out[1])  


            return resultList   
        except psycopg2.DatabaseError, e:
                print 'Error %s' % e
                sys.exit(1)
        finally:
            if con:
                con.close()

 sql = " some sql "
 w = Get_results()
 for i in  w.db_call(sql, dbHost, dbName, dbUser, dbPass):
     print "The result is : " + i

edited Oct 15, 2012 at 17:37

answered Oct 15, 2012 at 17:19

Prashant Kumar

3182 gold badges3 silver badges19 bronze badges

5 Comments

Prashant Kumar Over a year ago

this will print the value of column with index 0. To print other columns just change the index value in the return statement i.e. resultList.append(data_out[n])

thclpr Over a year ago

I tried your solution, but im still receiving one result only.

thclpr Over a year ago

Hi, the sql is simple , is "select * from table" it returns only 2 values.

Prashant Kumar Over a year ago

is it printing The_Galaxy twice in the result? if so use resultList.append(data_out[1]) and check the output.

thclpr Over a year ago

Prashant, really sorry, the code you described works. the failure was mine for inserting the return resultList inside the for data_out loop. Thanks a lot :)

Collectives™ on Stack Overflow

Return SQL result from function as array

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related