We will Know the step by step “python oracle connection”. cx_Oracle is an interface that connects the oracle database with python database API. It is a python extension. It is currently supported and tested against Oracle Client 19, 18, 12, and 11.2, and Python 2.7, 3.5, 3.6 and 3.7 version. it is also an open-source license (the BSD license).
You can call Python programs using cx_Oracle functions. Internally cx_Oracle dynamically loads Oracle Client libraries to access Oracle Database.
The following are the step by step method to python oracle connection.
Step 1: Download and Install the cx_Oracle package.
Step 2: Get the Connection information as below.
- Get the connection information. for finishing this step you can locate your tnsnames.ora file on your computer. (e.g., type tnsnames.ora in the Windows search bar):
- Now open your t
- tnsnames.ora file and look for your connection.
- It should look like the below example
(ADDRESS = (PROTOCOL = TCP)(HOST = Host Name)(PORT = Port Number))
(LOAD_BALANCE = YES)
(SERVER = DEDICATED)
(SERVICE_NAME = Service Name)
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
Step 3: Now try to connect Python to Oracle as below.
- Now you use the below code in Python to connect oracle.
import cx_Oracle dsn_tns = cx_Oracle.makedsn('Host Name', 'Port Number', service_name='Service Name') #if needed, place an 'r' before any parameter in order to address any special character such as '\'. conn = cx_Oracle.connect(user=r'User Name', password='Personal Password', dsn=dsn_tns) #if needed, place an 'r' before any parameter in order to address any special character such as '\'. For example, if your user name contains '\', you'll need to place 'r' before the user name: user=r'User Name' c = conn.cursor() c.execute('select * from database.table') # use triple quotes if you want to spread your query across multiple lines for row in c: print (row, '-', row) # this only shows the first two columns. To add an additional column you'll need to add , '-', row, etc. #conn.close()
Note: Execute the below query to get the Service Name in Python
- select sys_context(‘userenv’,’service_name’) from dual
Execute the below query to get the list of users:
- select username from dba_users
Now using this way you can establish a connection with oracle in Python. Now Enjoy.
cx_Oracle feature key points are below :
- It can be easily installed from PyPI
- It currently supports Python 2 and 3 and for multiple Oracle Database versions
- Execution of SQL and PL/SQL statements
- Oracle data type support, including large objects (CLOB and BLOB) and binding of SQL objects
- Connection management, including connection pooling
- It uses the Oracle Network Service infrastructure, including encrypted network traffic and security features.
- SODA (Simple Oracle Document Access)
- Used for fetching of large result sets.
- supports REF CURSOR.
- It is used to fetch PL/SQL Implicit Results. It provided simple return query results from PL/SQL.
- Work for Advanced Queuing. it can be used database notifications to build micro-service applications.
- Used for end-to-end monitoring and tracing.
- best features like Session Pooling, with tagging and session state fix-up callback.
- Database Resident Connection Pooling (DRCP).
- Privileged Connections.
- Provides Sharded Databases.
- Supports a feature like FAN notifications and Transaction Guard support.
- It is used to support for setting application context during the creation of a connection, making application metadata more accessible to the database, including in LOGON triggers.
You can read my Post: C# algorithm
I hope you liked this article about the “python oracle connection”. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.