Oracle+python query Oracle for related help documents

From: http://www.oracle.com/technetwork/cn/articles/dsl/prez-python-queries-101587-zhs.html

In the core principles of Python's way of doing things, there is a requirement to have an advanced interface to the API. The database API (in this case, the Oracle API) is an example. With Computronix's cx_oracle python module, you can control Oracle's query model while maintaining compatibility with the Python database API specification v2.0.

For all client libraries that follow this specification, the model for querying the database using DB API 2.0 is consistent. On this basis, Cx_oracle's main developer, Anthony Tuininga, added a rich set of properties and methods to reveal Oracle's unique features to developers. It is absolutely possible to forget the "extra" method in a standard way, but you will not do so in this article. The concept of universal database wrapping may work in some cases, but at the same time you lose all the optimizations provided by the RDBMS. Introduction to DB API 2.0 and Cx_oracle

Python Database API Specification v2.0 is the result of a collective effort to unify the access model for different database systems. Have a relatively small set of methods and properties that are easy to learn and keep in line when replacing a database vendor. It does not map database objects into a PYTHON structure in any way. Users still need to write SQL manually. This SQL may need to be rewritten after you replace it with another database. Nevertheless, it was a good solution to the problem of connectivity in the Python database.

The specification defines various parts of the API, such as module interfaces, connection objects, cursor objects, type objects and constructors, optional extensions of the DB API, and optional error-handling mechanisms.

The gateway between the database and the Python language is the connection object. It contains all the components needed to make a database-driven application, not only with DB API 2.0, but also a superset of canonical methods and properties. In multithreaded programs, modules and connections can be shared among different threads, but cursor sharing is not supported. This limitation is generally acceptable because a shared cursor can pose a deadlock risk.

Python uses the exception model heavily, and the DB API defines a number of standard exceptions that are useful when debugging problems in an application. The following are standard exceptions, along with a brief description of the type of reason: warning-data is truncated when the insert operation is performed, and so on error-the base class for all exceptions except Warning mentioned here. interfaceerror-the database interface rather than the database itself (this example is a cx_oracle problem) databaseerror-A strictly database problem dataerror-the problem with the following result data is 0, the value is out of range, and so on Operatio nalerror-database errors that are not related to the programmer: Connection loss, memory allocation errors, transaction errors, and so on integrityerror-database relational integrity was affected, for example, a FOREIGN KEY constraint failed internalerror-database encountered an internal error, for example, Invalid cursor, transaction out-of-sync programmingerror-not found table, syntax error in SQL statement, number of specified arguments error, etc. notsupportederror-called API parts do not exist

The connection process begins with the connection object, which is the basis for creating the cursor object. In addition to cursor operations, connection objects manage transactions using the commit () and rollback () methods. Executing SQL queries, issuing DML/DCL statements, and obtaining results are all subject to cursor control.

In the implementation of cursors and connection classes, Cx_oracle extends the standard DB API 2.0 specification to the fullest extent possible. If necessary, all of these extensions will be clearly marked in the text. Getting Started

Before you use queries and cursors, you first need to establish a database connection. There are several ways to provide credentials and data source names, and the results are similar. In the content extracted from the following Python Interactive session, the Connection object db, DB1, and DB2 are equivalent. The MAKEDSN () function creates an TNS entry based on the given parameter value. This is assigned to the variable dsn_tns. If the environment is set up properly, you can use a shorter form of cx_oracle.connect (' hr/hrpwd ') to omit the simple connection string for db and DB1.

>>> Import Cx_oracle
>>> db = Cx_oracle.connect (' hr ', ' hrpwd ', ' localhost:1521/xe ')
>>> db1 = cx_oracle.connect (' Hr/hrpwd@localhost:1521/xe ')
>>> dsn_tns = cx_oracle.makedsn (' localhost ', 1521, ' XE ')
>>> Print Dsn_tns
(Description= (address_list= (address= (protocol=tcp) (Host=localhost) (port=1521))) (Connect_data= (SID=XE)))
>>> DB2 = Cx_oracle.connect (' hr ', ' hrpwd ', dsn_tns)

Within the scope of the connection object (such as the connection object assigned to the DB variable above), you can obtain the database version by querying the Version property (this is an extension of DB API 2.0). This can be used to make Python programs dependent on specific versions of Oracle products. Similarly, you can obtain the connection string for a connection by querying the DSN property.

