Python

From Oracle FAQ
Jump to: navigation, search

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

Oracle connectivity[edit]

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[edit]

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[edit]

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[edit]

Example to execute DDL statements:

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

External links[edit]