Python Script to make connection for Oracle databases.

Share via:

Post Views: 0

Python Script to make connection for Oracle databases.

Hello Folks,

This article will explain you about installation cx_Oracle module and basic python script to make connection to Oracle databases.

Check the Python Version:

[root@ociktexperts3 ~]# python3 --version
Python 3.6.8

1 2	[root@ociktexperts3 ~]# python3 --version Python 3.6.8

Install oracle package cx-Oracle:

cx_Oracle is a Python extension module that enables access to Oracle Databases.

cx_Oracle is a Python extension module that enables access to Oracle Databases.
[root@ociktexperts3 ~]# pip3 install cx_Oracle
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Collecting cx_Oracle
  Downloading https://files.pythonhosted.org/packages/ec/28/84cc23a2d5ada575d459a8d260286d99dde4b00cafcc34ced7877b3c9bf0/cx_Oracle-8.3.0-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (858kB)
    100% |â–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆ| 860kB 1.9MB/s 
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-8.3.0

cx_Oracle is a Python extension module that enables access to Oracle Databases.

[root@ociktexperts3 ~]# pip3 install cx_Oracle

WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.

Collecting cx_Oracle

Downloading https://files.pythonhosted.org/packages/ec/28/84cc23a2d5ada575d459a8d260286d99dde4b00cafcc34ced7877b3c9bf0/cx_Oracle-8.3.0-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (858kB)

100% |â–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆâ–ˆ| 860kB 1.9MB/s

Installing collected packages: cx-Oracle

Successfully installed cx-Oracle-8.3.0

Connect to Oracle Pluggable database.

[oracle@ociktexperts3 ~]$ sqlplus KTEXPERTS/Ktexperts123@KTEXPERTS_PDB2
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 4 21:33:02 2024
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle.&nbsp; All rights reserved.
Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

KTEXPERS>>

[oracle@ociktexperts3 ~]$ sqlplus KTEXPERTS/Ktexperts123@KTEXPERTS_PDB2

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 4 21:33:02 2024

Version 19.20.0.0.0

Connected to:

Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production

Version 19.20.0.0.0

KTEXPERS>>

Check Employee table for any records.

KTEXPERS>> select * from KTEXPERTS.EMPLOYEE;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-MAR-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-MAR-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-MAR-81       1250        500         30
      7566 JONES      MANAGER         7839 02-MAR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-MAR-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAR-81       2850                    30
      7782 CLARK      MANAGER         7839 09-MAR-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-MAR-82       3000                    20
      7839 KING       PRESIDENT            17-MAR-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-MAR-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-MAR-83       1100                    20
      7900 JAMES      CLERK           7698 03-MAR-81        950                    30
      7902 FORD       ANALYST         7566 03-MAR-81       3000                    20
      7934 MILLER     CLERK           7782 23-MAR-82       1300                    10

KTEXPERS>> select * from KTEXPERTS.EMPLOYEE;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-MAR-80 800 20

7499 ALLEN SALESMAN 7698 20-MAR-81 1600 300 30

7521 WARD SALESMAN 7698 22-MAR-81 1250 500 30

7566 JONES MANAGER 7839 02-MAR-81 2975 20

7654 MARTIN SALESMAN 7698 28-MAR-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAR-81 2850 30

7782 CLARK MANAGER 7839 09-MAR-81 2450 10

7788 SCOTT ANALYST 7566 09-MAR-82 3000 20

7839 KING PRESIDENT 17-MAR-81 5000 10

7844 TURNER SALESMAN 7698 08-MAR-81 1500 0 30

7876 ADAMS CLERK 7788 12-MAR-83 1100 20

7900 JAMES CLERK 7698 03-MAR-81 950 30

7902 FORD ANALYST 7566 03-MAR-81 3000 20

7934 MILLER CLERK 7782 23-MAR-82 1300 10

Python script to connect Oracle database and execute SQL Query

[oracle@ociktexperts3 ~]$ cat dbconn_emptable.py

1	[oracle@ociktexperts3 ~]$ cat dbconn_emptable.py

import cx_Oracle
con=cx_Oracle.connect('KTEXPERTS/Ktexperts123@KTEXPERTS_PDB2')
if con!= None:
  print('Successfully connected  to KTEXPERTS')
else:
  print('connected Failed')
cur = con.cursor()
cur.execute('select empno,ename,sal,comm,deptno from employee where deptno=20')
for result in cur:
  print(result)
cur.close()
con.close()

import cx_Oracle

con=cx_Oracle.connect('KTEXPERTS/Ktexperts123@KTEXPERTS_PDB2')

if con!= None:

print('Successfully connected to KTEXPERTS')

else:

print('connected Failed')

cur = con.cursor()

cur.execute('select empno,ename,sal,comm,deptno from employee where deptno=20')

for result in cur:

print(result)

cur.close()

con.close()

Execute the Python script.

[oracle@ociktexperts3 ~]$ python3 dbconn.py

1	[oracle@ociktexperts3 ~]$ python3 dbconn.py

Script Output:

Successfully connected  to KTEXPERTS
(7369, 'SMITH', 800.0, None, 20)
(7566, 'JONES', 2975.0, None, 20)
(7788, 'SCOTT', 3000.0, None, 20)
(7876, 'ADAMS', 1100.0, None, 20)
(7902, 'FORD', 3000.0, None, 20)