CODEWITHSUNDEEP
python-3.xoracle-databasesshsqlplus
Prokhozhii
Prokhozhii

Reputation: 702

Escaping symbols in Python when running SQL statement with Oracle sqlplus via SSH

I would like to run SQL statements using Oracle SQLplus via SSH using Python. So far, I have the following code:

import subprocess

sql = """
SET TIME OFF
SET TIMING OFF
SET HEADING OFF
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIMSPOOL ON
SET FEEDBACK OFF
select name || '__a' from v$database;
"""

sql = sql.replace("'","\'").replace("$","\$")
export_cmd = "export ORACLE_HOME=/u01/oracle/19/srv; export ORACLE_SID=db01; export LD_LIBRARY_PATH=${ORACLE_HOME}/lib"
ssh_command = f"""{export_cmd}; echo "{sql}" | $ORACLE_HOME/bin/sqlplus -S / as sysdba"""

host = "host1"
process = subprocess.Popen(["ssh", f"oracle@{host}", ssh_command], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
stdout, stderr = process.communicate()

print("Output:\n", stdout.decode())
print("Error:\n", stderr.decode())

It works correctly. But I would like not to touch the SQL statement at all as it was done in that line:

sql = sql.replace("'","\'").replace("$","\$")

Is there a way to do that, except for creating a separate SQL script and then running it with SQLplus?

Upvotes: 0

Views: 32

Answers (1)

Prokhozhii
Prokhozhii

Reputation: 702

There is a solution (using heredoc):

import subprocess

sql = """SET TIME OFF
SET TIMING OFF
SET HEADING OFF
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIMSPOOL ON
SET FEEDBACK OFF
select name || '__a' from v$database;
"""

export_cmd = "export ORACLE_HOME=/u01/oracle/19/srv; export ORACLE_SID=db01; export LD_LIBRARY_PATH=${ORACLE_HOME}/lib"
sqlplus = "${ORACLE_HOME}/bin/sqlplus"

ssh_command = f"""{export_cmd}; {sqlplus} -S / as sysdba <<'EOF'
{sql}
EOF
"""

host = "host1"
process = subprocess.Popen(["ssh", f"oracle@{host}", ssh_command], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
stdout, stderr = process.communicate()

print("Output:\n", stdout.decode())
print("Error:\n", stderr.decode())

Upvotes: 0

Related Questions