How do I write a header row using csv.writer when using a sqlalchemy cursor?

Question

The questions stems from my previous question here. By switching from using pandas to run the query and output to csv to using sqlalchemy and csv.writer, I've lost the header row.

I apologize for the example code, as it's only going to be runnable if you have a SQL Server. Running this code, you get the output "hello world|goodbye world" and the headers (a and b, respectively) are missing.

from sqlalchemy.engine import URL
from sqlalchemy import create_engine
import csv

serverName = 'FOO'
databaseName = 'BAR'

connection_string = ("Driver={SQL Server};"
            "Server=" + serverName + ";"
            "Database=" + databaseName + ";"
            "Trusted_Connection=yes;")

connection_url = URL.create("mssql+pyodbc", query={"odbc_connect":connection_string })
engine = create_engine(connection_url)
connection = engine.raw_connection()
cursor = connection.cursor()

sql = "select 'hello world' as 'a', 'goodbye world' as 'b'"

with open('foo.csv', 'w') as file:
    w = csv.writer(file, delimiter='|', )
    for row in cursor.execute(sql):
        w.writerow(row)

cursor.close()

Not shown, as I was minimizing the code, is that my actual code grabs all .sql files in a given directory and runs each in turn, outputting the results to csv. As such, the header needs to be dynamically added, not hard-coded as the first row.

A user mentioned this post as potentially helpful, but I can't seem to find a way to use .keys() when using a cursor. Given the size of some of the queries running, running the query once to simply return the header, then again for the rows, isn't a possible solution.

I also found this which seems to say that I can return CursorResult.keys() to write the column headers, but I'm unsure how to use that in the script above, as I can't find any object that has the attribute 'CursorResult'.

How do I get the header row from the sql query written to the csv as the expected first row when using a cursor?

does `execute(sql).keys()` give you the columns? You might have to do something like `rows = cursor.execute(sql)` then `columns = rows.keys()` then your `for row in rows:` This is just from distant memory — JonSG, Jan 05 '23 at 15:40
Does this answer your question? [SQLAlchemy - How to access column names from ResultProxy and write to CSV headers](https://stackoverflow.com/questions/51549821/sqlalchemy-how-to-access-column-names-from-resultproxy-and-write-to-csv-header) — JonSG, Jan 05 '23 at 15:40
That doesn't seem to work when using a cursor. I tried adding a counter "i" and if i==0 then w.writerow(cursor.keys()), which results in "cursor object has no attribute 'keys'". Same result for trying row.keys(), w.keys(). — Sky, Jan 05 '23 at 15:53

score 0 · Answer 1 · answered Jan 05 '23 at 16:46

When using a raw pyodbc cursor you can get the column names from Cursor.description after you .execute():

cnxn = engine.raw_connection()
crsr = cnxn.cursor()
crsr.execute("SELECT 1 AS foo, 2 AS bar")
col_names = [x[0] for x in crsr.description]
print(col_names)  # ['foo', 'bar']

How do I write a header row using csv.writer when using a sqlalchemy cursor?

1 Answers1