TL;DR: Python and Oracle 23ai: A few helpful ideas, programs and code-snippets that made my life with python and Oracle easier. This one is about Connecting and Connection-info.
Background:
Below are some of the other small pieces of code that I split off into separate py-files and that are included into most of my (oracle-db connected) programs.
Maybe they can help you.
Last year I built a small python program to print out the contents of vectors stored in Oracle (blog on "victor" here...). And I have a few more "tools" that I built to help in my occasional-program or in my regular dba-ish tasks.
Caveat 1: Of course you will build your own. I know ppl re-use code much better when they first type it themselves. I Know you will do this this differently, and you will do it Much Better.
Caveat 2: Don't expect high-tech: What I did with python is very simple, most of it is "pushed to the DBA" job. Things that I would call "plumber-stuff": process incoming files, updating some meta-data like LoV lists, produce simple interface-data, or very simple reports-to-mail. The kind of stuff that never seems to go away, no matter how sophisticated the new ecosystems become.
Environment-settings and other variables: python-dotenv.
As most programs have to work in various environments, I needed a way to quickly set or obtain the various variables for LOCAL, DEV, TEST, PRE and PROD. The easiest solution turned out to be a tool called python-dotenv.
If you dont know of this tool yet, read the intro (link), and play with it.
I am still grateful to Gianni and Jeff and a few others who tipped me about this gizmo. Thx Folks!
In my case, it started by keeping the oracle-connect information plus a few other more system- or env-specific variables that I need to distinguish between various environments. It also coincided nicely with the preference of my cstmr to "keep stuff in config files".
And suddenly I found python-dotenv very tempting for multiple use-cases, and I started to hoard a lot of "settable variables" into the file. Very Useful.
Open a db-connection, use a function (part 1)
As all my programs relate to an RDBMS, I need all programs to "connect" to the database. The information needed to connect was easily stored in a dotenv-file, but the creation of a connection needed some attention. For several reasons:
- We want credentials to be separate from code. Not hardcoded (and this time we mean it, looking at you Scott and Tiger..). Credentials need to be both safely stored and easily modifiable (some systems still change passwords every 32 days...)
- We want to verify that a connection is to the Correct, Intended database. Preferable to some logfile or stdout stream (check part 2).
The easiest part was to pick-up the credentials from the dotenv-file. To this extend, we created a function that picks up the connect-info (including tiger...) from an env-file. So far so good:
The first attempt looked like this:
Notice several things:
- We print some of the info to stdout, just to verify. Out-comment if you like, but I Want this check. More Later.
- We use easy-connect syntax.
- The function returns the connection-object, the connection does not have to be a global object.
If you want more complicated connects, if you prefer to use tns-aliases, or complete tns-entries, or other types of dsn: that would require a slight re-code. But you can stick to the concept of using dotenv, it is able to use multi-line variables. Hence you have a lot of options. I didnt have a need for more complicated connection-specifications yet, but in most cases, I would store the information in dotenv.
Open a db-connection, and Verify to stdout (part 2)
A very common mistake, and a rather Impactful Mistake, is to connect some infrequently used, adhoc-running metadata-update-tool to the wrong database... Ooops! (of course, in Your case, the passwords between Dev and Prod would be different, but some systems are made by Murphy... )
My previous blogpost on this was "how to set SQL-Prompt"..
https://simpleoracledba.blogspot.com/2023/06/do-you-know-where-you-are.html
And I wanted something similar to my python-programs.
Easy... In python, is it very simple to take any valid SELECT, have it executed, and print the results to stdout:
I inserted this snip of code into the ora_logon function:
cursor = ora_conn.cursor()
for row in cursor.execute ( sql_show_conn ):
print ( ' ora_login:', row[1] )
And now I can define sql_show_conn with any select I want, notably the ones I use in SQLcl and SQL*Plus to show my connection:
My favorite is:
# customize this sql to show connection info on logon
sql_show_conn="""
select 2 as ordr
, 'version : ' || substr ( banner_full, -12) as txt
from v$version
UNION ALL
select 1
, 'user : '|| user || ' @ ' || global_name|| ' ' as txt
FROM global_name gn
UNION ALL
SELECT 3
, 'prompt : ' || user
|| ' @ ' ||db.name
|| ' @ '|| SYS_CONTEXT('USERENV','SERVER_HOST')
|| decode (SYS_CONTEXT('USERENV','SERVER_HOST')
, '98b6d46dd637', ' (xe-dev)'
, 'ip-nnn-nn-2-109', ' (PROD)'
, 'ip-nnn-nn-0-226', ' (ACC)'
, '2c4a51017a44', ' (dckr-23ai)'
, 'b88d087b53d3', ' (dckr-23.7)'
, ' (-envname-)')
|| ' > '
FROM v$database db
order by 1
"""
This is the equivalent of setting a fancy prompt in SQLcl to indicate "where you are" (link to old blog).
This SQL, actually a UNION of three queries, roughly translates to : 1) show user and global_name, 2) show the version and 3) use a fancy decode to show which environment you are at, which I often use to list out dev/text/acc/prod and other varieties we use.
Because at a certain cstmer, the pdb-names and global names and service-names are often identical over several environments, we use the container-name or host-name to identify "where we are", and decode that to some "meaningful name" like "PROD".
And you can make the verification-query as fancy as you want (or just keep it simple). In some cases, it was turned into a local linux-style MOTD, and in some cases into a complete Welcome-Message including some instructions and warnings.
You can add items to this cursor at your hearts content, and the union + order trick allow you to re-arrange the outcomes. Knock yourself Out (some devs really do!)
The result, in my case, comes out like this:
The complete, include-able function, together with some test-code at the bottom of the file, can be found here: link to ora_login.py.
Summary: Feel free to re-use, but...
From thinking- and coding my logon-function, using dotenv and my own logon-function, I got some good benefits.
But, big Caveat: Tooling is Personal, and YMMV.
Over time I have taken to use a lot of self-built tools and scripts, and some tools+utils I picked up from 't internet or other coders (and mostly modified for my own use).
I did find that it is difficult to get others to re-use tools that I crafted, or any tool from anyone else.
I have a few other snippets of python code that I (re-)use a lot, and I will see how those develop over time (if at all..). So far, if my code or just my ideas does inspire some of you to improve your code: Job done.
My main tip to others on code-reuse is generally: Read, Consider, Try, ... and in case of doubt: DIY.
The most (re)used scripts and code will probably be the "built you own" version.
Enjoy !
-- -- -- -- -- end of this blogpost, for now -- -- -- --
