Export Postgresql Table to excel with header

Question

i whrote a python script to export table from postgresql to Excel and it's works perfectly,but i get a file without header (only data) here is my code:

#!/usr/bin/python
import os
import psycopg2
"""Fichier en sortie"""
output_file_1=r"path\myfile.xls"

try:
    conn = psycopg2.connect(database="", user="", password="", host="", port="")
except:
    print "Connexion failed"

cur = conn.cursor()

try:
    cur.execute('''select * from table1''')

#cur2.execute("""select * from cores""");
except:
    print"Enable to execute query"
rows=cur.fetchall()
try:
    import xlwt
except ImportError: "import of xlwt module failed"

# Make spreadsheet
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet(os.path.split(output_file_1)[1])

worksheet.set_panes_frozen(True)
worksheet.set_horz_split_pos(0)
worksheet.set_remove_splits(True)

# Write rows
for rowidx, row in enumerate(rows):
    for colindex, col in enumerate(row):
    worksheet.write(rowidx, colindex, col)

# All done
workbook.save(output_file_1)
#print"finished"


print "finished!!"
conn.commit()
conn.close()

I want to get an excel file with header

mechanical_meat · Accepted Answer · 2016-06-24 16:44:39Z

1

You can use cur.description to get the column names. It returns a tuple of tuples with the column name as the first element of each contained tuple.

for colidx,heading in enumerate(cur.description):
    worksheet.write(0,colidx,heading[0]) # first element of each tuple

# Write rows
for rowidx, row in enumerate(rows):
    for colindex, col in enumerate(row):
    worksheet.write(rowidx+1, colindex, col) # increment `rowidx` by 1

Edit: to skip the first two rows and begin writing on the third row do something like:

rows_skipped = 2
for colidx,heading in enumerate(cur.description):
    worksheet.write(rows_skipped,colidx,heading[0])

# Write rows
for rowidx, row in enumerate(rows):
    for colindex, col in enumerate(row):
    worksheet.write(rowidx+rows_skipped, colindex, col)

edited Jun 24, 2016 at 16:44

answered Jun 23, 2016 at 16:19

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

1 Comment

Hchliyah Over a year ago

thank you it works, now i'm wondring how could I skip the 2 first rows and start whriting from the 3rd?

Collectives™ on Stack Overflow

Export Postgresql Table to excel with header

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related