Re: Python embebbed with Oracle SQL*Plus

From: Zen <zen_at_shangri-la.dropbear.id.au>
Date: Sun, 22 Jul 2001 06:54:01 GMT
Message-ID: <3b3bc830_at_mercury.its.rmit.edu.au>

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!' Received on Sun Jul 22 2001 - 08:54:01 CEST