run sql script using SQLCL in python

Question

I've discovered Oracle's SQLCL and have been able to make it work in the terminal. I've also been able to make it work in Python, up through entering the actual SQL query.

My code in Python looks like this:

import subprocess
import time
import os

os.chdir("C:/sqlcl/bin")

subprocess.run(["sql", "username/password@//database-oracle.datamore.com/moreprod.more:1521"])

At this point, I get the "SQL>" prompt showing that Oracle is ready to take my query. What I'd like to do is enter the location to a script and have it be executed, something like:

@C:/Users/username/queries/test-query.sql;

Basically, I need a way to pass a SQL statement or a script location via Python to the SQL prompt.

score 0 · Answer 1 · answered Nov 18 '20 at 01:32

Here is how to pass items to a subprocess

args = ["grep", "beans"]

child_proccess = subprocess.Popen(args, stdin=subprocess.PIPE, stdout=subprocess.PIPE)

child_process_output = child_proccess.communicate(b"I love beans \n I like cars")[0]

print(child_process_output)

score 0 · Answer 2 · answered Nov 19 '20 at 18:01

Here's what worked:

import subprocess
import os

os.chdir("C:/sqlcl/bin")

subprocess.run(["sql",
                "username/password@//database-oracle.datamore.com/moreprod.more:1521",
                "@",
                "C:/sqlcl/bin/test-query.sql",
                ";"])

Note that the query is in the same directory as the SQLCLapplication

score 0 · Answer 3 · answered Mar 27 '21 at 09:42

import pexpect
from os import environ
import sys
environ['TNS_ADMIN'] = wallet
sqlcl_bin = '/sqlcl_source/sqlcl/bin/sql /nolog' # path to sqlcl bin 
child = pexpect.spawn (sqlcl_bin)
child.logfile = sys.stdout.buffer
child.expect('SQL>', timeout=30)
user=
password=
service= 
conn_string = f"conn {user}/{password}@{service};"
child.sendline(conn_str)

This is how I connect

run sql script using SQLCL in python

3 Answers3