Writing Oracle Output to a File

[t_rectenwald]

On Dec 26, 12:10 pm, t_rectenwald <t.rectenw... at gmail.com> wrote:
> On Dec 26, 12:06 pm, Paul Hankin <paul.han... at gmail.com> wrote:
>
>
>
>
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for the tip.  I'll read up on the csv module and use that
> instead.  I'm already running into errors with null values, etc... and
> I believe some of the data in this DB will have commas, so this will
> be a much cleaner way of doing things.
>
> Regards,
> Tom- Hide quoted text -
>
> - Show quoted text -

I read up on the csv module.  BTW, thanks again!  That took care of
null values, I didn't even have to iterate anything in a loop, or
convert the tuples.  Great stuff.  I'm loving Python.

Regards,
Tom