Python - Having trouble exporting to .csv from sql query

Question

Using Windows 10/PyCharm/Python 3.6 - am able to click a button on my UI (PySimpleGui), connect to sql and run my query. I can get the results to pour into a .csv file - but I'm not able to get the headers. Any help is appreciated! The button is labeled "GET ALL DATA"

Have tried massaging content from here, in various ways with numerous modifications to the syntax and structure - to no avail:

Python - write headers to csv

https://www.oreilly.com/library/view/python-cookbook/0596001673/ch08s11.html

https://kadler.github.io/2018/01/08/fetching-python-database-cursors-by-column-name.html#

Here's a sample of one button's click event

if event =="GET ALL DATA":
    connstring = 'DRIVER={SQL Server};SERVER=xxx;DATABASE=xxx;UID=xxx;PWD=xxx'
    SQLStr = "SELECT * FROM [TSCACS]"
    conn = pyodbc.connect(connstring)
    cursor = conn.cursor()
    cursor.execute(SQLStr)
    with open("F:\TOOLS\Button1Output.csv", 'w',encoding='utf-8') as myfile:
        mywriter = csv.writer(myfile, delimiter=',', lineterminator='\r')
        for row in cursor:
            mywriter.writerow(row)

Nearly every time I run it I get a new error message or simply no headers. The single table I'm querying currently has 56 columns and will likely grow to about 78, so I'd rather not have to hard code them anywhere!

Thank you for any assistance

normally SQL gives only data without headers - they are not part of data. And DB expects you already know names of columns. — furas
– furas, Commented Aug 2, 2019 at 5:49
in links you can see that you can get names from cursor.description. Probably this should give names [d[0] for d in cursor.description] — furas
– furas, Commented Aug 2, 2019 at 5:56
Thank you - I believe that is similar to some things I tried from those links - do you happen to know if it goes just above "for row in cursor"? I'm not seeing how it actually writes back to the file, and none of my tests have worked yet. Thanks! — user7729132
– user7729132, Commented Aug 2, 2019 at 6:01
headers = [d[0] for d in cursor.description], mywriter.writerow(headers). — furas
– furas, Commented Aug 2, 2019 at 6:59
Not sure why I'm not getting notified of updates here, so I apologize for my delayed response. Thank you so much, I'm all set! — user7729132
– user7729132, Commented Aug 9, 2019 at 11:43

furas · Accepted Answer · 2019-08-02 07:23:35Z

0

You should get headers from description

headers = [d[0] for d in cursor.description]

and then you can write them before you write rows

with open("F:\TOOLS\Button1Output.csv", 'w',encoding='utf-8') as myfile:
    mywriter = csv.writer(myfile, delimiter=',', lineterminator='\r')

    mywriter.writerow(headers)

    for row in cursor:
        mywriter.writerow(row)

Probably using writerows() (with s at the end of name) you could write rows with one line of code

    mywriter.writerow(headers)
    mywriter.writerows(cursor)

answered Aug 2, 2019 at 7:23

furas

149k12 gold badges121 silver badges171 bronze badges

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

Collectives™ on Stack Overflow

Python - Having trouble exporting to .csv from sql query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related