Writing Oracle Output to a File

[t_rectenwald]

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@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

 

[Ralf Schönian]

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@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

Hi,

have a look at the csv Module: http://docs.python.org/lib/csv-examples.html

Just iterate over your result.

# Untested
import csv
writer = csv.writer(open("some.csv", "wb"))

for row in result:
row = map(str,row)
writer.writerows(row)
writer.close()


Ralf Schoenian

 

[Ralf Schönian]

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@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

Hi,

have a look at the csv Module: http://docs.python.org/lib/csv-examples.html

Just iterate over your result.

# Untested
import csv
writer = csv.writer(open("some.csv", "wb"))

for row in result:
row = map(str,row)

# should be
myRow = list(row)
row = map(str,myRow)

 

[t_rectenwald]

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@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

Regards,
Tom

 

[Paul Hankin]

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@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()

 

[t_rectenwald]

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@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()

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

 

[t_rectenwald]

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@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()

- 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