Example of connecting Python with Oracle database

Syntax used for Python to make connection with sysdba or simple user with Oracle database

Different example for connectivity with Oracle database with python language:

  1. First pass the direct connection string
import cx_Oracle
try: 
    # Type 1 Connection
    # pass the direct connection string for connection
    con = cx_Oracle.connect('hr/hr@localhost:1521/pdb1')
    c = con.cursor()
    c.execute('select username,account_status from user_users')
    for row in c:
        print (row[0], '-', row[1]) 
except cx_Oracle.DatabaseError as e:
    print("There is a problem with Oracle", e)
finally:
    if c:
	c.close()
    if con:
        con.close()
		

2. Pass the username and password with direct tnsnames.ora entry alias name already created in Oracle SERVER/CLIENT.

Note: You can use second connection string for sysdba user

import cx_Oracle
try:
    #Type 2 Connection
    #Pass direct tnsname entry which used in tnsnames.ora file
   
    #login with provide username and password without sysdba user
    con = cx_Oracle.connect(user=r'hr', password='hr', dsn='pdb1')
    
    #Login with provide username and password for sysdba user
    #con = cx_Oracle.connect(user=r'hr', password='hr',mode = cx_Oracle.SYSDBA, dsn='pdb1')   

    c = con.cursor()
    c.execute('select username,account_status from user_users')
    for row in c:
        print (row[0], '-', row[1]) 
except cx_Oracle.DatabaseError as e:
    print("There is a problem with Oracle", e)
finally:
    if c:
	    c.close()
    if con:
       con.close()

3. Create connection string like tns entry within the program and use for the connnection.

import cx_Oracle
try:

    #Type 3 Example:
    #Make first TNS Entry for detail and then put username & password
    tns_entry=cx_Oracle.makedsn('localhost',1521,'db1')
    #use for sysdba user
    con = cx_Oracle.connect(user=r'hr', password='hr',mode = cx_Oracle.SYSDBA,dsn=tns_entry)
	#use for simple user
    #con = cx_Oracle.connect(user=r'hr', password='hr',dsn=tns_entry)
    
    c = con.cursor()
    c.execute('select username,account_status from user_users')
    for row in c:
        print (row[0], '-', row[1]) 
except cx_Oracle.DatabaseError as e:
    print("There is a problem with Oracle", e)
finally:
    if c:
	    c.close()
    if con:
       con.close()
		

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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