psycopg2: error in display many columns of data in Python connecting to PostgreSQL

Question

I am using psycopg2 to connect to Postgres database and return query results on the screen using Python. I can only return one column of data, not many columns like PSQL does. Please see my code. Where did I do wrong?

Your kind response would be greatly appreciated.

#!/usr/bin/python


import psycopg2

CONNSTR = """
  host=localhost 
  dbname=wa
  user=super
  password=test
  port=5432"""
cxn = psycopg2.connect(CONNSTR)
cur = cxn.cursor()
cur.execute("""SELECT procpid,usename,current_query FROM pg_stat_activity;""")

rows = cur.fetchall()

print "\nShow me the query results:\n"
for row in rows:
        print "   ", row[1]

Answer 1

I found the answer on initd.org/psycopg/docs/cursor.html. Here is the correct code. Please see the last two lines of code with the changes.

For clarification, the first version of code only returns one column of data. The 2nd version listed below will return, actually it will display/print, all the columns I selected.

#!/usr/bin/python

import psycopg2

CONNSTR = """
  host=localhost 
  dbname=wa
  user=super
  password=test
  port=5432"""
cxn = psycopg2.connect(CONNSTR)
cur = cxn.cursor()
cur.execute("""SELECT procpid,usename,current_query FROM pg_stat_activity;""")

for rows in cur: 
print rows

Answer 2

It is not quite clear to me what you are trying to achieve.

If you only need one column of data, then you can filter that out in the SQL query. For instance:

cur.execute("""SELECT procpid FROM pg_stat_activity;""")

Still psycopg2 will return an array of tuples, where each tuple contains the columns you requested for. If you do not want the tuples and just an array of values for your the column-data, then you can convert them using:

colum_data = [r[1] for r in rows]

If this is not what you are asking for, please rephrase your question.