How to sequentially run daily SQL scripts (Oracle) automatically using python?
And also there are dependencies in the scripts as an example To run script B Script A wants to finish the execution.
Is that possible to do with python? - If possible, How can I do it automatically?
Thank you.
Just use Oracle and set up a chain of scheduled jobs where Job B is dependent on Job A having finished.
From the same documentation, an example is:
BEGIN DBMS_SCHEDULER.CREATE_CHAIN ( chain_name => 'my_chain1', rule_set_name => NULL, evaluation_interval => NULL, comments => NULL); END; / --- define three steps for this chain. Referenced programs must be enabled. BEGIN DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'stepA', 'my_program1'); DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'stepB', 'my_program2'); DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'stepC', 'my_program3'); END; / --- define corresponding rules for the chain. BEGIN DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain1', 'TRUE', 'START stepA'); DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( 'my_chain1', 'stepA COMPLETED', 'Start stepB, stepC'); DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( 'my_chain1', 'stepB COMPLETED AND stepC COMPLETED', 'END'); END; / --- enable the chain BEGIN DBMS_SCHEDULER.ENABLE('my_chain1'); END; / --- create a chain job to start the chain daily at 1:00 p.m. BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'chain_job_1', job_type => 'CHAIN', job_action => 'my_chain1', repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0', enabled => TRUE); END; /
The utility run_all_sql_dir.py executes all sql files from the specified directory and creates one log files. You can add this utility to your operating system's scheduler. In the script you can change the NLS settings, AUTOCOMMIT and silent mode sqlplus.
NLS_DATE_FORMAT = "\'DD.MM.YYYY HH24:MI:SS\'"
NLS_NUMERIC_CHARACTERS = "\'.,\'"
AUTO_COMMIT = "OFF"
NLS_LANG = 'AMERICAN_AMERICA.CL8MSWIN1251'
# silent sqlplus = "-s" silent off sqlplus = ""
silent_sqlplus = "-s"
-u Specify the username for example SCOTT
-p Specify the password for example TIGER
-c Specify the connect_string(TNS alias) or easy connect string for connect to oracle database
-d Specify the directory for executing sql scripts.
-l Specify the logfile for output log.
For example
run_all_sql_dir.py -u scott -p tiger -c 192.168.0.166:1521/test -d C:\Users\Dmitry\PycharmProjects\count_char\sql -l log_sql.log
import os
import subprocess
import pathlib
from optparse import OptionParser
import logging
NLS_DATE_FORMAT = "\'DD.MM.YYYY HH24:MI:SS\'"
NLS_NUMERIC_CHARACTERS = "\'.,\'"
AUTO_COMMIT = "OFF"
NLS_LANG = 'AMERICAN_AMERICA.CL8MSWIN1251'
Sqlheader = """
set autocommit """ + AUTO_COMMIT + "\n" + """set define off
set timing on
set linesize 10000
set pagesize 10000
set pause off
set echo on
set heading on
set termout ON
SET FEEDBACK ON
SET TAB ON
SET UNDERLINE ON
set trimspool on
""" + "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = " + NLS_NUMERIC_CHARACTERS + ";" + """
ALTER SESSION SET NLS_DATE_FORMAT = """ + NLS_DATE_FORMAT + ";\n"
parser = OptionParser(usage="usage: %%prog\n%s" % __doc__)
parser.add_option("-u", "--username", dest="username",
default=None,
help="User name of DB Oracle ")
parser.add_option("-p", "--password", dest="password",
default=None,
help="User Password of DB Oracle ")
parser.add_option("-c", "--connectstring", dest="connectstring",
default=None,
help="Connect string for DB Oracle ")
parser.add_option("-d", "--dir", dest="dirname",
default=None,
help="Name of dir sql ")
parser.add_option("-l", "--log", dest="logfilename",
default=None,
help="Name of log file ")
(options, args) = parser.parse_args()
input_dir_sql = options.dirname
username = options.username
password = options.password
connectstring = options.connectstring
logfilename = options.logfilename
logging.basicConfig(format='%(asctime)s %(levelname)s %(message)s', filename=logfilename, level=logging.INFO)
logging.info('Start script ')
sqlscript = ""
connectoracle = ''
currentDirectory = pathlib.Path(input_dir_sql)
for currentFile in currentDirectory.iterdir():
sqlscript = sqlscript + "PROMPT Start script: " + currentFile.__str__() + "\n"
sqlscript = sqlscript + "@" + currentFile.__str__() + "\n"
sqlscript = sqlscript + "PROMPT Stop script: " + currentFile.__str__() + "\n"
sqlscript = sqlscript + "PROMPT -------------------------------------------------------------------------------------- \n\n"
logging.info('Found script:' + currentFile.__str__())
SqlQueryALL = Sqlheader + sqlscript
logging.debug("Sqlfile:\n" + SqlQueryALL)
my_env = os.environ.copy()
my_env["NLS_LANG"] = NLS_LANG
logging.info("Set AUTO_COMMIT="+AUTO_COMMIT)
logging.info("Set NLS_LANG=" + NLS_LANG)
logging.info("Set NLS_DATE_FORMAT=" + NLS_DATE_FORMAT )
logging.info("Set NLS_NUMERIC_CHARACTERS=" + NLS_NUMERIC_CHARACTERS)
connectoracle = str(username) + "/" + str(password) + '@' + str(connectstring)
logging.info("Connect ro oracle:" + connectoracle)
sqlplus_result = subprocess.run([r'sqlplus.exe', silent_sqlplus, connectoracle], env=my_env,
stderr=subprocess.PIPE, stdout=subprocess.PIPE, text=True, input=SqlQueryALL)
#print(sqlplus_result.stdout)
logging.info(sqlplus_result.stdout)
logging.info('Stop script')
Output for example
2020-11-16 21:24:41,831 INFO Start script
2020-11-16 21:24:41,831 INFO Found script:C:\Users\Dmitry\PycharmProjects\count_char\sql\RunFirstTest.sql
2020-11-16 21:24:41,831 INFO Found script:C:\Users\Dmitry\PycharmProjects\count_char\sql\RunFirstTest6.sql
2020-11-16 21:24:41,831 INFO Found script:C:\Users\Dmitry\PycharmProjects\count_char\sql\RunFirstTest7.sql
2020-11-16 21:24:41,831 INFO Found script:C:\Users\Dmitry\PycharmProjects\count_char\sql\RunFirstTest8.sql
2020-11-16 21:24:41,832 INFO Found script:C:\Users\Dmitry\PycharmProjects\count_char\sql\RunFirstTest9.sql
2020-11-16 21:24:41,832 INFO Set AUTO_COMMIT=OFF
2020-11-16 21:24:41,832 INFO Set NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251
2020-11-16 21:24:41,832 INFO Set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
2020-11-16 21:24:41,835 INFO Set NLS_NUMERIC_CHARACTERS='.,'
2020-11-16 21:24:41,835 INFO Connect to oracle:scott/tiger@192.168.0.166:1521/test
2020-11-16 21:24:42,777 INFO
Session altered.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
Start script: C:\Users\Dmitry\PycharmProjects\count_char\sql\RunFirstTest.sql
View created.
Elapsed: 00:00:00.00
Table created.
Elapsed: 00:00:00.04