Writing Oracle Output to a File

[Paul Hankin]

On Dec 26, 4:51 pm, t_rectenwald <t.rectenw... at gmail.com> wrote:
> On Dec 26, 10:36 am, t_rectenwald <t.rectenw... at gmail.com> wrote:
>
>
>
> > Hello,
>
> > I attempting to execute an Oracle query, and write the results to a
> > file in CSV format.  To do so, I've done the following:
>
> > import cx_Oracle
> > db = cx_Oracle.connect('user/pass at DBSID')
> > cursor = db.cursor()
> > cursor.arraysize = 500
> > cursor.execute(sql)
> > result = cursor.fetchall()
>
> > The above works great.  I'm able to connect to the database and print
> > out the results as a list of tuples.  Here is where I get lost.  How
> > do I work with a "list of tuples?"  My understanding is that a "list"
> > is basically an array (I don't come from a Python background).  Tuples
> > are a "collection of objects."  So, if I do...
>
> > print result[0]
>
> > I get the first row of the query, which would make sense.  The problem
> > is that I cannot seem to write tuples to a file.  I then do this...
>
> > csvFile = open("output.csv", "w")
> > csvFile = write(result[0])
> > csvFile.close
>
> > This generates an exception:
>
> > TypeError: argument 1 must be string or read-only character buffer,
> > not tuple
>
> > So, I'm a bit confused as to the best way to do this.  I guess I could
> > try to convert the tuples into strings, but am not sure if that is the
> > proper way to go.  Any help would be appreciated.  I've also seen a
> > csv module out there, but am not sure if that is needed in this
> > situation.
>
> > Best Regards,
> > Tom
>
> Hello,
>
> I was able to figure this out by using join to convert the tuples into
> strings, and then have those write to the filehandle:
>
> csvFile = open("output.csv", "w")
> for row in cursor.fetchall():
>     csvFile.write(','.join(row) + "\n")
> csvFile.close

As usual, the python standard library has functions that do what you
want! Using the csv module will help you avoid trouble when your data
contains commas or control characters such as newlines.

import csv
help(csv)

Suggests this code:
import csv
csv_file = open('output.csv', 'w')
csv_writer = csv.writer(csvFile)
csv_writer.writerows(cursor.fetchall())
csv_file.close()

--
Paul Hankin