A Framework for Managing Imports through External Tables

A Framework for Managing Imports through External Tables

Python & Oracle By Michael McLaughlin Python cx_Oracle Tutorial cx_Oracle Documentation Objectives Introduce Python

Learn how to install cx_Oracle Learn how to connect to Oracle Learn how to run static SQL statements Learn how to run dynamic SQL statements Learn how to call PL/SQL store programs Introduce Python Python? A robust high-level programming language: Gets complex things done quickly Automate system and data integration tasks Solve complex analytical problems

Install cx_Oracle Driver The cx_Oracle Driver Conforms to the Python DB API 2.0 Requires Oracle Client Installation Not presently in standard Linux repos You can download from here: https://pypi.python.org/pypi/cx_Oracle

Installing cx_Oracle Driver Things you need to know first: Ships for Linux/Unix in RPM file Ships for Windows in MS Windows Installer file Available for Python 2.6/2.7 on Linux/Unix Available for Python 2.6/2.7/3.4-3.6 on Windows Requires local Oracle Instant Client insall Supports platforms: Linux, Unix, Mac OS X, and Windows Installation Steps (as root) Check your Python version with:

# python -V Check for Oracle Client installation: # rpm qa oracle-instantclient11-2-basic Check for Oracle Client installation: # yum install -y /tmp/oracle-instantclient* Install cx_Oracle Driver: # yum install -y cx_Oracle-5* Verify cx_Oracle Installation Connect to the Python IDLE environment Type in the following commands:

>>> import cx_Oracle >>> db = cx_Oracle.connect("student/[email protected]") >>> print db.version Returns the following for Oracle 11g XE: 11.2.0.2.0 Connect to Oracle Implement a connect.py script #!/usr/bin/python # Import the Oracle library. import cx_Oracle, sys try:

# Create a connection. db = cx_Oracle.connect("student/[email protected]") # Print a message. print "Connected to the Oracle " + db.version + " database." except cx_Oracle.DatabaseError, e: error, = e.args print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message finally # Close connection. db.close(); Run a static SQL Statement

Implement a static query #!/usr/bin/python ... try: # Create a connection. db = cx_Oracle.connect("student/[email protected]") # Create a cursor. cursor = db.cursor() # Execute a query. cursor.execute("SELECT 'Hello world!' FROM dual") # Read the contents of the cursor. for row in cursor: print (row[0]) ...

finally: # Close cursor and connection. cursor.close() db.close() Returning rows from a cursor #!/usr/bin/python ... # Execute a query. cursor.execute("SELECT ", "FROM "WHERE "

" " item_title " + item_rating " + item " + item_type = (SELECT common_lookup_id " + FROM common_lookup " + WHERE common_lookup_type = 'DVD_WIDE_SCREEN')") # Read the contents of the cursor. for row in cursor:

print (row[0], row[1]) ... Run a dynamic SQL Statement Binding a value in a cursor The local variable. #!/usr/bin/python ... sRate = 'PG-13' ... # Define a dynamic statment. stmt = "SELECT item_title, item_rating " + \ "FROM item WHERE item_rating = :rating

# Create a cursor. The bind variable. cursor = db.cursor() # Execute a statement with a bind variable. cursor.execute(stmt, rating = sRate) # Read the contents of the cursor. for row in cursor: print (row[0], row[1]) Name and value ... pair assignment. Binding two values in a cursor The list of local

variables. #!/usr/bin/python ... The bind variables. # Define a list. dvd = ('DVD_FULL_SCREEN','DVD_WIDE_SCREEN')| ... stmt = "SELECT common_lookup_id" + "\n" + \ "FROM common_lookup" + "\n" + \ "WHERE common_lookup_table = 'ITEM'"

+ "\n" + \ "AND common_lookup_column = 'ITEM_TYPE'" + "\n" + \ "AND common_lookup_type IN (:x,:y) # Parse the statement by replacing line returns with a single # whitespace, replacing multiple whitespaces with single spaces. stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r','')) # Declare a dynamic statement with a sequence. cursor.execute(stmt, x = dvd[0], y = dvd[1]) ... Name and value Name and value

pair assignment. Binding a dynamic dictionary A tuple used to name dictionary names, or the names of namevalue pairs. #!/usr/bin/python ... # Define an alphabetic indexing tuple. ind = tuple('abcdefghijklmnopqrstuvwxyz') # Define a parameter list and empty target list. typ = ('DVD_FULL_SCREEN','DVD_WIDE_SCREEN','BLU-RAY') mat = {} ...

Declaring an empty dictionary. A dynamic list of variables that need to bound to a dynamic SQL statement Binding a dynamic dictionary Beginning of list of values. ... # Define a dynamic query. stmt = "SELECT common_lookup_id" + "\n" + \

... "AND common_lookup_column = 'ITEM_TYPE'" + "\n" + \ "AND common_lookup_type IN (" # Build dictionary and append dynamic bind list to statement. for j, e in enumerate(typ): mat[str(ind[j])] = typ[j] if j == len(typ) - 1: stmt = stmt + ":" + str(ind[j]) Logic to build list of else: bind variables and stmt = stmt + ":" + str(ind[j]) + ", "

dynamic dictionary. # Close lookup value set. stmt = stmt + ")" + "\n" \ "ORDER BY 1 ... Append closing parenthesis of list. Dynamic statement and dictionary Generated dynamic statement: AND common_lookup_column = 'ITEM_TYPE' AND common_lookup_type IN (:a, :b, :c)

