Wrapping dbshell and invoking procedures and such

[Dan Davis]

I've succeeded over the last few years in creating a Django app that everyone likes that uses a "tip of the iceberg" out of a 20 year old database. Now I've inherited the SQL development as well, and I want to cleanup and move towards more Django.

There are lots of small, non-DRY scripts run from cron which invoke a stored procedure after doing some boilerplate.  Because the procedures are developed from an Oracle Developer's perspective, they do a lot via DBMS_OUTPUT and stuff.  I want to replace most  of these scripts with a single DRY Django management command taking the following inputs:

• Stored procedure to run
• What log file to generate
• Who should get the email

This makes things more DRY while still letting SQL development be SQL development. My idea is that the parent management command forks a child process that runs dbshell, and then writes into the dbshell input the stuff to run.   However, I think to do that, my code really needs to call pipe/dup2 (see basically history of UNIX).

My problem is that I really want to combine that with django's call_command.

print(f'spool {sql_log_path}', file=dbshell_in)
print(f'set echo on', file=dbshell_in)
print(f'sho user', file=dbshell_in)
print(f'execute {stored_proc}', file=dbshell_in)
print(f'spool off')

What do I have to do to really call pipe and dup2 before I do this stuff?  I used to be a wizard at this stuff in perl, but I don't want to go back to perl... Python/Django is the world and anything more than that and SQL is overkill for the backend and one more thing to learn (except maybe invoking all of this stuff from Apache Airflow).

[Dan Davis]

Never mind - os.pipe, os.fork, and os.dup2 work quite well.  The only thing I needed was to open the output as a Python IO object, and to be careful about semicolons.  And I didn't want to go back to perl...  See the code at https://gist.github.com/danizen/de40b4e6bfa713fc76d6fe2cfaa236cc

This will allow me to have my Django management commands act as wrappers for the SQL stored procedures.