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?

Can you explain - this is not working ? How is it not working? What else are you getting? — Anand S Kumar
– Anand S Kumar, Commented Aug 19, 2015 at 20:25
He just used the wrong method for csv.writer, Padraic answer is the correct one. — LuRsT
– LuRsT, Commented Aug 19, 2015 at 20:29
I get this error: with open('data.csv', 'w', newline='') as fp: TypeError: 'newline' is an invalid keyword argument for this function — user1471980
– user1471980, Commented Aug 19, 2015 at 20:31

Padraic Cunningham · Accepted Answer · 2015-08-19 20:36:08Z

6

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)

edited Aug 19, 2015 at 20:36

answered Aug 19, 2015 at 20:27

Padraic Cunningham

181k30 gold badges264 silver badges327 bronze badges

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

4 Comments

user1471980 Over a year ago

still not able to execute the sql

Padraic Cunningham Over a year ago

If you see ('Production', 'farm1', 'dc1prb01', 'web') how is the sql not working? Using writerows on that will give you P,r,o,d,u,c,t,i,o,n. ....

user1471980 Over a year ago

If I dont use the csv, and just print the line, I can print.

Padraic Cunningham Over a year ago

@user1471980, newline="" is for python3 not python2

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related