471,354 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

Writing Oracle Output to a File

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
6 11352
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
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
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
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
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
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.

Similar topics

3 posts views Thread by Chad The Man | last post: by
4 posts views Thread by susmita_ganguly | last post: by
6 posts views Thread by hegyvari | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.