Python+Oracle connectivity （转载）

本原文来自于：

Python

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

Contents []

• 4 Executing DDL statements

[] 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 access to Oracle databases and conforms to the Python database API specification. 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() [] 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 = 50 cursor.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_3 cursor.close() connection.close() [] 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 statements:

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