Python
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
- Go to your Python scripts directory (c:\app\python).
- Create a file with the contents shown below as: c:\app\python\hello.py.
print("Hello World!")
- From a DOS console run it by entering:
python hello.py
- 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 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.
Code Conventions (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
- https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html
- https://cx-oracle.readthedocs.io/en/latest/api_manual/module.html#cx_Oracle.clientversion
Useful Links
- Python 3.x: Docs | Functions | Oracle Best Practices
- tkinter 8.5: Docs|Cmds|Library|Ref|Cfg|Wikipedia|examples
- Extensions: Create Windows .exe (cx-freeze) | PSUtil | tkRAD | Win32
- Tools: Py GUI Builder | Nullege.com | Django (Web via Python)
For SYS user you can connect using:
connect = cx_Oracle.connect(connstr,mode=cx_Oracle.SYSDBA)