Xiaoqing Xiaoqing - 1 month ago 20
Python Question

how to use python cx_Oracle with spool

I'm using python3.4 to interact with oracle(11g)/sql developer.
Is it true that cx_Oracle could not deal with sqlPlus statements? It seems that the page https://sourceforge.net/p/cx-oracle/mailman/message/2932119/ said so.

So how could we execute 'spool' command by python?

The code:

import cx_Oracle
db_conn = cx_Oracle.connect(...)
cursor = db_conn.cursor()
cursor.execute('spool C:\\Users\Administrator\Desktop\mycsv.csv')
...


the error: cx_Oracle.DatabaseError: ORA-00900:

Answer

The "spool" command is very specific to SQL*Plus and is not available in cx_Oracle or any other application that uses the OCI (Oracle Call Interface). You can do something similar, however, without too much trouble.

You can create your own Connection class subclassed from cx_Oracle.Connection and your own Cursor class subclassed from cx_Oracle.Cursor that would perform any logging and have a special command "spool" that would turn it on and off at will. Something like this:

class Connection(cx_Oracle.Connection):

    def __init__(self, *args, **kwargs):
        self.spoolFile = None
        return super(Connection, self).__init__(*args, **kwargs)

    def cursor(self):
        return Cursor(self)

    def spool(self, fileName):
        self.spoolFile = open(fileName, "w")


class Cursor(cx_Oracle.Cursor):

    def execute(self, statement, args):
        result = super(Cursor, self).execute(statement, args)
        if self.connection.spoolFile is not None:
            self.connection.spoolFile.write("Headers for query\n")
            self.connection.spoolFile.write("use cursor.description")

    def fetchall(self):
        rows = super(Cursor, self).fetchall()
        if self.connection.spoolFile is not None:
            for row in rows:
                self.connection.spoolFile.write("row details")

That should give you some idea on where to go with this.

Comments