Cx_Oracle Python with Example

Python Programming Language:

Python programming language is one of the most used programming languages, as it is used widely in the field of software and data analysis, web development, etc. It is said to be a user-friendly programing language, as the syntax for it is very simple to write and easy to understand for a beginner programmer. Python programming language is rich in libraries that can be imported easily and used to perform many different operations. In the year 1989, Guido van Rossum is the one who introduced python programming language.

It is also used in web applications; web applications like the Django and Flask frameworks are created using python. Compared to any programming language, the syntax in python is much easier. Many colleges and institutions have introduced python in their syllabus so that the students need to learn python. The biggest advantage of the python programming language is that it has a good collection of libraries widely used in machine learning, web frameworks, test frameworks, multimedia, image processing, and many more application. Python supports multiple programming paradigms, including object-oriented, imperative, functional, and procedural.

Cx_Oracle Python

Because we need to store a lot of data, we sometimes need to work with databases as part of programming. Examples of these databases include Oracle, MySQL, and others. Therefore, we will talk about Oracle database connectivity using Python in this article. The cx Oracle module can be used for this.Access to the Oracle Database is made possible through the Python extension module cx Oracle. With numerous additions and a few omissions, it complies with the Python database API 2.0 definition.

Versions 3.6 through 3.10 of Python were used to test cx Oracle 8.3. It is possible to utilise older releases of cx Oracle with earlier Python releases. Connection to both more recent and more ancient databases is possible thanks to Oracle's standard client-server compatibility.The Python database API 2.0 specification has been followed in the creation of the cx Oracle. Additionally, it offers you a number of upgrades made especially for the Oracle Database.

The Python 2.7 and versions 3.5 to 3.7 all operate flawlessly with the cx Oracle 7.x. We will only utilize Python versions 3.5 to 3.7 in this part. Oracle 11.2, 12.1, 12.2, 18.3, and 19.3 client libraries are compatible with the cx Oracle.

Oracle Database: In order for our Python software to communicate with any database, we need a connector, and that connector is the cx Oracle package.

Installation of Cx-Oracle

The cx-oracle package can be installed using the python package, for the installation of the oracle the version of python must be greater than 3.6

Command:

Py -m  install cx-oracle

You can install the cx-Oracle package with this command, but first you must install the Oracle database on your computer.

Import a Module Specific to a Database

For instance, import cx-Oracle

• Connect (): Now Using the connect() function, connect the Python programme to the Oracle database.
  cxOracle.connect = con
  ('username/password@localhost')

• Cursor(): In order to run a SQL query and return results, a special object called cursor() is needed.
  cursor= con
  execute/executemany cursor() method
  To run a single SQL query, use cursor.execute(sqlquery).
  To perform a single query with several bind variables or place holders, use cursor.executemany(sqlqueries).

• Commit( ): For DML (Data Manipulation Language) queries that include update, insert, and delete operations. Only after we commit() will the result appear in the database.
  fetchone(), fetchmany(int), and fetchall()

• Fetchone( ): Fetch one row at a time from the top of the result set using the fetchone() function.
• Fetchmany(int): Using the input supplied to it, the fetchmany(int) function retrieves a set number of rows.
• Fetchall( ): Fetch all rows from the result set using the fetchall() function.
• Close(): All operations must be closed after they are finished.

Operations

cursor.close( )

con.close( )

SQL Statements Execution

1. Table creation

# importing module
importcx_Oracle
# With the help of the oracle database creating the table
try:
	con = cx_Oracle.connect('tiger/scott@localhost:1531/xe')
	print(con.version)
	# execute the sqlquery
	cursor = con.cursor()
# Creating a table employee
	cursor.execute(
		"create table employee(empid integer primary key, name varchar2(30), salary number(10, 2))")
print("We have created the table succesfully")
exceptcx_Oracle.DatabaseError as e:
	print("We can find a problem with the oracle", e)


# by writing finally, we may also shut down all database operations if an error arises.
finally:
	if cursor:
		cursor.close()
	if con:
		con.close()

Output:

We have created the table succesfully

Statements in DDL don't need to be committed. They are unquestionably bound. I've used the execute() method to run a SQL statement in the aforementioned software.

2. Using the execute( ) method to insert a data into the table

# importing module
importcx_Oracle


