how to export SQL query to excel with column names

Question

The code below will accept an sql file and export the data to excel using xlsxwriter but I cannot seem to get it to export with the column names as well (header). I can get it to display the column names onto the screen though, but cannot get it to add it as the first row to the excel file.

I did notice that if I change to using DictCursor instead of SSCursor, the excel file will instead be filled with just the column names repeated many times.

import MySQLdb
import sys
from xlsxwriter.workbook import Workbook
import MySQLdb.cursors as cursors

reload(sys)  
sys.setdefaultencoding('ISO-8859-1')

sql_file = sys.argv[1]
target_file = sys.argv[2]
target_sheet = sys.argv[3]

if sys.version_info[0] == 2:  # Not named on 2.6
    access = 'wb'
    kwargs = {}
else:
    access = 'wt'
    kwargs = {'newline':''}

workbook = Workbook(target_file)
worksheet = workbook.add_worksheet(target_sheet)

conn = MySQLdb.connect(host="localhost",
                     user="username",
                     passwd="password",
                     db="database"
                     , compress=1
                     )

with open(sql_file, 'r') as sql_script:
    sql_string = sql_script.read()


cur = conn.cursor(cursorclass=MySQLdb.cursors.SSCursor)
#print sql_string
cur.execute(sql_string)
results = cur.fetchall()

print ([i[0] for i in cur.description])

cur.close()
conn.close()

for r, row in enumerate(results):
    for c, col in enumerate(row):
        worksheet.write(r, c, col)

workbook.close()

Jahziel Rae Arceo · Accepted Answer · 2018-02-28 10:03:41Z

2

You can get the column names and write them in the first row, then proceed with writing the row values.

cols = [i[0] for i in cur.description]
for c, col in enumerate(cols):
    worksheet.write(0, c, col)

For writing the row values:

for r, row in enumerate(results):
    for c, col in enumerate(row):
        worksheet.write(r+1, c, col)

edited Feb 28, 2018 at 10:03

answered Feb 28, 2018 at 9:34

Jahziel Rae Arceo

1111 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

how to export SQL query to excel with column names

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related