In this series, we’re going to take a look at performing CRUD (Create Retrieve Update Delete) operations using the cx_Oracle driver.
A good ORM will handle most of your needs.
An ORM tool can handle many of the repetitive processes when interfacing with a database. Your project might call for an ORM tool such as SQLAlchemy or Pony. No doubt about it, a good ORM can come in very handy. An ORM application will typically have a function for passing in raw SQL if needed, so you may not need to go straight to the driver.
Why learn to use the driver directly?
An ORM brings its own, different complexity to a project. It may be overkill for some projects. There are also times when a specific task is just different enough that an ORM may not be able to help, or its application to your requirements become so complex that your code becomes difficult to maintain.
And if you’re like me, its hard to be satisfied with a black box approach. You want to know more about how your tools work and you want to have options, just in case.
Martin Fowler said “Mapping to a relational database involves lots of repetitive, boiler-plate code. A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%.”
When you have enough knowledge to implement direct CRUD operations, you are in a better position to choose the right tool for the right job.
Common Setup
All examples in this series will use the same database objects and connection information.
Creating the Database Objects
The following can be used to setup the initial tables we’ll use. Please make sure you’re connected to a schema in which you can safely execute commands like these.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
CREATE TABLE cx_people ( id NUMBER GENERATED BY DEFAULT AS identity, name VARCHAR2(20), age NUMBER, notes VARCHAR2(100) ) / ALTER TABLE CX_PEOPLE ADD CONSTRAINT PK_CX_PEOPLE PRIMARY KEY ("ID") / CREATE TABLE CX_PETS ( id NUMBER GENERATED BY DEFAULT AS IDENTITY, name VARCHAR2(20), owner NUMBER, type VARCHAR2(100) ) / ALTER TABLE CX_PETS ADD CONSTRAINT PK_CX_PETS PRIMARY KEY ("ID") / ALTER TABLE CX_PETS ADD CONSTRAINT FK_CX_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "CX_PEOPLE" ("ID") / INSERT INTO cx_people (name, age, notes) VALUES ('Bob', 35, 'I like dogs') / INSERT INTO cx_people (name, age, notes) VALUES ('Kim', 27, 'I like birds') / INSERT INTO cx_pets (name, owner, type) VALUES ('Duke', 1, 'dog') / INSERT INTO cx_pets (name, owner, type) VALUES ('Pepe', 2, 'bird') / COMMIT / |
Making the Connection
- Import the cx_Oracle driver.
- Import os module used to read the environment variable.
- Get the connection string from the environment variable.
- Create the connection object.
|
1 2 3 4 |
import cx_Oracle import os connectString = os.getenv('db_connect') con = cx_Oracle.connect(connectString) |
We will include this code section with all examples and use the “con” connection object “con” throughout the series.
Cleanup
To clean up the database when you are finished with the series, you just need to drop the two tables. Please make sure you’re connected to the correct schema where you created the tables.
|
1 2 3 4 5 |
drop table CX_PETS / drop table CX_PEOPLE / |
Guide to cx_Oracle CRUD Series
In this series, we will learn how to use cx_Oracle connections to create cursor objects that you can use to run SQL (DDL / DML) and PL/SQL procedures and functions in the Oracle Database.
Initial Setup
Create records
Retrieve records
Update records
Delete records
Nice work! My only comment is that in addition to the warning about choosing a safe schema, you may want to add “alter session set current_schema = scott;”, just in case someone does a copy & paste of example code.
Hi Blaine,
i am trying to access oracle 11g DB using cx_Oracle module in python. your tutorial is very much helpful to start in this area, but i am getting “cx_Oracle.DatabaseError: ORA-21561: OID generation failed” . I am using python 2.7.10, MacOSX El-cap. cx_oracle installations went fine (with instantclient 12.1 basic,sdk and cx_Oracle 5.2.1) but i am stuck here. How ever i have tried the same in java with simple JDBC it is working fine.
Thanks in advance
Howdy Karun,
Give this a try. http://stackoverflow.com/a/37150739/1296150
Let me know if that helps or not.
Thanks
Hey I want to Display the description of all columns of the table .
How do I go about making it happen
You could run a quick query such as
select * from all_tab_columnswhere table_name = 'MY_COOL_TABLE';