How do I add column headers for each row of data in a loop?

Question

I am using Python3.6.

Database table example:

column1 . .column2  . .column3

....10    ...........20..............30

....100  .......     200.............300

Code:

# extracts all data for the rows without the column names
rows=cursor.fetchall()

for row in rows:
    print(row)

  10   20   30  
  100  200  300

How do I add the column names manually to this loop so that it is included in the output?

I am new to stackoverflow so this post will need improvements in formatting, content, etc., so I welcome any feedback.

Thank you!

jpp · Answer 1 · 2018-09-28T18:22:18.493

1

You can use cursor.description to extract headers and then iterate headers and data via itertools.chain:

from itertools import chain
from operator import itemgetter

headers = [list(map(itemgetter(0), cursor.description))]
rows = cursor.fetchall()

for row in chain(headers, rows):
    print(*row)

column1 column2 column3
10 20 30
100 200 300

If formatting as a table with consistent spacing is important, see Printing Lists as Tabular Data.

edited Sep 28 '18 at 18:22

answered Sep 28 '18 at 18:15

jpp

159,742
34
281
339

`headers = [desc[0] for desc in cursor.description]` from PEP 249 -- Python Database API Specification v2.0 [Cursor Attributes](https://www.python.org/dev/peps/pep-0249/#cursor-attributes). – Steven Rumbalski Sep 28 '18 at 18:16
1

@StevenRumbalski, Thanks, updated. I wasn't familiar with `cursor`. Just have to put it in another list for consistency. – jpp Sep 28 '18 at 18:17

score 0 · Answer 2 · answered Sep 28 '18 at 18:16

0

if you're adding column names manually just do print the column names outside of the for loop.

print("col1\tcol2\tcol3")
for row in rows:
   print(row)

answered Sep 28 '18 at 18:16

jason adams

545
2
15
30

score 0 · Answer 3 · answered Sep 28 '18 at 18:28

If you want the headers to be available with each row of data, make a DictCursor. In my knowledge, most popular MySQL, Oracle, Postgres libs support it.

Then you can do this:

conn = MySQLdb.connect(host,port,user,passwd,db)
cursor = van.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT * FROM table;")

# Avoid doing fetchall(). If your query resukt is big enough, it can even make your program run out of available memory of the system.
#rows=cursor.fetchall()

#Alternatively, iterate over the cursor itself, which is a generator

for row in cursor:
    print row

Reference: generator

How do I add column headers for each row of data in a loop?

3 Answers3