472,971 Members | 1,897 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,971 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 11639
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Chad The Man | last post by:
I have been developing some applications on suse 9 / apache 1.37 / php 4.3.4 / Oracle 9i / OCI8. We recently moved servers, apache works fine, php works fine, oracle works fine... however, trying...
4
by: susmita_ganguly | last post by:
Hi I am trying to upgrade from oracle 8i to oracle 9i on the same server ..I don't know much abt migration . Can anyone help me out. Thanks. Susmita
0
by: Si | last post by:
Hi, I am having trouble with an install of Oracle 9i Release 2 on Linux Mandrake 9.1. I have 512 MB DDR RAM and a Duron 850 CPU. The install appeared to run fairly seamlessly except for a...
14
by: Ruth | last post by:
Hi All I am not a DBA, but a unix administrator. After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our test server, we have noticed a big slow down in our application...
0
by: sedefo | last post by:
I ran into this Microsoft Patterns & Practices Enterprise Library while i was researching how i can write a database independent data access layer. In my company we already use Data Access...
5
by: Michael Rudolph | last post by:
Hi newsgroup, I have an issue with the configuration of a DB2 federated database (WebSphere Information Integrator) in conjunction with the relational wrapper for Oracle on AIX. DB2 seems to not...
6
by: hegyvari | last post by:
Hi, I have a few apps written in PHP, running on Fedora Core. After porting these applications from PostgreSQL to Oracle a bug appeared: sometimes, not in a reproducable manner, the web page...
2
by: Vinod Sadanandan | last post by:
All, Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features . Precompilers:...
2
by: murthydb2 | last post by:
Hi My requirement is that i have to write a stored procedure in db2 and that will be executed in a batch file . Any system error or validation error that occurs inside the db2 sp during...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.