Oracle Database Connection in Python
Sometimes as part of programming, we are required to work with databases because we want to store a huge amount of information so we use databases, such as Oracle, MySQL, etc. So In this article, we will discuss the connectivity of Oracle database using Python. This can be done through the module name cx_Oracle.
Oracle Database: For communicating with any database through our Python program we require some connector which is nothing but the cx_Oracle module.
For installing cx-Oracle :
If you are using Python >= 3.6 use the below command in Linux: –
pip install cx-Oracle
If you are using Python >= 3.6 use the below command in Windows: –
py -m pip install cx-Oracle
By this command, you can install cx-Oracle package but it is required to install Oracle database first on your PC.
- Import database specific module
Ex. import cx_Oracle
- connect(): Now Establish a connection between the Python program and Oracle database by using connect() function.
con = cx_Oracle.connect('username/password@localhost')
- cursor(): To execute a SQL query and to provide results some special object is required that is nothing but cursor() object.
cursor = con.cursor()
- execute/executemany method:
cursor.execute(sqlquery) - - - -> to execute a single query. cursor.executemany(sqlqueries) - - - -> to execute a single query with multiple bind variables/place holders.
- commit(): For DML(Data Manipulation Language) queries that comprise operations like update, insert, delete. We need to commit() then only the result reflects in the database.
- fetchone(), fetchmany(int), fetchall():
- fetchone() : This method is used to fetch one single row from the top of the result set.
- fetchmany(int): This method is used to fetch a limited number of rows based on the argument passed in it.
- fetchall() : This method is used to fetch all rows from the result set.
- close(): After all done it is mandatory to close all operations.
Execution of SQL statement:
1. Creation of table
Table Created successfully
DDL statements don’t require to be committed. They are automatically committed. In the above program, I have used execute() method to execute an SQL statement.
2. Inserting a record into table using execute() method
Record inserted successfully
Once we execute any DML statement it is required to commit the transaction. You can commit a transaction in 2 ways: –
- con.commit(). This is used to commit a transaction manually.
- con.autocommit = True. This is used to commit a transaction automatically.
3. Inserting multiple records into a table using executemany() method
Multiple records are inserted successfully
There might be times when it is required to execute a SQL statement multiple times based on the different values supplied to it each time. This can be achieved using executemany() method. We supply a list containing a list of values that will replace placeholders in a SQL query to be executed.
From the above case
- :1 is substituted by value 10007
- :2 is substituted by value ‘Vikram’
- :3 is substituted by value 48000.0
And so on(next list of values in a given list)
Similarly, you can supply a list of dictionaries. But instead of placeholder, we will use the bind variable( discussed later).
4. View result set from a select query using fetchall(), fetchmany(int), fetchone()
[(10001, 'Rahul', 50000.5), (10002, 'Sanoj', 40000.75), (10003, 'Soumik', 30000.25), (10004, 'Sayan', 45000.0), (10005, 'Sobhan', 60000.1), (10006, 'Gururaj', 70000.0), (10007, 'Vikram', 48000.0), (10008, 'Sunil', 65000.1), (10009, 'Sameer', 75000.0)] [(10001, 'Rahul', 50000.5), (10002, 'Sanoj', 40000.75), (10003, 'Soumik', 30000.25)] (10001, 'Rahul', 50000.5)
In the above program, we have used 3 methods
- fetchall() : The fetchall() is used to fetch all records from the result set.
- fetchmany(int) : The fetchmany(int) is used to fetch the limited number of records from the result set based on the integer argument passed in it.
- fetchone() : The fetchone() is used to fetch one record from the top of the result set.
5. View result set from a select query using bind variable
[(10001, 'Rahul', 50000.5), (10005, 'Sobhan', 60000.1), (10006, 'Gururaj', 70000.0), (10008, 'Sunil', 65000.1), (10009, 'Sameer', 75000.0)]
In this case, I have passed a dictionary in execute() method. This dictionary contains the name of the bind variable as a key, and it’s corresponding value. When the SQL query is executed, value from the key is substituted in place of bind variable.