Python - Database Connection

Whenever we want to communicate with a Database from a Python program, we should follow a standard sequence of steps.

These steps form the basic procedure of Python Database Programming. According to the source document, there are 7 standard steps.

Python Program
     │
     ▼
Import Database Module
     │
     ▼
Establish Connection
     │
     ▼
Create Cursor
     │
     ▼
Execute SQL Queries
     │
     ▼
Commit / Rollback
     │
     ▼
Fetch Results
     │
     ▼
Close Resources

The seven standard steps are:

1. Import the database-specific module
2. Establish the connection
3. Create a Cursor object
4. Execute SQL queries
5. Commit or Rollback changes
6. Fetch the results
7. Close the resources

Step	Operation	Main Method / Statement
1	Import database-specific module	import cx_Oracle
2	Establish connection	connect()
3	Create Cursor object	cursor()
4	Execute SQL queries	execute(), executescript(), executemany()
5	Save or cancel changes	commit(), rollback()
6	Fetch SELECT results	fetchone(), fetchall(), fetchmany(n)
7	Close resources	close()

The first step is to import the module required for the Database we want to use. For example, the document uses the following module for Oracle Database:

import cx_Oracle

The database-specific module acts as a bridge between the Python program and the Database.

Python Program
      │
      ▼
Database-Specific Module
      │
      ▼
Database

Python needs an appropriate module or driver to communicate with a particular Database. The module provides the functions and classes required to:

• Connect to the Database
• Create Cursor objects
• Execute SQL statements
• Fetch results
• Manage transactions
• Close database resources

After importing the database-specific module, the next step is to establish a connection between the Python program and the Database.

A Connection object can be created by using the connect() function.

Syntax

con = cx_Oracle.connect(database_information)

Example

con = cx_Oracle.connect('scott/tiger@localhost')

The connection information used in the example is:

Part	Value
Username	scott
Password	tiger
Database / Host information	localhost

scott / tiger @ localhost
  │      │         │
  │      │         └── Database connection information
  │      │
  │      └── Password
  │
  └── Username

The object returned by cx_Oracle.connect(...) is called a Connection object. In the document, it is stored in con.

The Connection object is important because it is used for operations such as:

• Creating a Cursor object
• Committing transactions
• Rolling back transactions
• Closing the Database connection

              con
               │
       ┌───────┼───────┐
       │       │       │
       ▼       ▼       ▼
    cursor() commit() rollback()
       │
       └──────────┐
                  ▼
               close()

To execute SQL queries and hold their results, we require a special object called a Cursor object.

A Cursor object can be created by using the cursor() method of the Connection object.

Syntax

cursor = con.cursor()

A Cursor object is used to execute SQL statements and work with query results.

Connection Object
      │
      ▼
con.cursor()
      │
      ▼
Cursor Object
      │
      ▼
Execute SQL

After creating the Cursor object, we can use it to execute SQL queries.

cursor.execute(...)

Connection Object	Cursor Object
Represents the connection with the Database	Used for executing SQL statements
Created using connect()	Created using cursor()
Example variable: con	Example variable: cursor
Used for commit() and rollback()	Used for execute() and fetching results
Closed using con.close()	Closed using cursor.close()

After creating the Cursor object, we can execute SQL queries. SQL queries are executed by using methods of the Cursor object.

The document lists the following three important methods:

1. execute()
2. executescript()
3. executemany()

Cursor Object
     │
     ├── execute()
     │
     ├── executescript()
     │
     └── executemany()

The execute() method is used to execute a single SQL query.

Syntax

cursor.execute(sqlquery)

Example

cursor.execute("select * from employees")

This statement executes select * from employees against the connected Database.

Cursor
  │
  ▼
execute()
  │
  ▼
Single SQL Query
  │
  ▼
Database

executescript() is a method used to execute a string containing multiple SQL queries separated by a semicolon.

Syntax

cursor.executescript(sqlqueries)

Conceptually, SQL Query 1; SQL Query 2; SQL Query 3; can be provided as a SQL script when the particular database interface supports this method.

The executemany() method is used to execute a parameterized query for multiple sets of values.

One Parameterized SQL Query
            │
            ▼
     Multiple Records
            │
            ▼
      executemany()
            │
            ▼
        Database

Later in this chapter, we will use executemany() for inserting multiple employee records.

Comparison of the Three Methods

