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/[EMAIL PROTECTED]')
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Writing Oracle Output to a File
On Dec 26, 10:36 am, t_rectenwald <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]')
> 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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Writing Oracle Output to a File
On Dec 26, 12:06 pm, Paul Hankin <[EMAIL PROTECTED]> wrote:
> On Dec 26, 4:51 pm, t_rectenwald <[EMAIL PROTECTED]> wrote:
>
>
>
>
>
> > On Dec 26, 10:36 am, t_rectenwald <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]')
> > > 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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Writing Oracle Output to a File
On Dec 26, 12:10 pm, t_rectenwald <[EMAIL PROTECTED]> wrote:
> On Dec 26, 12:06 pm, Paul Hankin <[EMAIL PROTECTED]> wrote:
>
>
>
>
>
> > On Dec 26, 4:51 pm, t_rectenwald <[EMAIL PROTECTED]> wrote:
>
> > > On Dec 26, 10:36 am, t_rectenwald <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]')
> > > > 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
--
http://mail.python.org/mailman/listinfo/python-list
Strange Behavior: csv module & IDLE
I've noticed an oddity when running a program, using the csv module,
within IDLE. I'm new to Python so am confused by what is happening.
Here is what I'm doing:
1) Open the IDLE Shell.
2) Select File | Open...
3) Choose my file, foo.py, opening it in a window.
4) From that window, I hit F5 to run the module.
Within the program, the snippet where I use the csv module is below:
==
csvfile = open('foo.csv', 'w')
writer = csv.writer(csvfile)
for row in rows:
writer.writerow(row[0:3])
csvfile.close
==
The rows object is returned from a database query and is a list of
tuples. Now here is the strange thing. If I run this program
directly from the command line, i.e.,
D:\test> D:\python25\python foo.py
It runs fine, foo.csv is created and all is well. However, when I run
it through the IDLE shell as described above, the foo.csv file is
created but remains empty at 0 bytes. When I try to delete the file,
Windows says it is in use. The only way I can break out of this is by
restarting the IDLE shell. In other words, it appears that the shell
is hanging.
This will run through Task Scheduler, so shouldn't be a problem, but
I'm worried that I'm coding this wrong for it to be acting this way
under IDLE. Any help or explanation would be appreciated.
Best Regards,
Tom
--
http://mail.python.org/mailman/listinfo/python-list
Re: Strange Behavior: csv module & IDLE
On Dec 28, 9:43 pm, John Machin <[EMAIL PROTECTED]> wrote:
> On Dec 29, 1:12 pm, t_rectenwald <[EMAIL PROTECTED]> wrote:
>
> > I've noticed an oddity when running a program, using the csv module,
> > within IDLE. I'm new to Python so am confused by what is happening.
> > Here is what I'm doing:
>
> > 1) Open the IDLE Shell.
> > 2) Select File | Open...
> > 3) Choose my file, foo.py, opening it in a window.
> > 4) From that window, I hit F5 to run the module.
>
> > Within the program, the snippet where I use the csv module is below:
>
> Forget snippet, show us a *whole* "program". Cut out the database
> stuff; just use some simple made-up value for "rows".
>
> > ==
> > csvfile = open('foo.csv', 'w')
>
> Always use 'wb' -- not the cause of the current problem but it will
> bite you later.
>
> > writer = csv.writer(csvfile)
>
> > for row in rows:
> > writer.writerow(row[0:3])
>
> Adding
>
> del writer
>
> may help
>
> > csvfile.close
>
> The above statement does nothing. You meant csvfile.close(), I
> presume.
>
> > ==
>
> > The rows object is returned from a database query and is a list of
> > tuples. Now here is the strange thing. If I run this program
> > directly from the command line, i.e.,
>
> > D:\test> D:\python25\python foo.py
>
> > It runs fine, foo.csv is created and all is well. However, when I run
> > it through the IDLE shell as described above, the foo.csv file is
> > created but remains empty at 0 bytes. When I try to delete the file,
> > Windows says it is in use. The only way I can break out of this is by
> > restarting the IDLE shell. In other words, it appears that the shell
> > is hanging.
>
> No it's not hanging, it's just that the file is still open; you
> haven't closed it. It won't be closed until you exit IDLE.
>
>
>
> > This will run through Task Scheduler, so shouldn't be a problem, but
> > I'm worried that I'm coding this wrong for it to be acting this way
> > under IDLE. Any help or explanation would be appreciated.
>
> Do these things inside a function, so that the objects get garbage-
> collected on exit.
Thanks for all of the help. I'm still learning Python so dorked up
here and didn't add the empty parenthesis around csvfile.close as I
should have. So, it never called the close() function, but just
referenced it as was noted in your responses. After doing that,
everything works fine and the file is closed properly. I do have this
in a function in the actual script I'm writing, just sort of made a
dummy, foo.py, to do some testing and didn't have it in a function
there.
I'll research "wb" now to figure out what that does. Thanks again for
the help!
Tom
--
http://mail.python.org/mailman/listinfo/python-list
Cursors in a Loop
I have a python script that uses the cx_Oracle module. I have a list
of values that I iterate through via a for loop and then insert into
the database. This works okay, but I'm not sure whether I can use one
cursor for all inserts, and define it outside of the loop, or
instantiate and close the cursor within the loop itself. For example,
I have:
for i in hostlist:
cursor = connection.cursor()
sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
cursor.execute(sql)
cursor.close()
And I've also tried:
cursor = connection.cursor()
for i in hostlist:
sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
cursor.execute(sql)
cursor.close()
Both work fine, and execute in the same amount of time. I'm just
trying to understand what is the "correct" approach to use.
Thanks,
Tom
--
http://mail.python.org/mailman/listinfo/python-list
Re: Cursors in a Loop
On Jan 3, 7:47 pm, t_rectenwald <[EMAIL PROTECTED]> wrote:
> I have a python script that uses the cx_Oracle module. I have a list
> of values that I iterate through via a for loop and then insert into
> the database. This works okay, but I'm not sure whether I can use one
> cursor for all inserts, and define it outside of the loop, or
> instantiate and close the cursor within the loop itself. For example,
> I have:
>
> for i in hostlist:
> cursor = connection.cursor()
> sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
> cursor.execute(sql)
> cursor.close()
>
> And I've also tried:
>
> cursor = connection.cursor()
> for i in hostlist:
> sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
> cursor.execute(sql)
> cursor.close()
>
> Both work fine, and execute in the same amount of time. I'm just
> trying to understand what is the "correct" approach to use.
>
> Thanks,
> Tom
I think I have this one figured out. The answer would be the second
option, i.e. keep the cursor instantion and close outside of the
loop. I wasn't aware that one cursor could be used for multiple
executes.
Regards,
Tom
--
http://mail.python.org/mailman/listinfo/python-list
