Run sql script file from cx_oracle?

Question

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

I need to execute create table scripts in sql files.

Answer 1

PEP-249 , with which cx_oracle is trying to be compatible, does not actually have such a method.

However, the process should be fairly simple. Pull the contents of the file into a string, divide it by ";" character, and then call .execute for each member of the resulting array. I suppose that ";" the character is used only to delimit Oracle oracle statements within a file.

f = open('tabledefinition.sql') full_sql = f.read() sql_commands = full_sql.split(';') for sql_command in sql_commands: curs.execute(sql_command) 

Answer 2

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

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

 CREATE TABLE foo ( x INT ); CREATE TABLER bar ( y INT ); 

You can use the following function:

 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 as for cx_Oracle . filename is the full path to the script (for example, 'C:\temp\tables.sql' ). The function opens a SQLPlus session (with "-S" to disable its greeting message), and then sends an "@filename" to send to it - this tells SQLPlus to run the script.

sqlplus.communicate sends the command to stdin, waits for the SQL * Plus session to complete, and then returns (stdout, stderr) as a tuple. Calling this function using tables.sql above will yield 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 

It takes a little parsing, depending on what you want to return to the rest of your program - you can show the entire output to the user if it is interactive, or scan the word "ERROR" if you just want to check if it works fine.