By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,083 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,083 IT Pros & Developers. It's quick & easy.

Writing Oracle Output to a File

P: n/a
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
Dec 26 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Dec 26, 10:36*am, t_rectenwald <t.rectenw...@gmail.comwrote:
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
Dec 26 '07 #2

P: n/a
On Dec 26, 4:51*pm, t_rectenwald <t.rectenw...@gmail.comwrote:
On Dec 26, 10:36*am, t_rectenwald <t.rectenw...@gmail.comwrote:
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()

--
Paul Hankin
Dec 26 '07 #3

P: n/a
On Dec 26, 12:06*pm, Paul Hankin <paul.han...@gmail.comwrote:
On Dec 26, 4:51*pm, t_rectenwald <t.rectenw...@gmail.comwrote:


On Dec 26, 10:36*am, t_rectenwald <t.rectenw...@gmail.comwrote:
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()

--
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
Dec 26 '07 #4

P: n/a
On Dec 26, 12:10*pm, t_rectenwald <t.rectenw...@gmail.comwrote:
On Dec 26, 12:06*pm, Paul Hankin <paul.han...@gmail.comwrote:


On Dec 26, 4:51*pm, t_rectenwald <t.rectenw...@gmail.comwrote:
On Dec 26, 10:36*am, t_rectenwald <t.rectenw...@gmail.comwrote:
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()
--
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
Dec 26 '07 #5

P: n/a
t_rectenwald schrieb:
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


Dec 27 '07 #6

P: n/a
Ralf Schönian schrieb:
t_rectenwald schrieb:
>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)
writer.writerows(row)
writer.close()
Ralf Schoenian
Dec 27 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.