{ Abhilash Kumar Bhattaram : Follow on LinkedIn }
series : Python for Oracle on ARM Linux | Part 2 - "shell like" SQL scripts
NOTE : The blog examples is on ARM Linux but almost the same works for x86 if as long as you have the required miniconda environment.
Common Use case for DBA's
In traditional DBA workflows, shell scripts are commonly used to automate database tasks such as executing SQL files, capturing logs, and scheduling jobs. However, this blog demonstrates how Python can serve as a modern and powerful alternative to shell scripting for Oracle DBAs. By leveraging Python’s subprocess module, we show how to run SQL*Plus commands, manage SQL script execution, redirect output to spool files, and even handle background processes—all within a clean and maintainable Python environment. This approach not only improves readability and cross-platform compatibility but also enables integration with advanced Python capabilities such as data parsing, reporting, and automation frameworks, making it a compelling replacement for traditional shell scripts in DBA automation.
Lets get started
One of the first things to start working on python for oracle is to have your local environment set up. If you do not have that already check my earlier blog.
Python for Oracle - oracledb driver for ARM Linux | Getting Started - Part 1 : Essentials
Modules needed
Let me start with the basre minimal here , I need only "1" python module at this point , as a DBA I have the following requirements which are non negotiables.
- I work extensively on formatting SQL within my SQL files.
- I need a reliable, efficient program to execute my sqlplus commands.
- Ideally, I’d like Python to handle the spooling of output—so I can view the logs in real time as the SQL runs.
My first go-to python module is subprocess which I will be able to run the program ( in my case sqlplus )
import subprocess
Variables Needed
I need just the four variables
- Connection String : connection_str
- SQL File : sql_file
- Output File : output_file
- Command Argument : cmd
# SQL*Plus connection string
connection_str = "we/we@machine1.nabhaas.com:1521/FREEPDB1"
sql_file = "/opt/oracle/py_scripts/sample.sql"
output_file = "/opt/oracle/py_scripts/sqlplus_output.txt"
# Command to run
cmd = f"sqlplus -S {connection_str} @{sql_file}"
The subprocess to run the command argument
Note that I mention shell=True which means I am using the SQL*Plus UNIX command to run my SQL File
subprocess.run(cmd, shell=True, stdout=f_out, stderr=subprocess.STDOUT)
The SQL File I use - with JSON processing
The SQL below retrieves some sample weather data.
-- sample.sql
set lines 200 pages 200
col city for a30
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set lines 2000
set pages 2000
SET SQLBL OFF
col directory_name format a40
col value format a80
select sysdate from dual;
SELECT
JSON_VALUE(t.weather_data, '$.location.city') AS city,
t.created_at,
ROUND(AVG(f.temp_max), 1) AS avg_temp_max,
ROUND(AVG(f.temp_min), 1) AS avg_temp_min,
ROUND(AVG(f.humidity), 1) AS avg_humidity,
ROUND(AVG(f.wind_speed), 1) AS avg_wind_speed,
ROUND(AVG(f.cloud_pct), 1) AS avg_cloud_pct,
ROUND(AVG(f.pressure), 1) AS avg_pressure
FROM (
SELECT *
FROM we.weather_data_json_part
FETCH FIRST 100 ROWS ONLY
) t,
JSON_TABLE(
t.weather_data,
'$.forecast[*]'
COLUMNS (
forecast_date DATE PATH '$.date',
temp_max NUMBER PATH '$.temp_max',
temp_min NUMBER PATH '$.temp_min',
humidity NUMBER PATH '$.humidity',
wind_speed NUMBER PATH '$.wind_speed',
cloud_pct NUMBER PATH '$.cloud_pct',
pressure NUMBER PATH '$.pressure'
)
) f
GROUP BY JSON_VALUE(t.weather_data, '$.location.city'), t.created_at
ORDER BY city, t.created_at;
exit;
Putting it all together - py_ora.py
(base) [oracle@machine1 py_scripts]$ cat py_ora.py
import subprocess
# SQL*Plus connection string
connection_str = "we/we@machine1.nabhaas.com:1521/FREEPDB1"
sql_file = "/opt/oracle/py_scripts/sample.sql"
output_file = "/opt/oracle/py_scripts/sqlplus_output.txt"
# Command to run
cmd = f"sqlplus -S {connection_str} @{sql_file}"
# Run the command (no output capture, just execution)
#subprocess.run(cmd, shell=True)
with open(output_file, "w") as f_out:
# Run subprocess and redirect stdout & stderr to the file
subprocess.run(cmd, shell=True, stdout=f_out, stderr=subprocess.STDOUT)
print(f"SQL*Plus output redirected to: {output_file}")
try:
with open(output_file, "r") as f:
output = f.read()
print("=== SQL*Plus Spool Output ===")
print(output)
except FileNotFoundError:
print(f"Spool file not found: {output_file}")
Running the SQL file from python
Before executing remember to ensure you are using the relevant miniconda environment.
export PATH="/opt/oracle/py_miniconda/bin:$PATH"
(base) [oracle@machine1 py_scripts]$ env | grep conda
CONDA_EXE=/opt/oracle/py_miniconda/bin/conda
CONDA_PREFIX=/opt/oracle/py_miniconda
CONDA_PYTHON_EXE=/opt/oracle/py_miniconda/bin/python
PATH=/opt/oracle/py_miniconda/bin:/opt/oracle/product/23ai/dbhomeFree/bin:/usr/sbin:/usr/local/bin:/opt/oracle/py_miniconda/bin:/opt/oracle/py_miniconda/condabin:/home/oracle/.local/bin:/home/oracle/bin:/usr/share/Modules/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
Running the py_ora.py beautifully runs the SQL file via SQL*Plus and provides the same output as we see in a shell program , but this does it in a more elegant way.
(base) [oracle@machine1 py_scripts]$ python py_ora.py
SQL*Plus output redirected to: /opt/oracle/py_scripts/sqlplus_output.txt
=== SQL*Plus Spool Output ===
Session altered.
SYSDATE
--------------------
18-MAY-2025 15:34:26
CITY CREATED_AT AVG_TEMP_MAX AVG_TEMP_MIN AVG_HUMIDITY AVG_WIND_SPEED AVG_CLOUD_PCT AVG_PRESSURE
------------------------------ -------------------- ------------ ------------ ------------ -------------- ------------- ------------
StressCity_67173 01-NOV-1999 00:00:00 39.9 20.1 47.5 29.3 51.8 1001.1
StressCity_67193 01-DEC-1999 00:00:00 40.2 20 50.8 31.1 53.7 996.5
StressCity_67226 01-JUN-1999 00:00:00 40 19.4 49.2 30.5 50.3 999.5
StressCity_67242 01-JAN-1999 00:00:00 39.9 20.6 49.1 29.2 46.9 1001.2
StressCity_67245 01-DEC-1999 00:00:00 40.1 20.1 48.9 29.2 50.4 1001.6
StressCity_67288 01-AUG-1999 00:00:00 39.5 20.4 51.2 31.5 47.2 1001.6
StressCity_67339 01-FEB-1999 00:00:00 39.8 20.6 48.6 29.6 49.8 1000
StressCity_67373 01-FEB-1999 00:00:00 40.3 19.8 50.3 28.9 49.4 1000.5
StressCity_67388 01-JUN-1999 00:00:00 39.4 20.7 51.9 30.1 49.1 999.1
StressCity_67406 01-APR-1999 00:00:00 39.8 20.6 48.9 31.3 51 998.9
StressCity_67415 01-NOV-1999 00:00:00 40.2 19.5 53.2 30.4 49.8 1002.1
StressCity_67437 01-APR-1999 00:00:00 40.1 20.1 50.4 29.9 50.6 1001.4
StressCity_67441 01-JAN-1999 00:00:00 40 19.7 49.4 30.5 49.8 1000.4
StressCity_67530 01-JUL-1999 00:00:00 39.9 19.6 49.9 29.4 49.7 999.6
StressCity_67556 01-SEP-1999 00:00:00 40.7 19.7 49.7 31.8 49.6 1000.3
StressCity_67557 01-DEC-1999 00:00:00 39.8 20 50.2 30 52.2 999
StressCity_67591 01-JAN-1999 00:00:00 40.3 20.4 49.1 31.1 50.9 999.5
StressCity_67663 01-JUL-1999 00:00:00 39.3 19.8 49.6 29 48.8 999.7
StressCity_67702 01-JAN-1999 00:00:00 40 20 48 29.3 54.4 999.4
StressCity_67709 01-FEB-1999 00:00:00 40.1 19.9 51.5 30.5 51.8 997.3
StressCity_67711 01-MAR-1999 00:00:00 39.8 20.4 48.4 30.1 54 999.8
StressCity_67724 01-APR-1999 00:00:00 40 19.8 49.4 29.3 51.9 997.4
StressCity_67741 01-MAR-1999 00:00:00 40 20.2 49.2 30.2 51.5 999.3
StressCity_67771 01-JUL-1999 00:00:00 40.3 20.1 50.4 30.1 50.9 1003.6
StressCity_67830 01-JUN-1999 00:00:00 39.9 20.1 50.8 29.6 49.9 1000.9
StressCity_67831 01-MAR-1999 00:00:00 40.5 20 49.7 30.7 49.2 999
StressCity_67834 01-MAR-1999 00:00:00 40.4 20.3 48.9 32.2 49.6 999.8
StressCity_67857 01-MAR-1999 00:00:00 39.5 19.5 51.6 30.1 50.2 998.6
StressCity_67864 01-APR-1999 00:00:00 40.3 20 49.6 30.9 48.2 998.8
StressCity_67934 01-FEB-1999 00:00:00 40.1 20 49 31.2 48.8 998.6
StressCity_67972 01-JUL-1999 00:00:00 39.9 20.2 49.3 30.2 50.9 999.2
StressCity_67983 01-MAY-1999 00:00:00 40.4 19.9 48.8 30.6 50.3 1000.8
StressCity_68094 01-DEC-1999 00:00:00 39.5 20.1 47.7 29.8 48.4 997.8
StressCity_68099 01-OCT-1999 00:00:00 40.2 20 52.3 29.4 47.8 1000.9
StressCity_68116 01-MAR-1999 00:00:00 39.6 20.2 49.2 30.1 49.8 1004.2
StressCity_68145 01-SEP-1999 00:00:00 40.3 19.7 50.4 29.7 49.9 1000.9
StressCity_68166 01-JUN-1999 00:00:00 39.8 20.7 49.2 29.7 49.6 998.5
StressCity_68168 01-APR-1999 00:00:00 39.9 20.2 50 31 46.7 999.3
StressCity_68198 01-FEB-1999 00:00:00 39.9 20.3 48.6 29.5 50.6 999.7
StressCity_68210 01-JAN-1999 00:00:00 40 19.6 49 29.7 48.6 1001
StressCity_68223 01-OCT-1999 00:00:00 39.9 19.5 49.9 29.7 50.9 1000.6
StressCity_68224 01-FEB-1999 00:00:00 40.2 19.7 50.2 28.9 50.8 1000.4
StressCity_68236 01-JUL-1999 00:00:00 40.6 19.7 51.8 29.5 48 999.9
StressCity_68267 01-DEC-1999 00:00:00 39.9 19.2 51.5 29.9 50.4 1000.9
StressCity_68379 01-JAN-1999 00:00:00 40.2 19.8 49.5 29.7 50.4 1000.9
StressCity_68386 01-AUG-1999 00:00:00 39.8 19.9 48.8 28.5 49.4 997.7
StressCity_68390 01-MAY-1999 00:00:00 39.8 20.3 50.2 30.4 52.4 998.3
StressCity_68493 01-JUN-1999 00:00:00 40 20.3 50.9 31.5 50.3 1001.2
StressCity_68503 01-AUG-1999 00:00:00 40.6 20 48.4 29.7 51.7 1000.1
StressCity_68531 01-MAR-1999 00:00:00 40.5 20 48.9 29.2 49.8 1000.2
StressCity_68557 01-SEP-1999 00:00:00 40.3 20.5 52.1 29.5 48.6 1002.3
StressCity_68665 01-JUN-1999 00:00:00 40.1 19.8 51.2 30.4 49.9 1001.6
StressCity_68705 01-DEC-1999 00:00:00 39.8 20.3 49.9 28 51.6 1002.3
StressCity_68706 01-DEC-1999 00:00:00 39.7 20 48.7 30.5 48.3 997.3
StressCity_68732 01-DEC-1999 00:00:00 39.8 19.8 49.9 30.3 48.7 1000.5
StressCity_68735 01-MAY-1999 00:00:00 39.7 19.5 50.7 29.9 49.4 999.6
StressCity_68738 01-AUG-1999 00:00:00 40.1 19.8 50.4 30.6 49.3 999.4
StressCity_68755 01-MAR-1999 00:00:00 40 20.2 50.1 30.8 48.6 998
StressCity_68783 01-JUL-1999 00:00:00 39.8 20.6 49.2 29.4 49 1001.6
StressCity_68819 01-APR-1999 00:00:00 39.8 19.9 49.9 29.1 50.9 1001.1
StressCity_68827 01-JUN-1999 00:00:00 40 19.5 49.7 29.4 50.1 998.4
StressCity_68859 01-DEC-1999 00:00:00 40 20.1 50.1 30.9 48.5 999.8
StressCity_68900 01-APR-1999 00:00:00 39.7 20.1 48.7 30.2 49.4 996.8
StressCity_68907 01-JUL-1999 00:00:00 39.3 19.8 51.9 29 46.8 997.3
StressCity_68923 01-JUL-1999 00:00:00 39.5 19.9 48.9 30.4 48.9 1001.8
StressCity_68946 01-DEC-1999 00:00:00 40.3 19.9 48.8 31.2 50.1 999.7
StressCity_69019 01-JUL-1999 00:00:00 39.9 19.9 51.9 30.8 48.4 1000.9
StressCity_69077 01-JAN-1999 00:00:00 40 20.2 51.1 28.5 53.1 1001
StressCity_69091 01-OCT-1999 00:00:00 39.8 20.2 49.4 31.1 48.1 1000.9
StressCity_69098 01-MAR-1999 00:00:00 39.8 19.8 48.9 29.4 54.4 997.2
StressCity_69102 01-DEC-1999 00:00:00 39.8 19.7 50.9 29.2 46.6 999.8
StressCity_69122 01-MAR-1999 00:00:00 39.9 20.1 49.7 31.9 48.9 1002.1
StressCity_69123 01-APR-1999 00:00:00 40.2 20 52.1 30.6 50.9 998.5
StressCity_69135 01-MAR-1999 00:00:00 40.2 19.7 50.5 30.8 53.7 997.9
StressCity_69156 01-JUN-1999 00:00:00 40.1 19.8 50.1 31.1 52.7 1000.4
StressCity_69168 01-AUG-1999 00:00:00 39.9 19.9 52.1 29.3 49.9 1001.4
StressCity_69183 01-AUG-1999 00:00:00 40.1 19.7 50.3 30.3 48.9 1002.6
StressCity_69186 01-MAR-1999 00:00:00 39.7 20.4 50.3 30 47.4 999.8
StressCity_69220 01-APR-1999 00:00:00 39.9 20.1 47.4 32.2 48.8 998.5
StressCity_69222 01-JUN-1999 00:00:00 40.1 19.9 49.1 30.3 51 1000.9
StressCity_69283 01-NOV-1999 00:00:00 40.2 19.5 49.3 29.4 47.1 1001.6
StressCity_69295 01-MAY-1999 00:00:00 40 19.9 50.3 28.6 50.5 1000.1
StressCity_69323 01-OCT-1999 00:00:00 40.1 20.1 50.1 28.9 51.1 1000.8
StressCity_69340 01-OCT-1999 00:00:00 40.4 20.6 51.5 30.5 49.2 1001
StressCity_69368 01-AUG-1999 00:00:00 40 20.2 51.2 31.8 49.4 998.8
StressCity_69372 01-FEB-1999 00:00:00 39.7 20.3 47.8 27.9 49.8 998.4
StressCity_69373 01-JUL-1999 00:00:00 40.6 19.8 48.7 28.8 50.7 1000.6
StressCity_69383 01-AUG-1999 00:00:00 40.2 20.4 50.7 30.6 49.9 1001.8
StressCity_69413 01-OCT-1999 00:00:00 40.1 20.6 52.2 31 48.4 999.7
StressCity_69415 01-FEB-1999 00:00:00 39.9 19.5 50.5 29.7 49.1 999.6
StressCity_69425 01-SEP-1999 00:00:00 40.1 19.2 51.8 29.4 47.7 997
StressCity_69457 01-JAN-1999 00:00:00 39.7 20.1 46.8 31.1 52.3 998.4
StressCity_69466 01-AUG-1999 00:00:00 39.8 19.9 49 30.9 50.5 997.7
StressCity_69491 01-MAY-1999 00:00:00 39.8 20.1 50.2 30.8 49.3 1001.9
StressCity_69495 01-JAN-1999 00:00:00 40 20 49.6 30.7 48.7 998.7
StressCity_69496 01-JUL-1999 00:00:00 39.8 19.8 51.9 29.6 49.5 998.4
StressCity_69515 01-NOV-1999 00:00:00 40.1 20.2 48.5 29.4 50.5 998.2
StressCity_69519 01-DEC-1999 00:00:00 40.3 19.7 50.3 29.7 47.3 998.8
StressCity_69563 01-JAN-1999 00:00:00 40.1 20.7 53.3 31 49.7 999.9
StressCity_69589 01-MAY-1999 00:00:00 39.6 19.9 48.4 28.9 48.6 998.6
100 rows selected.
More in my next post on Python for Oracle.
Top comments (0)