Method	Purpose
execute()	Executes a single SQL query
executescript()	Executes a script/string containing multiple SQL queries separated by semicolons, when supported
executemany()	Executes a parameterized SQL operation for multiple sets of values

The next step is transaction management. This step is especially required for DML — Data Manipulation Language operations such as:

• INSERT
• UPDATE
• DELETE

Two important methods are commit() and rollback().

commit()

The commit() method saves the changes permanently to the Database.

con.commit()

rollback()

The rollback() method rolls back the temporary changes in the current transaction.

con.rollback()

commit() vs rollback()

commit()	rollback()
Saves transaction changes	Cancels uncommitted transaction changes
Makes changes permanent	Rolls changes back
Used after successful DML operations	Commonly used when a transaction must be cancelled, such as after an error
con.commit()	con.rollback()

DML Transaction Flow

          DML Query
             │
             ▼
     INSERT / UPDATE / DELETE
             │
             ▼
       Execute Query
             │
             ▼
       Was it successful?
          /                Yes         No
         │           │
         ▼           ▼
     commit()    rollback()
         │           │
         ▼           ▼
    Save Changes   Cancel
                  Uncommitted
                   Changes

Fetching results is required for SELECT queries. After executing a SELECT statement, the Cursor object can be used to retrieve the returned rows.

The document lists three important fetch methods:

1. fetchone()
2. fetchall()
3. fetchmany(n)

SELECT Query
     │
     ▼
Cursor Result
     │
     ├── fetchone()
     ├── fetchall()
     └── fetchmany(n)

fetchone()

The fetchone() method fetches only one row from the result set. The fetched row is stored in data and print(data) displays the row.

data = cursor.fetchone()
print(data)

fetchall()

The fetchall() method fetches all remaining rows from the query result. It returns a list of rows, and a for loop is then used to process every row.

data = cursor.fetchall()

for row in data:
    print(row)

fetchmany(n)

The fetchmany(n) method fetches up to the specified number of rows from the result set. For example, cursor.fetchmany(3) requests up to three rows from the current result-set position.

cursor.fetchmany(3)

Comparison of Fetch Methods

Method	Purpose
fetchone()	Fetches one row
fetchall()	Fetches all remaining rows
fetchmany(n)	Fetches up to n rows

SELECT Result
     │
     ├── Need one row?
     │       └── fetchone()
     │
     ├── Need all rows?
     │       └── fetchall()
     │
     └── Need a limited batch?
             └── fetchmany(n)

DML queries — INSERT, UPDATE, DELETE — normally require transaction handling using commit() or rollback(), while SELECT queries use the fetch methods to obtain the returned rows.

After completing all Database operations, it is highly recommended to close the resources.

Resources should be closed in the reverse order of their opening. We created the Connection first, then the Cursor. Therefore, while closing, we close the Cursor first and then the Connection.

Opening Order	Closing Order
1. Connection	1. Cursor
2. Cursor	2. Connection

The Cursor depends on the Connection, so cleanup is performed in reverse order.

Cursor
    │
    ▼
Close Cursor
    │
    ▼
Close Connection

Close Cursor

cursor.close()

Close Connection

con.close()

The following program combines the main steps required for a simple SELECT operation. No commit() is shown here because this conceptual example only performs a SELECT operation.

No output captured.

For INSERT, UPDATE and DELETE operations, transaction management using commit() or rollback() becomes important. This demonstrates the difference between a SELECT operation and a DML operation.

No output captured.

Before a Python program can communicate with an Oracle Database, we require a special software component called a Driver or Connector.

A Python program and an Oracle Database use different interfaces for communication. Therefore, an intermediate component is required to translate the communication between them.

Python Program
      │
      ▼
Driver / Connector
      │
      ▼
Oracle Database

According to the document, the Driver required for Oracle Database is cx_Oracle.

cx_Oracle is a Python extension module that enables Python programs to access Oracle Database. It provides the functionality required to establish a connection with Oracle Database, create Cursor objects, execute SQL statements, fetch records, manage transactions, and close database resources.

Python
   │
   ▼
cx_Oracle
   │
   ▼
Oracle Database

Features of cx_Oracle

Feature	Description
Module	cx_Oracle
Type	Python Extension Module
Purpose	Access Oracle Database from Python
Python Versions in Source	Python 2 and Python 3
Oracle Versions in Source	9, 10, 11 and 12

Note: These Python and Oracle versions reflect the source document's original environment and should be understood as historical tutorial information.

Modern Note

