oracledba.help
Legacy

Python 3.4

<- Legacy

Legacy Docs

Overview

This Quickstart covers using Python to create applications that can use Oracle. According to nearly all of the language tracking indices Python is one of the fastest growing and mature computer languages. It is also clearly the language to learn at this time.

There are some really good reasons to use Python:

  1. Python is fast, simple, powerful and scalable.
  2. Python works really well with databases especially Oracle.
  3. On average, Python scripts take 20% less code than other scripting languages and are still more readable!
  4. Because of the above Python has become one of the top enterprise scripting languages.

There is a plethora of draconian information on Python and tkinter. tkinter has been revamped and integrated with Python. Many may be surprised to find out that tkinter is the simplest most powerful GUI API for standard Python applications at this point.

When using Google preface your searches with the latest versions to get the most useful results. Example: "python 3" "tkinter 8.5"

Prerequisites

  • Download software from Python.org.
    Example file name of install: python-3.4.1.amd64.msi
  • Download the Oracle extension module matching your Python and Oracle version.
    Example file: cx_Oracle-5.1.3-12c.win-amd64-py3.4.exe
  • Create directory for your applications.
    Example: C:\app\py

Useful Links

Python Installation

Linux (RHEL)

As root:

  1. Install package with repository for your system.
    $ yum install centos-release-scl
  2. Enable RHSCL repository for you system.
    $ yum-config-manager --enable rhel-server-rhscl-7-rpms
  3. Install the python collection.
    $ yum install rh-python35
  4. Start using software collections.
    $ scl enable rh-python35 bash

Windows

Run python-3.4.1.amd64.msi:

  1. (x) Install for all users.
  2. Destination Directory: C:\Python34 (use default)
  3. Customize Accept defaults and add: Add python.exe to Path
    Installation runs...
  4. Finish

Test Python

From a command prompt window run: python -V

Python 3.4.1

If you dont see the above on a Windows system. Re-save the PATH variable. In some Windows environments it does not immediately detect the change to the PATH as added from the installer.

Install Python Extensions for Windows (AKA Win32 Extension)

This extension is not essential but incredibly useful. Should be part of your baseline in Windows environments.

  1. Run installation .exe.
    Example: pywin32-219.win-amd64-py3.4.exe
  2. Location
    - Python Directory: C:\Python34 (default)
    - Installation Directory: C:\Python34\Lib\site-packages\ (default)
  3. Finish

If fields not filled in with defaults you most likely are trying to install wrong version.

Create a Test Application

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

Install Oracle Extension Module

  1. Run installation .exe.
    Example: cx_Oracle-5.1.3-12c.win-amd64-py3.4.exe
  2. Location
    - Python Directory: C:\Python34 (default)
    - Installation Directory: C:\Python34\Lib\site-packages\ (default)
  3. Finish

If fields not filled in with defaults you most likely are trying to install wrong version.

Test Oracle

 import os
 import cx_Oracle

 # If IC On Same System as Database
 #os.putenv('ORACLE_HOME','C:\app\oracle\product\12.1.0\ic32')
 #os.putenv('ORACLE_SID','DB01')
 #os.putenv('TNS_ADMIN','C:\app\oracle\product\11.2.0.3\dbhome_1\\NETWORK\ADMIN')
 #Note the two(2) back slashes before \\NETWORK!

 # Connect
 print('Connecting...\n')
 connstr  = 'scott/tiger@localhost:1521/DB01'
 connect  = cx_Oracle.connect(connstr)
 #connect = cx_Oracle.connect(connstr,mode=cx_Oracle.SYSDBA)

 # 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')

Run Within Notepad++

-- Create Run Item
Press F6 then enter the below matching your environment. 

 NPP_SAVE
 cd "$(CURRENT_DIRECTORY)"
 python.exe $(NAME_PART).py

Save as: Python Run

-- Create NppExec Item
Go to Advance Options within NppExec plugin, 

 A. Check the box at the top that says "Place to the Macros Submenu"
 B. Select script from "Associated Script" combo box. It will automatically 
    fill in the "Item Name".
 C. Now click the "Add/Modify" button.
 D. Click OK. This will exit the Advanced Options box and say that 
    NotePad++ needs to be restarted.

-- Assign to HotKey 

 A. Navigate to: Settings -> Shortcut Mapper -> Plugin Commands -> Python Run
 B. Right-Click -> Modify
 C. Select Ctrl-F1 (or other combo you desire)

Notepad++ If Using Instant Client on DB Server

 NPP_SAVE
 ENV_SET PATH = $(SYS.PATH);C:\Python34;C:\app\oracle\product\12.1.0.1\ic32;
 ENV_SET ORACLE_SID=DB01
 ENV_SET ORACLE_HOME=c:\app\oracle\product\12.1.0.1\ic32;
 ENV_SET TNS_ADMIN=C:\app\oracle\product\11.2.0.3\dbhome_1\NETWORK\ADMIN;

 cd "$(CURRENT_DIRECTORY)"
 python.exe $(NAME_PART).py

With Notepad++ you can create a comment block highlighting a range and using: 

 Ctl-K       = Comment Block
 Ctl-Shift-K =  UN-comment Comment Block

Syntax Code Convention

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.

Symbolic Links Note

You can use symlinks if you have modules in a particular path and dont want to use an environment var or hard code an import path to access them. This is useful testing different versions of libs\modules.

Say you have the file c:\app\py\lib\utl.py and you want to link to it from here (c:\app\py\dev\myapp\utl.py) to access it as if it is local to that directory.

  1. From console window change to local dir.
    cd c:\app\py\dev\myapp
  2. Issue mklink command.
C:\app\py\dev\myapp> mklink utl.py c:\app\py\lib\utl.py
symbolic link created for utl.py <<===>> c:\app\py\lib\utl.py

Creating a Stand-Alone Windows Version using cx_freeze

You may need MS Visual C++ 2010 Redist Pkg if not already installed.
Usage:

  1. Install cx_freeze Python extension.
  2. Create setup.py as shown below in the same folder as your script(s).
  3. Change to your script folder.
  4. Create build folder: python setup.py build
  5. Copy the contents of you build folder to your destination system. 
import sys
from cx_Freeze import setup, Executable

sBase = None
aIncludeFiles = ["favicon.ico"]

if sys.platform == "win32":
   sBase = "Win32GUI"

aExecParams = [Executable('MyAppName.py',base=sBase)]

setup(name="MyAppName",
      version="2.1",
      description="My GUI application!",
      executables=aExecParams,
      options = {'build_exe': {'include_files':aIncludeFiles} }
      )

If your application has any print or flush commands you may get a console window.

<- Legacy