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.
3 Answers
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)
4 Comments
9000
Splitting on
; will fail if there are string literals that contain semicolons; this might take a little bit of simple scanning.
chipchilders
Agreed - that was my point when I said "I'm assuming that the ";" character is only used to delimit the oracle SQL statements within the file." You stated the obvious issue more clearly though. Thanks!
Lucas
Also assumes that the only delimiter is
;. Which is usually the case, but if the sql script contains things like stored procedures, then the delimiter gets changed before defining the procedure...
Ulrik Larsen
we use ~ character when script contains procedures/triggers etc.
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.
2 Comments
Skylar Saveland
the accepted answer does not work in the case of PL/SQL .. this sqlplus bit does ok in that case.
Zenith_1024
if anyone gets file not found errors, ensure you use the full path of the .sql file and also add sqlplus to PATH variables.
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.
1 Comment
arne
Wow, thank you for unearthing this old question and providing this link. Very interesting! Although the method used in the
run_sql_script is rather crude.