>>> Print Db.version
10.2.0.1.0
>>> versioning = Db.version.split ('. ')
>>> Print Versioning
[' 10 ', ' 2 ', ' 0 ', ' 1 ', ' 0 ']
>>> if versioning[0]== ' 10 ':
... print "Running 10g"
... elif versioning[0]== ' 9 ':
... print "Running 9i"
...
Running 10g
>>> Print Db.dsn
Localhost:1521/xe
Cursor Objects

You can define any number of cursors by using the cursor () method of the Connection object. A simple program uses a cursor that can be reused repeatedly. However, larger projects may require several different cursors.

>>> cursor = Db.cursor ()
Application logic often requires a clear distinction between the processing stages of statements issued against a database. This helps to better understand performance bottlenecks and to write faster and optimized code. Statement processing is divided into three stages:

Analysis (optional) Cx_Oracle.Cursor.parse ([statement])
Calls are not actually required because SQL statements are parsed automatically during the execution phase. This method can be used to validate a statement before it is executed. When an error is detected in such a statement, a Databaseerror exception is thrown, and the corresponding error message can usually be "ora-00900:invalid SQL statement, ora-01031:insufficient privileges or ora-00921:unexpected end of SQL command. Execute cx_Oracle.Cursor.execute (statement, [parameters], **keyword_parameters)
This method can accept a single parameter--an SQL statement--that runs directly against the database. A binding variable that assigns a value through a parameters or Keyword_parameters parameter can be specified as a dictionary, sequence, or set of keyword parameters. If a dictionary or keyword parameter is already provided, the values are bound to the name. If the sequence is given, they are parsed according to the position of the values. If this is a query operation, this method returns a list of variable objects, or none if it is not. Cx_Oracle.Cursor.executemany (statement, parameters)
This is especially useful for bulk inserts because it can limit the number of Oracle execution operations required to just one. For more information about how to use this method, see the "Once Multiple lines" section below.
Get (optional)-for queries only (because DDL and DCL statements do not return results). These methods will throw a Interfaceerror exception on cursors that do not execute the query. Cx_Oracle.Cursor.fetchall ()
Gets all remaining rows in the result set, in the form of a byte group list. If there are no remaining rows, it returns a blank list. The get operation can be adjusted by setting the cursor's ArraySize property, which sets the number of rows returned from the database in each underlying request. The higher the ArraySize setting, the less the number of times you need to return to and from the network. The default value for ArraySize is 1. Cx_Oracle.Cursor.fetchmany ([Rows_no])
Gets the next Rows_no row in the database. If this parameter is not specified, the method gets the number of rows that are arraysize. If the rows_no is greater than the number of fetched rows, the method gets the number of rows that are left. Cx_Oracle.Cursor.fetchone ()
Gets a single byte group from the database, or none if there are no remaining rows.

Before you continue to understand the cursor example, understand the Pprint function of the Pprint module. It is used to output PYTHON data structures in clear, readable form.

