[Python] How to connect Python to Oracle database

ENVIRONMENT
- Python 3
- Red Hat Linux 8
- Oracle 18
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!