ORDER BY 1 Parse the query for line returns: stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r','')) Generated mat dictionary: {'a': 'DVD_FULL_SCREEN', 'c': 'BLU-RAY', 'b': 'DVD_WIDE_SCREEN'} Call the query with a dictionary: cursor.execute(stmt, mat) Binding a transaction ... # Declare variables.

sItemTitle = 'Star Trek Beyond sItemSubtitle = 'Extended Edition' ... # Execute a query. stmt = "UPDATE item" + "\n" + \ "SET item_subtitle = :bItemSubtitle" + "\n" + \ "WHERE item_title = :bItemTitle" ... # Declare a dynamic statement. cursor.execute(stmt, bItemTitle = sItemTitle , bItemSubtitle = sItemSubtitle )

# Commit the inserted value. db.commit() ... Assign local variables Commit change. to bind targets. Transaction Control You start a transaction like: # Set a starting transaction point. db.begin()

Commit the write in the try-block: db.commit() Rollback in the except block with an error: db.rollback() Call PL/SQL stored programs Calling a procedure ... # Create a connection. db = cx_Oracle.connect("student/[email protected]") First call parameter # Create a cursor.

is the name of the cursor = db.cursor() procedure. # Call a stored procedure. cursor.callproc( 'insert_bill_detail' \ , ( sBillNumber \ Second call parameter is , sBillText \ the list of actual , sDetailNumber \ parameters. , sDetailText ))

... Calling a procedure with list A parameter list. ... # Create a sequence for a procedure call. param = (sBillNumber, sBillText, sDetailNumber, sDetailText) try: # Create a connection. db = cx_Oracle.connect("student/[email protected] # Create a cursor. cursor = db.cursor() # Call a stored procedure. cursor.callproc( 'insert_bill_detail', param)

... Call parameters bundled in a list. Calling a function with list ... # Create a sequence for a procedure call. param = (sBillNumber, sBillText, sDetailNumber, sDetailText) try: # Create a connection. Function return type. db = cx_Oracle.connect("student/[email protected]") # Create a cursor.

cursor = db.cursor() # Call a stored procedure. fRetVal = cursor.callfunc( 'insert_bill_detail_func', cx_Oracle.NUMBER, param) # Check for successful function call or failure number. if fRetVal == 0: print "Success else: print "Failure [" + str(int(fRetVal)) + "] ... Capture a failure code from the function. Review

Introduce Python Learn how to install cx_Oracle Learn how to connect to Oracle Learn how to run static SQL statements Learn how to run dynamic SQL statements Learn how to call PL/SQL store programs

Questions & Answers

Recently Viewed Presentations

  • Team 10: Battery Supercapacitor Hybrid System

    Team 10: Battery Supercapacitor Hybrid System

    Team 10: Battery Supercapacitor Hybrid System KELD LLC Problem High Gas Prices Hybrid Electric Vehicles (HEVs) not up to par Battery is not capable of high power demand Objective To design and develop a battery-supercapacitor hybrid system Specifications 1kW Pulse...
  • Modeling Issues of the Delta Salinity Jamie Anderson

    Modeling Issues of the Delta Salinity Jamie Anderson

    Modeling Issues of the Delta Salinity Jamie Anderson Ph.D., P.E. CWEMF Workshop February 6, 2007 Modeling Support Branch Bay Delta Office Delta Salinity Modeling Issues Choice of parameter (Jamie Anderson) Cross sectional variation in salinity (Pete Smith) Vertical stratification Lateral...
  • The Big Bang Theory:

    The Big Bang Theory:

    Where did the Big Bang take place? How do we know that the Big Bang was hot? What was the universe like during its beginning years? What is "dark energy"? How does the curvature of the universe reveal its presence?...
  • Dias nummer 1 - uudanmark.dk

    Dias nummer 1 - uudanmark.dk

    For at lykkes i det Australier Jim Bright - arbejdet med læring, stress i arbejdslivet; vejledning og Robert Pryor - modstykke til træk -faktor teorierne, interessekategorierne vil han heller ikke bruge; i gæld til Krumboltz - planned happenstance Matching giver...
  • www.microsoft.com

    www.microsoft.com

    Cloud Computing . Well-documented benefits. Trend to move computation and data to cloud. Database functionality. Amazon RDS. Microsoft SQL Azure. HerokuPostegreSQL. Xeround [AF+09
  • CourseDen Orientation CourseDen uwgonline.westga.edu eCore  Online Core Classes

    CourseDen Orientation CourseDen uwgonline.westga.edu eCore Online Core Classes

    eCore. Synchronous 8 & 16 week sessions. Fulfills ALL core areas (A - E) Transferable to all USG institutions. Seats are always available. If interested, contact NakitaHogans at 678-839-6091
  • Art of the Americas - Union-Endicott Central School District

    Art of the Americas - Union-Endicott Central School District

    Warbonnets (or war bonnets) are the impressive feather headdresses commonly seen in Western movies and TV shows. Although warbonnets are the best-known type of Indian headdress today, they were actually only worn by a dozen or so Indian tribes in...
  • Presentación de PowerPoint

    Presentación de PowerPoint

    Quiero suponer que todos ya conocéis la Intranet, puesto que han hecho sus deberes de descargarse el manual y traerlo estudiado al curso. De cualquier forma, repasare los temas abordados en el manual por si hay alguna duda.