how do you write output of sql to a csv file in python

Question

I am trying to connect to oracle table and execute a sql. I need to export result set to a csv file. My code is below:

import pyodbc
import csv

cnxn = pyodbc.connect("DSN=11g;UID=test101;PWD=passwd")
cursor = cnxn.cursor()
cursor.execute(sql)
row = cursor.fetchall()
with open('data.csv', 'w', newline='') as fp:
    a = csv.writer(fp, delimiter=',')
    for line in row:
        a.writerows(line)

cursor.close()

when I do print to line within for loop, I get something like this:

('Production', 'farm1', 'dc1prb01', 'web')
('Production', 'farv2', 'dc2pr2db01', 'app.3')
('Production', 'farm5', 'dc2pr2db02', 'db.3')

this is not working. Any ideas what I might be missing?

Answer 1

It would be writerow for a single row:

 a.writerow(line)

writerows expects an iterable of iterables, so it will iterate over the substrings writing each char individually.

If you want to use writerows call it on row:

    row = cursor.fetchall()
    with open('data.csv', 'w', newline='') as fp:
            a = csv.writer(fp, delimiter=',')
            a.writerows(row)

If you are using python2 remove newline='', newline is a *python*3 keyword:

  row = cursor.fetchall()
    with open('data.csv', 'w') as fp:
            a = csv.writer(fp, delimiter=',')
            a.writerows(row)