[Python] How to connect Python to Oracle database

Hello everybody,

today I’m going to show you how to connect your python script/application to an Oracle Database.

First of all, you need to download the Oracle libraries:

python -m pip install cx_Oracle --upgrade

Now, let’s start!

Open a new python script and type:

import cx_Oracle #importo Oracle Libraries
vito0omp_low = 'tns_string' #define your tns string
connection = cx_Oracle.connect("username","password",dsn=vito0omp_low) #start connection defining username, password and tns string
cursor=connection.cursor() #open cursor on db

Now we can define the statement to be run and how to run it.

For the select statement we can run this:

statement="select * from dual" #prepare the statement
cursor.execute(statement) #execute the select on db
for result in cursor: #fetch the results
  print(result)

As you can see, the cursor now is a list that contains rows, and each row is a list itself.

If you need to run an insert/update/delete you can type something like this:

statement="insert into log values (" + str(id_from) + ",'" + name +"',to_date('"+ dt_string + "','ddmmyyyyhh24miss'),'"+ message +"')" #prepare the insert
cursor.execute(statement) #exec the insert
connection.commit() #exec the commit

Eventually, you can check if the statement has failed using the cursor.

Lastly, we can close the connection:

cursor.close() #close the cursor
connection.close() #close the connection

That’s it, now your python script/application can connect to an Oracle Database.

Regards!