This tutorial shows you how to use Python with Oracle Database 11g. For more tutorials, visit the following Oracle By Examples articles. OK Filling help tables. This is PostgreSQL Python tutorial. This tutorial covers the basics of PostgreSQL programming in Python.
Using Python With Oracle Database 1. Purpose. This tutorial shows you how to use Python with Oracle Database 1. For more tutorials, visit the following Oracle By Examples articles: Developing a Python Web Application with Oracle Database 1. Using Python with Oracle Database 1.
Time to Complete. Approximately 1 hour. Overview. Python is a popular general purpose dynamic scripting language. With the rise of Frameworks, Python is also becoming common for Web application development. If you want to use Python and an Oracle database, this tutorial helps you get started by giving examples. If you are new to Python review the Appendix: Python Primer to gain an understanding of the language. Prerequisites. For this Hands On Session, the following has already been installed for you: Oracle Database 1.
R2, with a user . The example tables in this schema are from Oracle's Human Resources or . Many inbuilt and third party modules can be included in this way in Python scripts. The connect() method is passed the username . In this case, Oracle's Easy Connect connection string syntax is used.
It consists of the IP of your machine and the DB service name . Any connections not explicitly closed will be automatically released when the script ends. In a command line terminal run: python connect. If the connection succeeds, the version number is printed: An exception is thrown if the connection fails. Indentation is used in Python to indicate the code structure.
There are no statement terminators unlike many other languages and there are no begin/end keywords or braces to indicate blocks of code. Open connect. py in an editor. Indent the print statement by two spaces ad save the file. Here the Python interpreter is not expecting a new code block level after the connect() call so it warns about the different indentation.
Syntax: cursor.execute(operation, params=None, multi=False) iterator = cursor.execute(operation, params=None, multi=True) This method executes the given database.
In other cases such as in 'if' and loop blocks (shown later), take care that all statements in each block are equally indented. If you are copying and pasting from this lab, check the pasted indentation is correct before running each example. Python treats everything as an object.
The last element of the list is ver. A list slice is created by ver. This returns the elements starting at position 1 and up to, but not including, elements from position 4. Python lists have methods and can also be manipulated with operators. Change connect. py to. The remove(. The first print and if are at the same level of indentation because they are both inside the loop. Re- run the script in the command line terminal: python connect.
The loop prints and tests each value from the list in turn. Using Database Resident Connection Pooling. Database Resident Connection Pooling is a new feature of Oracle Database 1. It is useful for short lived scripts such as typically used by web applications. It allows the number of connections to be scaled as web site usage grows. It allows multiple Apache processes on multiple machines to share a small pool of database server processes.
Without DRCP, a Python connection must start and terminate a server process. Below left is diagram of nonpooling. Every script has its own database server proces. Scripts not doing any database work still hold onto a connection until the connection is closed and the server is terminated. Below right is a diagram with DRCP. Scripts can use database servers from a pool of servers and return them when no longer needed.
Batch scripts doing long running jobs should generally use non- pooled connections. This tutorial shows how DRCP can be used by new or existing applications without writing or changing any application logic.
Perform the following steps: Review the code as follows, which is contained in the connect. A Connection Class . Session information (such as the default date format) might be retained between connection calls, giving performance benefits. Session information will be discarded if a pooled server is later reused by an application with a different connection class name. Applications that should never share session information should use a different connection class and/or use ATTR.
This reduces overall scalability but prevents applications mis- using session information. Run connect. There are a number of functions you can use to query an Oracle database, but the basics of querying are always the same: 1. Parse the statement for execution. Bind data values (optional).
Execute the statement. Fetch the results from the database.
To create a simple query, and display the results, perform the following steps. Review the code as follows that is contained in the query. HOME directory. Perform the following steps. Review the code as follows that is contained in the query. When called multiple time, consecutive rows are returned: Run the script in a terminal window.
Here the num. Rows parameter specifices that three rows should be returned. Run the script in a terminal window. The output is a list (Python's name for an array) of tuples. Each tuple contains the data for one row.
The list can be manipulated in the any Python manner. The choice of which fetch method to use will depend on how you want to process the returned data. Improve Query Performance. This section demonstrates a way to improve query performance by increasing the number of rows returned in each batch from Oracle to the Python program. Perform the following steps: First, create a table with a large number of rows.
Review the following query. The arraysize is set to 1. This causes batches of 1. Python. This reduces the number of . The fetchone(), fetchmany() and even fetchall() methods will read from the cache before requesting more data from the database. From a terminal window, run: python query. In general, larger array sizes improve performance.
Depending how fast your system is, you may need to use different arraysizes than those given here to see a meaningful time difference. There is a time/space tradeoff for increasing the arraysize. Larger arraysizes will require more memory in Python for buffering the records. Using Bind Variables. Bind variables enable you to re- execute statements with new values, without the overhead of reparsing the statement. Bind variables improve code reusability, and can reduce the risk of SQL Injection attacks. To use bind variables in this example, perform the following steps..
Review the code as follows that is contained in the bind. The statement is only prepared once but executed twice with different values for the WHERE clause. The special symbol 'None' is used in place of the statement text argument to execute() because the prepare() method has already set the statement.
The second argument to the execute() call is a Python Dictionary. In the first execute call, this associative array has the value 2. The second execute uses the value 1. From a terminal window, run. The setinputsizes() call describes the columns.
The first column is integral. The second column has a maximum of 2. The executemany() call inserts all seven rows. The commit call is commented out, and does not execute.
The final part of the script queries the results back and displays them as a list of tuples. From a terminal window, run: python bind.
When the changed data is committed to the database, it is then available to other users and sessions. This is a database transaction. Perform the following steps. Edit the script used in the previous section bind. Doing your own transaction control has performance and data- integrity benefits. Using PL/SQL Stored Functions and Procedures. PL/SQL is Oracle's procedural language extension to SQL.
PL/SQL procedures and functions are stored and run in the database. Using PL/SQL lets all database applications reuse logic, no matter how the application accesses the database. Many data- related operations can be performed in PL/SQL faster than extracting the data into a program (for example, Python) and then processing it.
Oracle also supports Java stored procedures. In this tutorial, you will create a PL/SQL stored function and procedure and call them in Python scripts. Perform the following steps...
Start SQL*Plus and create a new table, ptab with the following command. Review the create. The constant cx. The two PL/SQL function parameters are passed as a tuple and bound to the function parameter arguments. From a terminal window, run: python plsql. The second parameter contains an OUT return value. The number 1. 23 and the return variable name are bound to the procedure call parameters using a tuple. From a terminal window, run: python plsql.
This can be useful in many circumstances, including for mid- tier cache invalidation. A cache might hold some values that depend on data in a table.
If the table changes, the cached values must be updated with the new information. This example show DCN events being handled in Python. Perform the following steps.... Review the code as follows that is contained in the dcn. HOME directory. This function will be called when a table changes. The 'message' parameter is a cx.
The function simply prints out the kinds of changes that have occurred and the affected rowids. The main body of the script is below the function - note the indentation level of 'con = ..' is the same as for 'def ..'. The body creates the database connection with the 'events = True' parameter to allow the database to send an event notification to Python when a table change takes place. The subscribe() call registers the DCNCallback() to be called in a new thread when an INSERT or UPDATE occurs.
Only one Python thread can run at any given time. Python switches between threads as needed. The rowids = True parameter lets rowids be accessed in the callback. The registerquery() call registers a query that selects everything from the MYTAB table.
Any change to the table that is an UPDATE or DELETE will cause DCNCallback() to be called. The MYTAB table was created in a previous section of the lab.
The script concludes with a raw. In the first window run. This will print a message and wait without returning a prompt: Leave it running for the moment and continue with the next step.
Python My. SQL Database Access. The Python standard for database interfaces is the Python DB- API. Most Python database interfaces adhere to this standard. You can choose the right database for your application. Python Database API supports a wide range of database servers such as .
For example, if you need to access an Oracle database as well as a My. SQL database, you must download both the Oracle and the My. SQL database modules. The DB API provides a minimal standard for working with databases using Python structures and syntax wherever possible. This API includes the following: Importing the API module.
Acquiring a connection with the database. Issuing SQL statements and stored procedures. Closing the connection. We would learn all the concepts using My. SQL, so let us talk about My. SQLdb module. What is My. SQLdb? My. SQLdb is an interface for connecting to a My.
SQL database server from Python. It implements the Python Database API v. My. SQL C API. How do I Install My. SQLdb? Before proceeding, you make sure you have My.
SQLdb installed on your machine. Just type the following in your Python script and execute it. If it produces the following result, then it means My. SQLdb module is not installed. Traceback (most recent call last). File . Next, db object is used to create a cursor object, which in turn is used to execute SQL queries. Finally, before coming out, it ensures that database connection is closed and resources are released.
Creating Database Table. Once a database connection is established, we are ready to create tables or records into the database tables using execute method of the created cursor.
Example. Let us create Database table EMPLOYEE. Open database connection. My. SQLdb. connect(.
You can use either fetchone() method to fetch single record or fetchall() method to fetech multiple values from a database table. It fetches the next row of a query result set. A result set is an object that is returned when a cursor object is used to query a table. It fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves. This is a read- only attribute and returns the number of rows that were affected by an execute() method.
Example. The following procedure queries all the records from EMPLOYEE table having salary more than 1. Here, we increase AGE of all the males by one year. Example. #!/usr/bin/python. Open database connection. My. SQLdb. connect(.
Following is the procedure to delete all the records from EMPLOYEE where AGE is more than 2. Transactions have the following four properties: Atomicity: Either a transaction completes or nothing happens at all. Consistency: A transaction must start in a consistent state and leave the system in a consistent state. Isolation: Intermediate results of a transaction are not visible outside the current transaction. Durability: Once a transaction was committed, the effects are persistent, even after a system failure. The Python DB API 2.
Example. You already know how to implement transactions. Here is again similar example . However, instead of depending on any of DB lower level implementation details, your application would be better off calling commit or rollback explicitly. Handling Errors. There are many sources of errors. A few examples are a syntax error in an executed SQL statement, a connection failure, or calling the fetch method for an already canceled or finished statement handle. The DB API defines a number of errors that must exist in each database module. The following table lists these exceptions.
Exception. Description. Warning. Used for non- fatal issues.
Must subclass Standard. Error. Error. Base class for errors. Must subclass Standard. Error. Interface. Error. Used for errors in the database module, not the database itself.
Must subclass Error. Database. Error. Used for errors in the database. Must subclass Error.
Data. Error. Subclass of Database. Error that refers to errors in the data. Operational. Error. Subclass of Database. Error that refers to errors such as the loss of a connection to the database. These errors are generally outside of the control of the Python scripter. Integrity. Error.
Subclass of Database. Error for situations that would damage the relational integrity, such as uniqueness constraints or foreign keys. Internal. Error. Subclass of Database. Error that refers to errors internal to the database module, such as a cursor no longer being active.
Programming. Error. Subclass of Database. Error that refers to errors such as a bad table name and other things that can safely be blamed on you. Not. Supported. Error. Subclass of Database. Error that refers to trying to call unsupported functionality. Your Python scripts should handle these errors, but before using any of the above exceptions, make sure your My.
SQLdb has support for that exception. You can get more information about them by reading the DB API 2.