The chapter uses cx_Oracle because that was the Oracle Python driver used when the original material was prepared. For modern Python projects, Oracle's current Python driver is generally known as python-oracledb, imported as import oracledb. We will continue using cx_Oracle in this tutorial wherever required to preserve the programs and examples from the source document.

Before using cx_Oracle, it must be installed in the Python environment. The document instructs us to install it from the Normal Command Prompt — it should not be typed inside the Python Console.

Installation Command

pip install cx_Oracle

Command Prompt (Correct)

D:\python_classes>pip install cx_Oracle

Python Console (Not Correct)

>>> pip install cx_Oracle

pip install ... is a command-line installation command, not normal Python source code.

Installation Output

The document shows installation output similar to the following:

Collecting cx_Oracle
Downloading cx_Oracle-6.0.2-cp36-cp36m-win32.whl (100kB)
100% |-----------| 102kB 256kB/s
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-6.0.2

The important success message is Successfully installed cx-Oracle-6.0.2.

Understanding the Wheel File Name

The downloaded package file is cx_Oracle-6.0.2-cp36-cp36m-win32.whl:

Part	Meaning in the Source Environment
cx_Oracle	Package name
6.0.2	Package version
cp36	CPython 3.6 tag
win32	32-bit Windows platform tag
.whl	Python wheel package

You do not need to manually type this filename when using the normal pip install cx_Oracle command.

After installation, we should verify whether the module is available in Python. The source document uses help("modules") for verification.

Open the Python Console and execute:

>>> help("modules")

Python displays the available modules. We should check whether cx_Oracle appears in that list.

Expected Output

The output contains a large list of modules. A partial representation of the output shown in the document is:

csv
ctypes
cx_Oracle
data
datetime
dbm
decimal
difflib
dis
distutils
doctest
...

The important entry for this tutorial is cx_Oracle. If cx_Oracle appears in the output of help("modules"), then the module is available to that Python environment.

Alternative Simple Import Test

For practical testing, we can also try importing the module:

>>> import cx_Oracle
>>>

If Python immediately returns to the prompt without an import error, the module was found.

If cx_Oracle is Not Available

If Python cannot find the module, an error similar to the following may occur:

ModuleNotFoundError: No module named 'cx_Oracle'

This usually means that the package is not available in the Python environment currently running the program. Therefore, installation and program execution should use the intended Python environment.

After installing the cx_Oracle driver successfully, the first Oracle Database application is to connect a Python program with Oracle Database and print the Oracle Database version.

This is a simple program, but it demonstrates the most basic database operation: establishing a connection. It demonstrates how to import the Oracle Database module, establish a connection, store the Connection object, access the Oracle Database version, print it, and close the connection.

The complete program given in the document is:

D:python_classes>py db1.py
11.2.0.2.0

The program contains only four main statements:

import cx_Oracle

con = cx_Oracle.connect('scott/tiger@localhost')

print(con.version)

con.close()

Line-by-Line Explanation

Line	Statement	Purpose
1	import cx_Oracle	Imports the Oracle Database module
2	con = cx_Oracle.connect('scott/tiger@localhost')	Establishes the Oracle Database connection
3	print(con.version)	Displays the connected Oracle Database version
4	con.close()	Closes the Database connection

Connection Details

The source program uses the connection information scott/tiger@localhost. Here, scott is the username, tiger is the password, and localhost is the Database / connection location, which refers to the local computer in this tutorial setup.

con.version

The version attribute of the Connection object returns the version of the connected Oracle Database. In con.version, con is the Oracle Connection object, . is the member-access operator, and version is the attribute containing the connected Oracle Database version. According to the document, the output is 11.2.0.2.0.

Why is no Cursor Required in This Program?

In the standard Database Programming steps, we learned about creating a Cursor object. But this program does not execute any SQL statement — it only accesses an attribute of the Connection object (con.version). Therefore, no Cursor object is required for this particular program.

Why is commit() Not Required?

This program does not perform any DML operation such as INSERT, UPDATE, or DELETE. It only connects to Oracle and reads the Database version. Therefore, con.commit() is not required.

Closing the Connection

Closing the connection releases the Database resource associated with that connection. Always close the connection using close() after completing Database operations.

The complete program with comments is:

# Import Oracle Database module
import cx_Oracle

# Establish connection with Oracle Database
con = cx_Oracle.connect('scott/tiger@localhost')

# Print Oracle Database version
print(con.version)

# Close the Database connection
con.close()