Python+Oracle connectivity （转载）

本原文来自于： Python+Oracle connectivity

Python

Python is an interpreted, interactive, object-oriented programming language. It is often compared to Perl, Java and Tcl.

Contents [] 1 Oracle connectivity 2 Fetching rows 3 Inserting rows 4 Executing DDL statements 5 External links

[edit] Oracle connectivity

Before you can access a database, you need to install one of the many available database modules. One such module is cx_Oracle.

cx_Oracle is a Python extension module that allows accessto Oracle databases and conforms to the Python database APIspecification. The cx_Oracle module must be imported as it's not part of the core Python language. Example:

python>>> import cx_Oracle>>> connection = cx_Oracle.connect('scott/tiger@orcl')>>> # do some stuff here>>> connection.close()[edit] Fetching rows

Define a cursor to fetch rows. Examples:

connection = cx_Oracle.connect("uid/pwd@database")cursor = connection.cursor()cursor.execute("SELECT COUNT(*) FROM User_Tables")count = cursor.fetchall()[0][0]cursor.close()connection.close()

More complex example, using bind variables:

connection = cx_Oracle.connect("uid/pwd@database")cursor = connection.cursor()cursor.arraysize = 50cursor.execute(""" select Col1, Col2, Col3 from SomeTable where Col4 = :arg_1 and Col5 between :arg_2 and :arg_3""", arg_1 = "VALUE", arg_2 = 5, arg_3 = 15)for column_1, column_2, column_3 in cursor.fetchall(): print "Values from DB:", column_1, column_2, column_3cursor.close()connection.close()[edit] Inserting rows

Note the use of named variables, and that dates are handled using an explicit "to_date(...)" conversion:

connection = cx_Oracle.connect("uid/pwd@database")cursor = connection.cursor()cursor.execute("INSERT INTO User_Tables(login,first_name,last_name,age,date_of_birth) VALUES (:login,:first,:last,:age,to_date(:dob,'YYYY-MM-DD HH24:MI:SS'))", { 'login' : 'some_user_login', 'first' : 'some_first_name', 'last' : 'some_last_name', 'age' : 42, 'dob' : '1970-01-01 23:52:00', } )count = cursor.fetchall()[0][0]cursor.close()connection.commit()connection.close()[edit] Executing DDL statements

Example to execute DDL statements:

connection = cx_Oracle.connect("uid/pwd@database")cursor = connection.cursor()cursor.execute("CREATE TABLE x(a DATE)")cursor.close()[edit] External links

• Python home page
• cx_Oracle home page