Simple ORACLE Database Connection with Python

How to work with Oracle through Python?

Installation of Oracle latest version 19c.

Go to oracle.com website at the following link:

http://www.oracle.com/technetwork.datbases.database-technologies/express-edition/downlaods/index.html

So after this you able to download and install on your system but you need another thing Oracle Database Driver.

Installing Oracle Database Driver

We have to install a driver or connector software that connects the Oracle database with our Python programs.

The name of the driver needed is 'ex_Oracle' and it can be installed using pip command that comes with python by default. Just go to system prompt and type the command like below:

C:\>pip install cx_Oracle

Now let me give you a simple program to understand.

Program # 1 : 

import cx_Oracle

conn = cx_Oracle.connect('SYSTEM/ABHINAW@localhost')

cursor = conn.cursor()

cursor.execute("select * from table")

row = cursor.fetchone()

while row is not None:

    print(row)

    row = cursor.fetchone()

cursor.close()

conn.close()

Output: 

C:>python ora.python

(1001,'ABHINAW',100000)

(1002,'JAYANTA',100000)

(1003,'PRITAM',100000)

(1004,'BIPRADIP',100000)

Now since we are discussing ORACLE so let me also tell you about "Stored Procedures"

Stored Procedures

So a stored procedure is a set of statements written using PL/SQL(Procedural  Language / Structured Query Language). To perform some calculations on tables of a database, we can use stored procedures. Stored procedures are written and stored at the database server. So when a client contacts the server, the stored procedures are executed and results are sent to the client.

Stored Procedures are compiled once and stored in the executable form at the server-side. The memory for stored procedures is not allocated every time they are called. Stored Procedures are stored in cache memory at the server-side and they are immediately available to clients. This is one of the main advantages of Stored Procedures. 

let me give you an example of how to write the stored procedures program:

-- myproc.sql

-- to increase number in my table

create or replace provider myproc(no in int,isal out float) as

salary float;

begin 

         select sal into salary from table where someid=no

         isal := Salary+1000;

end;

/

Note: atlast line you see '/' indicates to execute the procedure at server side.

Now Points to Remember:

• A database management system(DBMS) represents software that stores and manage data.
• After installing the ORACLE driver we can see a new module by name 'cx_Oracle' in the Python library.
• The SQL commands are almost the same for all databases like MYSQL or ORACLE.