>>> from Pprint import pprint
>>> cursor.execute (' SELECT feed_id, Feed_url, Xmltype.getclobval (feed_xml) from Rss_feeds ')
>>> cursor.execute (' SELECT * from Jobs ')
[<cx_oracle.string with value none>, <cx_oracle.string with value none>, <cx_oracle.number with value None ", <cx_oracle.number with value none>]
>>> Pprint (Cursor.fetchall ())
[(' Ad_pres ', ' President ', 20000, 40000),
(' AD_VP ', ' administration Vice President ', 15000, 30000),
(' Ad_asst ', ' administration Assistant ', 3000, 6000),
(' Fi_mgr ', ' Finance Manager ', 8200, 16000),
(' Fi_account ', ' Accountant ', 4200, 9000),
?
(' Pr_rep ', ' public Relations representative ', 4500, 10500)]

The cx_oracle cursor is an iterator. With these powerful Python constructs, you can iterate through the sequence in a natural way, only to get subsequent items as needed. High-cost database selection operations naturally conform to this idea, since data is only acquired when needed. You can do an iteration until you find the value you want or meet another condition without having to create or get the entire result set.

>>> cursor = Db.cursor ()
>>> cursor.execute (' SELECT * from Jobs ')
[<cx_oracle.string with value none>, <cx_oracle.string with value none>, <cx_oracle.number with value None ", <cx_oracle.number with value none>]
>>> for row in cursor: # # Notice, this is plain english!
... print row
...
(' AD_VP ', ' administration Vice President ', 15000, 30000)
(' Ad_asst ', ' administration Assistant ', 3000, 6000)
(' Fi_mgr ', ' Finance Manager ', 8200, 16000)
(' Fi_account ', ' Accountant ', 4200, 9000)
(' Ac_mgr ', ' Accounting Manager ', 8200, 16000)
?
(' Pr_rep ', ' public Relations representative ', 4500, 10500)
After the execution of the list (cursor), the same task is performed against Cursor.fetchall (). This is because the built-in list () function iterates until the end of the given iterator.
Data type

In the acquisition phase, basic Oracle data types are mapped to their equivalent data types in Python. Cx_oracle maintains a separate collection of data types that are useful for this transformation. The Oracle-cx_oracle-python map is:

Oracle	Cx_oracle	Python
VARCHAR2 NVARCHAR2 LONG	Cx_oracle.string	Str
CHAR	Cx_oracle.fixed_char
Number	Cx_oracle.number	Int
FLOAT	Float
DATE	Cx_oracle.datetime	Datetime.datetime
TIMESTAMP	Cx_oracle.timestamp
Clob	Cx_oracle.clob	Cx_oracle.lob
Blob	Cx_oracle.blob

In addition to cases involving large objects, the data type above is usually transparent to the user. As of the version 4.3,cx_oracle, these data types are still processed and not packaged with the built-in file types.

Other data types that cx_oracle are not currently responsible for processing include xmltype and all complex types. All queries for columns of unsupported type are currently unsuccessful, and Notsupportederror exceptions are thrown. You need to clear them from the query or convert them to supported data types.

For example, consider the table provided by the following RSS feeds for storing aggregations:

CREATE TABLE Rss_feeds (
feed_id number PRIMARY KEY,
Feed_url VARCHAR2 () not NULL,
Feed_xml XmlType
);

When you try to query this table using Python, you need to perform some extra steps. In the following example, Xmltype.getclobval () is used to return XML from a table in the form of a CLOB value. >>> cursor.execute (' SELECT * from Rss_feeds ')

Traceback (most recent call last):
File "<pyshell#37>", line 1, in <module>
Cursor.execute (' SELECT * from Rss_feeds ')
NotSupportedError:Variable_TypeByOracleDataType:unhandled Data Type 108
>>> cursor.execute (' SELECT feed_id, Feed_url, Xmltype.getclobval (feed_xml) from Rss_feeds ')
[<cx_oracle.number with value none>, <cx_oracle.string with value none>, <cx_oracle.clob with value NONE&G t;]

As you may have noticed, the Cx_oracle.cursor.execute* series method returns the column data type for the query. These are variable object lists (extensions of DB API 2.0) that get the value None prior to the fetch phase, and get the appropriate data values after the acquisition phase. Details about the data type can be obtained through the Description property of the cursor object. Description is a 7-item byte group that contains column names, column types, display size, internal size, precision, scale, and the possibility of being empty. Note Column information is only accessible to SQL query statements.

>>> column_data_types = Cursor.execute (' SELECT * FROM Employees ')
>>> Print Column_data_types
[<cx_oracle.number with value none>, <cx_oracle.string with value none>, <cx_oracle.string with value None <cx_oracle.string with value None&gt, <cx_oracle.string with value none>, <cx_oracle.datetime with Val UE None&gt, <cx_oracle.string with value None&gt, <cx_oracle.number with value none>, <cx_oracle.number wit h value None>, <cx_oracle.number with value none>, <cx_oracle.number with value none>]
>>> Pprint (cursor.description)
[(' employee_id ', <type ' Cx_oracle.number ', 7, 22, 6, 0, 0),
(' first_name ', <type ' cx_oracle.string ', 20, 20, 0, 0, 1),
(' last_name ', <type ' cx_oracle.string ', 25, 25, 0, 0, 0),
(' EMAIL ', <type ' cx_oracle.string ', 25, 25, 0, 0, 0),
(' Phone_number ', <type ' cx_oracle.string ', 20, 20, 0, 0, 1),
(' hire_date ', <type ' datetime.datetime ', 23, 7, 0, 0, 0),
(' job_id ', <type ' cx_oracle.string ', 10, 10, 0, 0, 0),
(' SALARY ', <type ' Cx_oracle.number ', 12, 22, 8, 2, 1),
(' commission_pct ', <type ' Cx_oracle.number ', 6, 22, 2, 2, 1),
(' manager_id ', <type ' Cx_oracle.number ', 7, 22, 6, 0, 1),
(' department_id ', <type ' Cx_oracle.number ', 5, 22, 4, 0, 1)]
binding variable Pattern
As Oracle Master Tom Kyte introduces, binding variables are a central tenet of database development. Not only do they make the program run faster, but they can also protect against SQL injection attacks. Please see the following query:

SELECT * from Emp_details_view WHERE department_id=50
SELECT * from Emp_details_view WHERE department_id=60
SELECT * from Emp_details_view WHERE department_id=90
SELECT * from Emp_details_view WHERE department_id=110

When run individually, they need to be analyzed separately, which adds additional overhead to your application. By using a binding variable, you can tell Oracle to analyze only one query at a time. Cx_oracle supports binding variables by name or location.

Passing a binding variable by name requires that the parameters parameter that executes the method be a dictionary or a set of keyword parameters. The following query1 and Query2 are equivalent:

>>> named_params = {' dept_id ': $, ' sal ': 1000}
>>> Query1 = Cursor.execute (' SELECT * FROM Employees WHERE department_id=:d ept_id and Salary>:sal ', Named_par AMs
>>> Query2 = Cursor.execute (' SELECT * FROM Employees WHERE department_id=:d ept_id and Salary>:sal ', dept_id=5 0, sal=1000) when you use a named binding variable, you can use the cursor's Bindnames () method to check for a binding variable that is currently specified:

>>> Print Cursor.bindnames ()
[' dept_id ', ' SAL '] is similar to this by location, but you need to name it carefully. The variable name is arbitrary, so it is easy to confuse the query. In the following example, three queries R1, R2, and R3 are equivalent. The parameters variable must be supplied as a sequence.

>>> r1 = cursor.execute (' SELECT * from locations WHERE country_id=:1 and City=:2 ', (' US ', ' Seattle ')
>>> r2 = cursor.execute (' SELECT * from locations WHERE country_id=:9 and City=:4 ', (' US ', ' Seattle ')
>>> R3 = Cursor.execute (' SELECT * from locations WHERE country_id=:m and city=:0 ', (' US ', ' Seattle ') when binding, you can first associate Prepare the statement, and then execute None with the changed arguments. Preparing a statement based on a bound variable is sufficient, and Oracle will process it as it did in the previous example. The prepared statement can be executed any time.

>>> cursor.prepare (' SELECT * from Jobs WHERE min_salary>:min ')
>>> r = Cursor.execute (None, {' min ': 1000})
>>> Print Len (Cursor.fetchall ())
19
You have limited the number of times to analyze. In the next paragraph, we will eliminate unnecessary execution, especially costly bulk inserts.

multiple lines at a time
Large inserts do not require separate inserts multiple times, because Python fully supports inserting multiple rows at a time through the Cx_Oracle.Cursor.executemany method. Limiting the number of actions to perform greatly improves program performance, so you should consider this feature first when writing applications that have a large number of inserts.

Let's start by creating a table for the Python module list, this time directly from Python. You will delete the table at a later time.

>>> create_table = "" "
CREATE TABLE Python_modules (
Module_name VARCHAR2 (m) not NULL,
File_path VARCHAR2 (+) not NULL
)
"""
>>> from sys import modules
>>> Cursor.execute (create_table)
>>> M = []
>>> for M_name, M_info in Modules.items ():
... try:
... M.append ((M_name, m_info.__file__))
... except Attributeerror:
. Pass
...
>>> Len (M)
76
>>> Cursor.prepare ("INSERT into Python_modules (module_name, File_path) VALUES (: 1,: 2)")
>>> Cursor.executemany (None, M)
>>> Db.commit ()
>>> r = Cursor.execute ("Select COUNT (*) from Python_modules")
>>> Print Cursor.fetchone ()
(76,)
>>> cursor.execute ("DROP TABLE python_modules PURGE") sends only one execution to the database, requiring all 76 module names to be inserted. This is a huge performance boost for a large insert operation. Note the two-point difference here: Cursor.execute (create_tab) does not produce any output, because it is a DDL statement, and (76) is a byte group with a single element. The (76) without commas is exactly equal to the integer 76.

Summary

Once you are familiar with the basic concepts of Oracle-python connectivity, you can start writing your own database-driven applications. I strongly recommend taking the time to use the Python interactive shell, because it does make learning process easier.

You learned about the three phases of SQL statements and how to minimize the steps that Oracle databases need to perform. Binding variables are an unavoidable part of database application development, and Python supports binding by name or location.

You also learned about the smooth transitions between Oracle and Python data types, and how the database data is handled naturally in contexts where cursors are processed as iterators. All of these features promote productivity and support focus on data, which is at the core.