oracledba.help

Python

<- QuickStarts

Overview

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

Prerequisites

  • Install Microsoft Windows Redistributables (AKA Visual C Redistributable) for VS 2010.
    Example: vcredist_x64.exe
  • Downloaded Python from Python.org.
    Example: python-3.8.3-amd64.exe
  • Create directory for your apps.
    Example: C:\app\py

Ensure This is Done

Set windows system to show file extensions:
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-3.8.3-amd64.exe 

 1. Select: Custom installation

 2. Optional Features (accept defaults)

 3. Advanced Options
      Enable these:
         [x] Install for all users
         [x] Associate files with Python
         [x] Create shortcuts for installed applications
         [x] Add Python to environment variables
         [x] Precompile standard library

       Customize install location: C:\Program Files\Python38 
       Select: [Install]
       Process runs...

       When completed you should see: Setup was successful.

       Select: Disable path length limit.
       Select: [Close]

Validate Python Working

From a new command prompt window run: python -V

Python 3.8.3

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 https://oracle.github.io/python-cx_Oracle -> Quick Start cx_Oracle Installation: 

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

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

Test Oracle

Create and run a file with the contents shown below as: c:\app\python\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.

Syntax Code Convention (Suggestion)

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.

 - 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: Many Python developers simply 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