Python
From Oracle FAQ
⧼orafaq-jumptonavigation⧽⧼orafaq-jumptosearch⧽
Python is an interpreted, interactive, object-oriented programming language. It is often compared to Perl, Java and Tcl.
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()
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()
