Connecting Python to Oracle

I’m sure there are bunch of tutorials, web pages, videos about it. This is my version while working on Python server environment. Maybe someone else can benefit from this as well. This will be the template for Oracle connection for my future posts.

Photo by Kelly Sikkema on Unsplash

You should able to access to some working folder depending on your configuration. Let’s call it home folder. The necessary libraries should also be installed by you or your IT department. In the code, I only import them to use.

There is a Settings folder in my home folder which can be accessed only by me. (except admins of course) My template files, configuration files, project files/folders etc. are stored here.

In the Settings folder, I saved a text file with the username on the first line and the password on the second line. This is your initiative of course, I can’t make you write your password openly even the file or folder is only accessible by you.But I believe this is secure enough. The advantage of this method is, you don’t need the update or check every time a code executes if your password is up-to-date. My password expires periodically. If you don’t want your password to be saved in the file, I recommend delete the whole file or delete password information every time code execution completed. Alternatively, you can embed the username and password into the code. But I won’t explain it in this template.

This code gets the username, user password, default export folder.

import os
import pandas as pd
import cx_Oracle

def get_user_info():
	home_folder = "/data/home/myusername" # This is a manual definition.
	oracle_file_path = os.path.join(home_folder, "Settings", "oracle.txt")
	export_file_path = os.path.join(home_folder, "Exports")
	
	try:
		with open(oracle_file_path, "r") as oracle_file:
			oracle_file_contents = oracle_file.readlines() # Read whole file
			oracle_user = oracle_file_contents[0}.split("\n")[0] # Reads the username
			oracle_pass = oracle_file_contents[1] # Reads the password
			return oracle_user, oracle_pass, export_file_path # Returns 3 variables
	except Exception as ex:
		return "None" # In case if it gives an error

# Check the code
# get_user_info()[1]

In the Settings folder, there is file called Oracle Connections.xlsx. (I am using only Oracle connections) In this file there are 2 columns called Alias and ConnectionInfo. First one (Alias) shows the alias of the connection. Second one (ConnectionInfo) shows the connection string information. Since these connection strings are constant (not frequently changes) I believe it is useful to store in the file and call them at runtime (while the code executing) instead of writing the connection strings in the code every time. (or check all your codes if one of your connection string is changed.)

Since all codes are in the same Jupyter Notebook, you don’t need the import same libraries again. If this is not the case, you need the import the necessary libraries.

def get_oracle_connection_info(connection): # connection input argument will be inserted here when the code executes.
	try:
		home_folder = "/data/home/myusername" # This is a manual definition.
		connection_file_path = os.path.join(home_folder, "Settings", "ORACLE_CONNECTIONS.xlsx")
		connections_file = pd.read_excel(connection_file_path) # Pandas library reads the excel with read_excel
		df = pd.DataFrame(connections_file, columns=["Alias", "CONNECTION_INFO"]) # If you change the column headers in the excel file, this line should be updated as well.
		df_oracle_connection = df[df["Alias"] == connection] # Based on given input argument, necessary connection string retrieved. We are simulating Vlookup function in Excel.
		
		return df_oracle_connection	# Returns the connection string
	except Exception as ex:
		print(f"Connection failed, \nError: {ex}")
		return "None"

# Check the code
# get_oracle_connection_info(connection="ORACLE_1") # ORACLE_1 is just a dummy example. You should replace the alias and update the file based on your configuration

This is the main part. Everything before it consolidates and makes sense.

If you work locally, installation of cx_Oracle library is sufficient. Since working environment is local, Oracle client should be already installed. On Python server environment, it should be explicitly defined where the Oracle client libraries are located. Definition is sufficient, the code handles the rest on its own.

def connect_db(connection): # connection input argument will be inserted here when the code executes.
	oracle_connection_result = get_oracle_connection_info(connection) # This calls the get_oracle_connection_info function which was previouly defined and retrieves the connection string based on given database alias input.
	oracle_connection_user = get_user_info()[0] # This is username (hence the name)
	oracle_connection_password = get_user_info()[1] # This is the password for that user
	
	os.environ["ORACLE_HOME"] = "data/app/oracle/product/12.2.0/client_1" # It can be changed based on your database. Please consult your IT department.
	
	try:
		oracle_connection = cx_Oracle.connect(user = oracle_connection_user, password = oracle_connection_password, dsn = oracle_connection_result, encoding="UTF-8")
		return oracle_connection # Connects to Oracle database in there are no errors.
	except Exception as ex:
		print(f"Connection failed, \nError: {ex}")

# Check the code
# conn = connectdb("ORACLE_1") # ORACLE_1 is just a dummy example. You should replace the alias and update the file based on your configuration

Given the necessary connection information, it would be sufficient to connect to the Oracle database. Here is an example:

You can add try except block here but I believe it is unnecessary because they are already defined in the functions above.

oracle_connection = connect_db("ORACLE_2) # This is it. No username, no password, no connection string
cursor = oracle_connection.cursor()  # Opens a cursor
sql_query = "SELECT * FROM TBL_CALENDAR FETCH FIRST 20 ROWS ONLY" # Sample dummy table. DUAL table can be used too.

# Execute the query with read_sql_query and insert the results into Pandas DataFrame.
result_df = pd.read_sql_query(sql_query, oracle_connection)
print(result_df)

cursor.close()
oracle_connection.close()

And finally, we should save this module as python_to_oracle.py in order to import and use as module in other files.

You can access the files here.