Python embebbed with Oracle SQL*Plus

[Zen]

olavb at yahoo.com (Olav) writes:

>This is a very simple Cygwin/NT example:
>***************
>PLUS33 scott/tiger at prod_sa <<EOF 
>select COU_NAME from M1_countries
>/
>quit
>eof
>********************
>So it is not simply building an SQL-string and calling
>some function.

This method can be done using the popen2 module to open pipes to
a SQL*Plus process (or os.popen if you don't need to parse the output).

>It is really like SQL embedded in the script.
>There are also mechanism for passing variables back and forth.
>(I can easily write a script that takes a sql-statement as an arguments
>from the command line, something like osql select "*" from M1_countries)

If you havn't looked at the DB API documentation I suggeset you do so
(http://www.python.org/topics/database/DatabaseAPI-2.0.html). It is much
nicer to work in this interface rather than deal with piping things
too and from sqlplus.

>I guess the mechanism is to build a temporary file, and call sqlplus
>with this.

>I think it might be more difficult with Python because it is actually 
>precompiled and because there is no equivalent of pipes.

import popen2

(to_sqlplus,from_sqlplus) = popen2.popen2('PLUS33.EXE scott/tiger at prod_sa')
print >> to_sqlplus,'''
	set pagesize 0
	select COU_NAME from M1_countries where COU_NAME = '%s';
	exit;
    ''' % ('Austria')
for l in from_sqlplus.readlines():
    print l,


The equivalent using the DB API

import DCOracle

try:
    connection = DCOracle.Connect('scott/tiger at prod_sa')
    cursor = connection.cursor()
    cursor.execute('select COU_NAME from M1_countries where COU_NAME = :p1',
	['Austria'])
    for row in cursor.fetchall():
	print row[0]
except DCOracle.error:
    print 'Argh!'