oracledba.help
QuickStarts

Python

<- QuickStarts

Overview

This Quickstart covers the essential steps to use Python to create applications that can use Oracle.

Prerequisites

  • Downloaded Python from Python.org.
    Example: python-3.10.0-amd64.exe
  • Create directory for your apps.
    Example: C:\app\py

Ensure File Extensions Visible
Windows-Key 🠊 File Explorer 🠊 View 🠊 [x] File name extensions

Oracle Instant Client (IC) Installation

 1. Download Oracle's IC zip file to your software directory (Ex: C:\sw).
    a. Go to Oracle's download page.
    b. Select: Instant Client for Microsoft Windows (x64)
    c. Select the latest Basic Package 64-bit download link.
       Ex: instantclient-basic-windows.x64-19.6.0.0.0dbru.zip

 2. Extract\unzip the IC file.
    Example sub dirs created: ...\instantclient-basic-windows.x64-19.6.0.0.0dbru\instantclient_19_6

 3. Create dir for the 64-bit Instant Client to run from and copy unzipped files to it.
    Ex dir to create: C:\app\oracle\ic64

    Copy unzipped files from:
    C:\SW\instantclient-basic-windows.x64-19.6.0.0.0dbru\instantclient_19_6 to
    C:\app\oracle\ic64
    Just copy files from sub dir instantclient_19_6 (including the vc14 sub dir) to C:\app\oracle\ic64.

 4. Add IC to PATH Environment Variable
    C:\app\oracle\ic64
    Shortcut to Edit Env Variables:  Windows Key 🠊 type: env

Python Installation

Run: Python <InstallFileName>.exe

 1. Select: [x] Add Python 3.10 to PATH
 2. Select: Install Now
    Process runs...
    When completed you should see: Setup was successful.
 3. Select: Disable path length limit.
 4. Select: [Close]

PATH example if not added automatically: C:\Users\Administrator\AppData\Local\Programs\Python\Python310

Validate Python Working

From a new command prompt window run:

 python -V
 Python 3.10.0

Create Test Application

  1. Go to your Python scripts directory (c:\app\python).
  2. Create a file with the contents shown below as: c:\app\python\hello.py.
    print("Hello World!")
  3. From a DOS console run it by entering:
    python hello.py
  4. You should see the famous Hello World! message displayed.

cx_Oracle Extension Installation

Per the cx_Oracle Installation docs:

 1. Run: python -m pip install --upgrade pip
    Installing collected packages: pip
       Found existing installation: pip nn.n.n
          Uninstalling pip-nn.n.n:
             Successfully uninstalled pip-nn.n.n
       Successfully installed pip-21.3.1

 2. Run: python -m pip install cx_Oracle --upgrade
    Installing collected packages: cx-Oracle
       Successfully installed cx-Oracle-8.3.0

Test Oracle

Create and run a file with the contents shown below as: c:\app\py\oratest.py.

 import cx_Oracle

 # Connect
 print('Connecting...\n')
 connstr  = 'scott/tiger@myhostname:1521/MY_DB_NAME'
 connect  = cx_Oracle.connect(connstr)

 # Cursor
 sql     = 'SELECT table_name FROM dba_tables ORDER BY table_name'
 curs    = connect.cursor()
 curs.execute(sql)

 # Retrieve Just One Value
 for row in curs.fetchone():
    print(row[0])

 # Retrieve Multiple Values
 rows = curs.fetchall()
 for row in rows: 
    print(row[0])

 # End
 curs.close()
 connect.close()
 print('*** Program Ended ***\n')
  • Change connection values to match your environment.
  • You may have to remove spaces at beginning of line and re-tab the indents.

Code Conventions (Suggested)

Most Python variables can be summarized as:

  • Strings
  • Numbers
  • Boolean
  • Arrays (AKA Lists) and
  • Instances of Objects (AKA classes).
 - Variables: Proper case with prefix.
   Examples:
      aStates = ['FL','NY','TX']
      nCount  = 42
      bActive = False
      sConn   = 'scott/tiger@localhost:1521/DB01'
      oConn   = cx_Oracle.connect(sConn)
      sSqlCmd = 'SELECT table_name FROM dba_tables ORDER BY table_name'
      oCursor = oConn.cursor()
      oCursor.execute(sSqlCmd)

      Note: Using an 'o' prefix for objects allows you to easily 
            distinguish between native Python names and yours.

 - GLOBAL variables: UPPER_CASE_WITH_UNDERSCORES
   Example: aSCHEMA

 - Classes: oMixedCase
   oEmail.Server = 10.10.10.1 # Property  (Proper Case)
   oEmail.send                # Method    (lower case)
   SomeClass.doSomething      # Complex Method Format: lowerProper 
                                       e.g. actionNoun

 Note: Some Python developers prefer variable names  
       just be a lower case name with underscores.

References

Useful Links

For SYS user you can connect using:

 connect = cx_Oracle.connect(connstr,mode=cx_Oracle.SYSDBA)

<- QuickStarts