Why is Python important for Oracle DBAs these days?
Traditionally, Bash and Perl are two most languages chosen by Oracle DBAs for scripting in operating systems. However, these days Python has changed the game. Python is flexible and there are many developed libraries available to work with. All modern Unix and Linux systems come with Python on board; for example, Oracle Linux 6.1 ships with Python 2.6.6. Besides, since Oracle Autonomous came, DBAs’ roles have changed. Thus, learning Python and Oracle database together has more advantages for your future career. Obviously start point is to know, how to connect to Oracle database with Python.
Requirements:
Python
First of all, you have to install Python on the computer which the Python code is going to be run. For download please check on python.org. Please download the latest stable version. At the moment the latest version is 3.8.1
Installing Python is pretty straightforward. Please flow the instruction in the mentioned above web site. Open your command prompt/terminal you have to see your Python version with the commands below:
1 2 3 4 5 | C:\>where python C:\python\python38\python.exe C:\>python --version Python 3.8.1 |
cx-Oracle
cx_Oracle is a module that enables access to Oracle Database and conforms to the Python database API specification. by clicking on the link, you will find good documentation regards to use cases and examples using cx-Oracle. We’ll install cx-library with the commands below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | C:\>pip list Package Version ---------- ------- pip 20.0.2 setuptools 41.2.0 C:\>pip install cx-oracle Collecting cx-oracle Downloading cx_Oracle-7.3.0-cp38-cp38-win32.whl (135 kB) |████████████████████████████████| 135 kB 3.2 MB/s Installing collected packages: cx-oracle Successfully installed cx-oracle-7.3.0 C:\>pip list Package Version ---------- ------- cx-Oracle 7.3.0 pip 20.0.2 setuptools 41.2.0 |
If you have difficulties with installation cx-oracle, please check the cx- oracle installation document.
Oracle Instant Client
If your Oracle database is on a remote computer, then download and unzip the client libraries from the free Oracle Instant Client “Basic” or “Basic Light” package for your operating system architecture. Download Instant Client
For Windows users, it’s important to add Instant Client’s location into the Path. Also if you want to use TNS for advantage simplifying connection to the Oracle database, you have to add TNS_ADMIN to the Environment Variable.
1 2 3 4 5 6 7 | C:\>setx PATH "%PATH%;C:\Programs\instantclient_19_5" SUCCESS: Specified value was saved. C:\>setx TNS_ADMIN "C:\Programs\oracle_network" SUCCESS: Specified value was saved. |
At the end of preparation for connecting to Oracle database with Python, it’s worth to know through the below diagram, what exactly will happen between Python and Oracle Database.
Connect to Oracle database with Python
before having the first connection, let’s check our database and TNSNAMES.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | [oracle@abadan ~]$ crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE abadan STABLE ONLINE ONLINE shiraz STABLE ora.chad ONLINE ONLINE abadan STABLE ONLINE ONLINE shiraz STABLE ora.net1.network ONLINE ONLINE abadan STABLE ONLINE ONLINE shiraz STABLE ora.ons ONLINE ONLINE abadan STABLE ONLINE ONLINE shiraz STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup) 1 ONLINE ONLINE abadan STABLE 2 ONLINE ONLINE shiraz STABLE 3 ONLINE OFFLINE STABLE ora.DATA.dg(ora.asmgroup) 1 ONLINE ONLINE abadan STABLE 2 ONLINE ONLINE shiraz STABLE 3 OFFLINE OFFLINE STABLE ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE abadan STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE shiraz STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE shiraz STABLE ora.abadan.vip 1 ONLINE ONLINE abadan STABLE ora.asm(ora.asmgroup) 1 ONLINE ONLINE abadan Started,STABLE 2 ONLINE ONLINE shiraz Started,STABLE 3 OFFLINE OFFLINE STABLE ora.asmnet1.asmnetwork(ora.asmgroup) 1 ONLINE ONLINE abadan STABLE 2 ONLINE ONLINE shiraz STABLE 3 OFFLINE OFFLINE STABLE ora.cvu 1 ONLINE ONLINE shiraz STABLE ora.hhpdb.db 1 ONLINE ONLINE abadan Open,HOME=/u01/app/o racle/product/19.3/d b_1,STABLE 2 ONLINE ONLINE shiraz Open,HOME=/u01/app/o racle/product/19.3/d b_1,STABLE ora.hhpdb.nima.svc 1 ONLINE ONLINE abadan STABLE ora.qosmserver 1 ONLINE ONLINE shiraz STABLE ora.scan1.vip 1 ONLINE ONLINE abadan STABLE ora.scan2.vip 1 ONLINE ONLINE shiraz STABLE ora.scan3.vip 1 ONLINE ONLINE shiraz STABLE ora.shiraz.vip 1 ONLINE ONLINE shiraz STABLE -------------------------------------------------------------------------------- |
As you saw, our cluster is up and running. We’ve got a service, its name is nima. We are going to use this service which has been registered on SCAN to connect to the database.
TNS on the client machine:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | C:\>echo %TNS_ADMIN% C:\Programs\oracle_network C:\>cd C:\Programs\oracle_network C:\Programs\oracle_network>dir Directory of C:\Programs\oracle_network 05/02/2020 17:16 <DIR> . 05/02/2020 17:16 <DIR> .. 05/02/2020 17:17 201 tnsnames.ora 1 File(s) 201 bytes 2 Dir(s) 58,831,081,472 bytes free |
1 2 3 4 5 6 7 8 9 | NIMA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = nima-scan.masoudhhp.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = nima.masoudhhp.com) ) ) |
Finally the Python code:
1 2 3 4 5 6 7 8 9 10 | import cx_Oracle # Connect as user "hr" with password "hr" to the "nima" service running on remote cluster. connection = cx_Oracle.connect("hr", "hr", "nima") cursor = connection.cursor() sql = "select first_name, last_name from employees where job_id = :job order by last_name" job_id = 'IT_PROG' cursor.execute(sql, job= job_id) for fname, lname in cursor: print(f"{fname:<20} {lname:<20}") |
Output:
1 2 3 4 5 6 | PS C:\Users\User> & C:/python/python38/python.exe c:/Users/User/Desktop/Pyo.py David Austin Bruce Ernst Alexander Hunold Diana Lorentz Valli Pataballa |
I hope this post be useful to you.
