python oracle connection

python oracle connection

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):

python oracle connection

  • Now open your t
  • tnsnames.ora file and look for your connection.
  • It should look like the below example

SYSTEM_OCON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Host Name)(PORT = Port      Number))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Service Name)
(FAILOVER_MODE =
(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[0], '-', row[1]) # this only shows the first two columns. To add an additional column you'll need to add , '-', row[2], 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 Features

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

Conclusion

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.

SHARE THIS

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.