Oracle Database Analysis and Manipulation Through Python
Sometimes we want to develop use-cases that is data-driven, but we face the problem of low level of robustness with Databases and SQL when we talk about complex mathematical operations and analysis, so to resolve this issue we need to access these databases using one of the high-level programming languages (e.g., Java, Python, etc.).
In this particular blogpost I will discuss how can we access, and manipulate Oracle databases using Python.
Oracle developed a third-party module in Python that can communicate with their databases, that enables to the user (CRUD) operations, and allows you to store data from the database in your preferred Python or another third-party data structure (e.g., Pandas DataFrame, Python List, etc.), the name of the module is (cx_Oracle), I will go through basic example in the usage of this module to make it more understandable
import cx_Oracle
import pandas as pd# We have to enter the credentials to access to our Oracle database and the credentials should entered as followed:access = 'username/password@host(IP Address):PortNumber/ServiceName'conn = Nonetry:
# Try to connect with the database using the entered credentials aboveconn = cx_Oracle.connect(access) # Through the cursor we can execute SQL queries
cur = conn.cursor()
query = "SELECT * FROM student"
# Execute our desired query
cur.execute(query) # Fetch all the results that came from the executed query above, then store them in Python data structure which is (list of tuples) records = cur.fetchall()
# Then we can transform the results that we got from the database to Panads DataFrame using from_record() and it transform ndarray to Pandas DataFrame df = pd.DataFrame.from_records(records)
# Handling exceptions if it occur during the connection or query executionexcept Exception as err:
print("Error while connection to the database")
print(err)# If everything worked well, then we close the cursor, and the connection with the database for safety and security purposes finally:
if(conn):
cur.close()
conn.close()
There are plenty of operations and manipulation that can be done using (cx_Oracle) module, but I tried to give a glimpse of the module, and always you can refer to (cx_Oracle) module documentation for more details.
Also if you to connect other database server with your Python code such as (MySQL), there is multiple packages and module for different database servers that you can use to communicate with your database through Python code, I will provide two helpful sources below
Many thanks for your careful reading, I hope that you gained knowledge from this blogpost, and I hope it was enjoyable blogpost.
Hamad S. Alassafi