# creating an entry in an Oracle database table
try:
	con = cx_Oracle.connect('tiger/scott@localhost:1511/xe')
	cursor = con.cursor()
#con.autocommit = True
	#Insertion of the value into the table of employees
	cursor.execute('insert into employee values(1002,\'Nihaas\',5000.50)')


	# commit() to make changes reflect in the database
	con.commit()
	print('We have successfully inserted the value into table')


exceptcx_Oracle.DatabaseError as e:
	print("There is a problem with Oracle", e)


# by writing finally, we may also shut down all database operations if an error arises.
finally:
	if cursor:
		cursor.close()
	if con:
		con.close()

Output:

'We have successfully inserted the value into table'

Any DML statement we run must be followed by a transaction commit. There are two ways to commit a transaction:

• con.commit(). This is employed when manually committing a transaction.
• autocommit = True for con. This is used to automatically commit a transaction.

3. Using the executemany() function to add several records to a table

importcx_Oracle


# Load data from a csv file into Oracle table using executemany
try:
	con = cx_Oracle.connect('tiger/scott@localhost:1521/xe')


exceptcx_Oracle.DatabaseError as er:
	print('There is an error in Oracle database:', er)


else:
	try:
		cur = con.cursor()
		data = [[10007, 'Varun', 48000.0], [10008, 'Sagar', 65000.1], [10009, 'Santhosh', 75000.0]]


		cur = con.cursor()
		# Inserting multiple records into employee table
		# (:1,:2,:3) are place holders. They pick data from a list supplied as argument
		cur.executemany('insert into employee values(:1,:2,:3)', data)


	exceptcx_Oracle.DatabaseError as er:
		print('There is an error in Oracle database:', er)


	except Exception as er:
		print(er)


	else:
		# To commit the transaction manually
		con.commit()
		print('We have successfully inserted the multiple records')


finally:
	if cur:
		cur.close()
	if con:
		con.close()

Output:

We have successfully inserted the multiple records

A SQL statement could need to be run more than once depending on the varied values that are supplied to it each time. Utilizing the executemany() method, this is possible. We provide a list of values that will be used in place of placeholders in an upcoming SQL query.

4. Using a bind variable, view the result set from a select query:

importcx_Oracle


try:
	con = cx_Oracle.connect('tiger/scott@localhost:1521/xe')


exceptcx_Oracle.DatabaseError as er:
	print('There is error in the Oracle database:', er)


else:
	try:
		cur = con.cursor()


		cur.execute('select * from employee where salary > :sal', {'sal': 50000})
		rows = cur.fetchall()
		print(rows)


	exceptcx_Oracle.DatabaseError as er:
		print('There is error in the Oracle database:', er)


	except Exception as er:
		print('Error:', er)


	finally:
		if cur:
			cur.close()


finally:
	if con:
		con.close()

Output:

[(10001, 'Rohit', 50000.5), (10005, 'Solmon', 60000.1), (10006, ‘Rishika', 70000.0),
 (10008, 'Deeraj', 65000.1), (10009, 'Nikitha', 75000.0)]

In this instance, the execute() method received a dictionary as a parameter. The name of the bind variable is a key in this dictionary, and its matching value is also a key. When the SQL query is run, the bound variable is replaced with the value from the key.

Conclusion

Because we need to store a lot of data, we sometimes need to work with databases as part of programming. Examples of these databases include Oracle, MySQL, and others. Therefore, we will talk about Oracle database connectivity using Python in this article. The cx Oracle module can be used for this.Access to the Oracle Database is made possible through the Python extension module cx Oracle. With numerous additions and a few omissions, it complies with the Python database API 2.0 definition.

Versions 3.6 through 3.10 of Python were used to test cx Oracle 8.3. It is possible to utilise older releases of cx Oracle with earlier Python releases. Connection to both more recent and more ancient databases is possible thanks to Oracle's standard client-server compatibility.The Python database API 2.0 specification has been followed in the creation of the cx Oracle. Additionally, it offers you a number of upgrades made especially for the Oracle Database.The Python 2.7 and versions 3.5 to 3.7 all operate flawlessly with the cx Oracle 7.x. We will only utilise Python versions 3.5 to 3.7 in this part. Oracle 11.2, 12.1, 12.2, 18.3, and 19.3 client libraries are compatible with the cx Oracle.