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:empables.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:empables.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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…