CODEWITHSUNDEEP
pythondatabasecx-oracle
user747858
user747858

Reputation:

execute a sql script file from cx_oracle?

Is there a way to execute a sql script file using cx_oracle in python.

I need to execute my create table scripts in sql files.

Upvotes: 14

Views: 30088

Answers (3)

Rubén Pozo
Rubén Pozo

Reputation: 1083

Into cx_Oracle library you can find a method used by tests to load scripts: run_sql_script

I modified this method in my project like this:

   def run_sql_script(self, connection, script_path):
    cursor = connection.cursor()
    statement_parts = []
    for line in open(script_path):
        if line.strip() == "/":
            statement = "".join(statement_parts).strip()
            if not statement.upper().startswith('CREATE PACKAGE'):
                statement = statement[:-1]
            if statement:
                try:
                    cursor.execute(statement)
                except Exception as e:
                    print("Failed to execute SQL:", statement)
                    print("Error:", str(e))
            statement_parts = []
        else:
            statement_parts.append(line)

The commands into script file must be separated by "/". I hope it can be of help.

Upvotes: 2

Neil Vass
Neil Vass

Reputation: 5341

Another option is to use SQL*Plus (Oracle's command line tool) to run the script. You can call this from Python using the subprocess module - there's a good walkthrough here: http://moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/.

For a script like tables.sql (note the deliberate error):

CREATE TABLE foo ( x INT );

CREATE TABLER bar ( y INT );

You can use a function like the following:

from subprocess import Popen, PIPE

def run_sql_script(connstr, filename):
    sqlplus = Popen(['sqlplus','-S', connstr], stdin=PIPE, stdout=PIPE, stderr=PIPE)
    sqlplus.stdin.write('@'+filename)
    return sqlplus.communicate()

connstr is the same connection string used for cx_Oracle. filename is the full path to the script (e.g. 'C:\temp\tables.sql'). The function opens a SQLPlus session (with '-S' to silence its welcome message), then queues "@filename" to send to it - this will tell SQLPlus to run the script.

sqlplus.communicate sends the command to stdin, waits for the SQL*Plus session to terminate, then returns (stdout, stderr) as a tuple. Calling this function with tables.sql above will give the following output:

>>> output, error = run_sql_script(connstr, r'C:\temp\tables.sql')
>>> print output

Table created.

CREATE TABLER bar (
       *
ERROR at line 1:
ORA-00901: invalid CREATE command

>>> print error

This will take a little parsing, depending on what you want to return to the rest of your program - you could show the whole output to the user if it's interactive, or scan for the word "ERROR" if you just want to check whether it ran OK.

Upvotes: 13

chipchilders
chipchilders

Reputation: 182

PEP-249, which cx_oracle tries to be compliant with, doesn't really have a method like that.

However, the process should be pretty straight forward. Pull the contents of the file into a string, split it on the ";" character, and then call .execute on each member of the resulting array. I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file.

f = open('tabledefinition.sql')
full_sql = f.read()
sql_commands = full_sql.split(';')

for sql_command in sql_commands:
    curs.execute(sql_command)

Upvotes: 13

Related Questions