Beginning with Oracle using cx_Oracle in Python
Python has a third party library called cx_Oracle, which helps you to work with Oracle databases. You can install it with pip or download it from The Cheese Shop.
cx_Oracle
In this article, we will look at a few basic operations like:
- connecting to a database
- executing DDL (Data Definition Language) statements like CREATE, ALTER
- executing DML (Data Manipulation Language) statements like INSERT, UPDATE, DELETE
- executing DQL (Data Query Language) statement i.e. SELECT
Connecting to a Database§
The connect() method of the cx_Oracle module takes a connection string which contain the database credentials in the format: user/password@host/instance. The method returns a Connection object if it succeeds in connecting. In my experience, it takes 10-15 seconds to connect.
>>> import cx_Oracle
>>> oracleConnection = cx_Oracle.connect('user/password@host/instance')
Executing DDL (Data Definition Language) statement: CREATE, ALTER§
Before executing any statements, you will have to obtain a Cursor object by calling the cursor() method of the Connection object. Once you have done that, you can use the execute() method of the Cursor object to execute statements. Be sure to close the cursor when you are done by calling cursor.close().
>>> import cx_Oracle
>>> oracleConnection = cx_Oracle.connect('user/password@host/instance')
#### DDL: CREATE ####
>>> oracleCursor = oracleConnection.cursor()
>>> oracleCursor.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR2(50) NOT NULL,
LAST_NAME VARCHAR2(50) NOT NULL)''')
#### DDL: ALTER ####
>>> oracleCursor.execute('ALTER TABLE EMPLOYEE ADD DOB DATE')
>>> oracleCursor.execute('ALTER TABLE EMPLOYEE DROP COLUMN DOB')
>>> oracleCursor.close()
Do NOT put a semi-colon at the end of the statementS (e.g. NOT NULL);'''), because if you do, then Python raises cx_Oracle.DatabaseError.
Executing DML (Data Manipulation Language) statements: INSERT, UPDATE, DELETE§
>>> import cx_Oracle
>>> oracleConnection = cx_Oracle.connect('user/password@host/instance')
>>> oracleCursor = oracleConnection.cursor()
>>> oracleCursor.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR2(50) NOT NULL,
LAST_NAME VARCHAR2(50) NOT NULL)''')
#### DML: INSERT ####
>>> oracleCursor.execute('''INSERT INTO EMPLOYEE
(EMP_ID, FIRST_NAME, LAST_NAME)
VALUES(1, 'Ethan', 'Hunt')''')
>>> oracleCursor.execute('COMMIT')
#### DML: UPDATE ####
>>> oracleCursor.execute('''UPDATE EMPLOYEE
SET EMP_ID=7
WHERE FIRST_NAME = 'Ethan'
''')
>>> oracleCursor.execute('COMMIT')
#### DML: DELETE ####
>>> oracleCursor.execute('COMMIT')
>>> oracleCursor.execute('''DELETE FROM EMPLOYEE
WHERE EMP_ID = 7''')
>>> oracleCursor.execute('COMMIT')
>>> oracleCursor.close()
Executing DQL (Data Query Language) statement: SELECT§
>>> import cx_Oracle
>>> oracleConnection = cx_Oracle.connect('user/password@host/instance')
>>> oracleCursor = oracleConnection.cursor()
>>> oracleCursor.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR2(50) NOT NULL,
LAST_NAME VARCHAR2(50) NOT NULL)''')
>>> oracleCursor.execute('''INSERT INTO EMPLOYEE
(EMP_ID, FIRST_NAME, LAST_NAME)
VALUES(1, 'Ethan', 'Hunt')''')
>>> oracleCursor.execute('''INSERT INTO EMPLOYEE
(EMP_ID, FIRST_NAME, LAST_NAME)
VALUES(2, 'Tyrion', 'Lannister')''')
>>> oracleCursor.execute('COMMIT')
#### DQL: SELECT ####
>>> resultSet = oracleCursor.execute('SELECT * FROM EMPLOYEE')
>>> for record in resultSet:
print(record)
(1, 'Ethan', 'Hunt')
(2, 'Tyrion', 'Lannister')
>>> oracleCursor.close()
That’s it for this one! This article only scratches the surface of what you can do with cx_Oracle. Having said that, I hope it will help you get started. Cheers!
See also:
- Python 101
- Object Oriented Python
- Design Patterns in Python
- 50+ Handy Standard Library Modules
- 60+ Handy Third Library Modules
- 50+ Tips & Tricks for Python Developers
- 50+ Know-How(s) Every Pythonista Must Know