I have a .sql file with the following code:
delete from stalist
where stalistid=4944
/
insert into stalist
(stalistid, staid)
(select distinct 4944, staid
from staref
Where staid in(
3797,3798,
3870,4459,
3871,3872,
3876,3877,
0
))
/
commit
/
I would like to use Python to execute this file from SQLPlus. Is this possible? I do not have any python experience in this type of work and could use some help. Thank you!
see this tutorial: http://moizmuhammad.wordpress.com/2012/01/31/run-oracle-commands-from-python-via-sql-plus/
i.e.
from subprocess import Popen, PIPE
#function that takes the sqlCommand and connectString and retuns the output and #error string (if any)
def runSqlQuery(sqlCommand, connectString):
session = Popen(['sqlplus', '-S', connectString], stdin=PIPE, stdout=PIPE, stderr=PIPE)
session.stdin.write(sqlCommand)
return session.communicate()
should do it (where sqlCmmand is "@scriptname.sql").
Below is an example of how to do this. You need to read the file and pass the entire string as a command.
(username, password, host) = ("user","password","host")
conn_string = " %s/%s@%s "% (username,password,host)
session = Popen(['sqlplus','-S', conn_string], stdin=PIPE, stdout=PIPE, stderr=PIPE)
logging.info("Starting sqlplus")
sql_file = '%s/%s' % (sql_folder, file)
logging.info("Running " + sql_file)
f= open(sql_file,'r')
cmd = f.read()
session.stdin.write(cmd)
stdout, stderr = session.communicate()
The previous answers are outdated by almost a decade. Python 3.6+ has slightly easier methods of doing this (in my opinion, subprocess.run() is a bit cleaner because you don't have to resort to Popen hacking).
Here's a Python 3.9 function that will let you (1) log into SQL*Plus, (2) execute a SQL script, and (3) parameterize arguments to it:
def execute_sql_script_via_sqlplus():
sqlplus_login_command = 'sudo sqlplus oracle_user/oracle_password'
sql_script_path = '/example/path/to/some_script.sql'
sql_script_arguments = ["foo", "bar"]
sql_command = "@" + str(sql_script_path)
for argument in sql_script_arguments:
sql_command += f" {argument}"
result = subprocess.run(
sqlplus_login_command,
input=sql_command,
shell=True,
text=True,
capture_output=True
)
return result.stdout, result.stderr
I'll break this down a little:
subprocess.run(sqlplus_login_command) represents whatever command you'd use to log into SQL*PLUS. It doesn't necessarily have to use -S connection_string - especially if you have a valid local TNS configuration. This happens before we try to execute any SQL scripts.sql_command is an absolute system path to the script, which is prepended with @, and suffixed with a string-delimited list of arguments. These arguments are referenced within the script via "&1", "&2", etc. Arguments are not zero-indexed.input=sql_command fires off the actual SQL script after the initial SQL*Plus login.capture_output allows you to capture the stdout/stderr.text=True is necessary to make input=sqlcommand work.shell=True.