1 / 28

Python & Oracle

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

lbergen
Download Presentation

Python & Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Python & Oracle By Michael McLaughlin Python cx_Oracle Tutorialcx_Oracle Documentation

  2. 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

  3. Introduce Python

  4. Python? • A robust high-level programming language: • Gets complex things done quickly • Automate system and data integration tasks • Solve complex analytical problems

  5. Install cx_Oracle Driver

  6. 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

  7. 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

  8. 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*

  9. Verify cx_Oracle Installation • Connect to the Python IDLE environment • Type in the following commands: >>> import cx_Oracle >>> db = cx_Oracle.connect("student/student@xe") >>> print db.version • Returns the following for Oracle 11g XE: 11.2.0.2.0

  10. Connect to Oracle

  11. Implement a connect.py script #!/usr/bin/python# Import the Oracle library.import cx_Oracle, systry:  # Create a connection.db = cx_Oracle.connect("student/student@xe")  # 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();

  12. Run a static SQL Statement

  13. Implement a static query #!/usr/bin/python... try:  # Create a connection.db = cx_Oracle.connect("student/student@xe")  # 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()

  14. Returning rows from a cursor #!/usr/bin/python... # Execute a query. cursor.execute("SELECT item_title " +                  ",      item_rating " +                  "FROM   item " +                  "WHERE  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])...

  15. Run a dynamic SQL Statement

  16. Binding a value in a cursor #!/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. 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])... The local variable. The bind variable. Name and value pair assignment.

  17. Binding two values in a cursor #!/usr/bin/python... # 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])... The list of local variables. The bind variables. Name and value pair assignment.

  18. Binding a dynamic dictionary #!/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 = {}... A tuple used to name dictionary names, or the names of name-value pairs. A dynamic list of variables that need to bound to a dynamic SQL statement Declaring an empty dictionary.

  19. 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]) else: stmt = stmt + ":" + str(ind[j]) + ", "   # Close lookup value set. stmt =  stmt + ")" + "\n" \          "ORDER BY 1”... Logic to build list of bind variables and dynamic dictionary. Append closing parenthesis of list.

  20. 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)

  21. 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 to bind targets. Commit change.

  22. 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()

  23. Call PL/SQL stored programs

  24. Calling a procedure ...# Create a connection. db = cx_Oracle.connect("student/student@xe") # Create a cursor. cursor = db.cursor() # Call a stored procedure.cursor.callproc( 'insert_bill_detail' \        , ( sBillNumber        \        , sBillText          \        , sDetailNumber      \        , sDetailText   )) ... First call parameter is the name of the procedure. Second call parameter is the list of actual parameters.

  25. 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/student@xe” # Create a cursor. cursor = db.cursor() # Call a stored procedure. cursor.callproc( 'insert_bill_detail', param) ... Call parameters bundled in a list.

  26. Calling a function with list ...# Create a sequence for a procedure call.param = (sBillNumber, sBillText, sDetailNumber, sDetailText) try: # Create a connection. db = cx_Oracle.connect("student/student@xe") # 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)) + "]”... Function return type. Capture a failure code from the function.

  27. 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

  28. Questions & Answers

More Related