Python cx_Oracle on Linux

Open all Close all

This tutorial shows you how use the Python cx_Oracle interface on Linux to connect applications to Oracle Autonomous Database (ADB). Follow the steps below.

• 1. Provision an ADB Instance

  Click on the links below to walk through these steps if you have not yet provisioned an ADB instance. Remember the ADMIN password. You will use this to connect Python to ADB.

  • Create an Oracle cloud account.
  • Provision an ADB instance.

  You may select the Always Free option to use the Always Free Oracle ADB. Choose the Shared Infrastructure deployment type.

• 2. Obtain Client Credentials

  Below are the instructions to download the client credentials from the Oracle Cloud Console. The credentials give mutual TLS, providing enhanced security for authentication and encryption.

  • From the Oracle Cloud Console, go to the Autonomous Database Details page of your Oracle Autonomous Database instance.
  • Click the DB Connection button.
  

  • A new window will appear. Click the Download Wallet button.

  

  • Enter a wallet password in the Password field and confirm the password in the Confirm password field. Then, click the Download button. The password must be at least 8 characters long and include at least 1 letter and either 1 numeric character or 1 special character. Although not required for Python, this password protects the downloaded client credentials wallet.

  • Save the credentials zip file to a secure location on the computer where you will be running Python.

• 3. Install Python

  Install Python 3. For example, on Oracle Linux 7:

  Copy

  sudo yum install -y python3

  With Linux ARM (aarch64), also install the python3-devel package using yum.

• 4. Install cx_Oracle

  Install cx_Oracle:

  Copy

   python3 -m pip install cx_Oracle --upgrade --user 

  Make sure you use the python3 executable.

  For further assistance and options, such as for installing behind an HTTP proxy, see Installing cx_Oracle on Linux.

• 5. Install the Oracle Instant Client Basic Package

  Install Instant Client, for example on Oracle Linux 7 x86_64:

  • Configure the Instant Client repository:

    Copy

    sudo yum install oracle-instantclient-release-el7

  • Install the Basic package:

    Copy

    sudo yum install oracle-instantclient-basic

  On Linux ARM (aarch64) use Instant Client 19c. For example on Oracle Linux 7:

  • Configure the Instant Client repository:

    Copy

    sudo yum install oracle-release-el7

  • Install the Basic package, for example:

    Copy

    sudo yum install oracle-instantclient19.10-basic

  For other Linux flavors, install the Instant Client ZIP files and follow the instructions from the download page: Instant Client for Linux x86-64 (64-bit) or Instant Client for Linux ARM (aarch64). If you use ZIP files, make sure to run ldconfig or set LD_LIBRARY_PATH as shown in the instructions.

• 6. Unzip the Wallet

  Move the credentials zip file to the network/admin sub-directory of your Instant Client directory and unzip it. For example:

  Copy

  sudo cp Wallet_*.zip /usr/lib/oracle/21/client64/lib/network/admin/
  
  sudo sh -c 'cd /usr/lib/oracle/21/client64/lib/network/admin/ && unzip -B Wallet_*.zip'

  If you installed Instant Client 19c, the directory might be like /usr/lib/oracle/19.10/client64/lib/network/admin/.

  Wallet files including network/admin/tnsnames.ora should exist.

• 7. Create a Python Application

  • Using your favorite text editor, create a new Python file example.py in a directory of your choice. It should contain the following code. Make sure the indentation is the same as shown:
    Copy

    import cx_Oracle
    
    
    connection = cx_Oracle.connect(user="admin", password="XXXX", dsn="XXX_dbhigh")
    
    cursor = connection.cursor()
    
    # Create a table
    
    cursor.execute("""begin
                         execute immediate 'drop table pytab';
                         exception when others then if sqlcode <> -942 then raise; end if;
                      end;""")
    cursor.execute("create table pytab (id number, data varchar2(20))")
    
    # Insert some rows
    
    rows = [ (1, "First" ),
             (2, "Second" ),
             (3, "Third" ),
             (4, "Fourth" ),
             (5, "Fifth" ),
             (6, "Sixth" ),
             (7, "Seventh" ) ]
    
    cursor.executemany("insert into pytab(id, data) values (:1, :2)", rows)
    
    # connection.commit()  # uncomment to make data persistent
    
    # Now query the rows back
    for row in cursor.execute('select * from pytab'):
        print(row)

  • Modify example.py to use your ADB connection information in the cx_Oracle.connect() call:
    • User: Use admin which is the user created by default during the creation of Autonomous Database. (If you created another ADB user, you can use that user instead.)
    • Password: Use the user's password. If connecting as the admin user, set this to the password you chose during the Create Autonomous Database step while provisioning Autonomous Database.
    • Data Source Name (DSN): Use a net service name such as DBName_high. DBName is the Database Name entered during the Create Autonomous Database step while provisioning Autonomous Database. The available net service names can also be seen in the wallet tnsnames.ora file.
  • Save the changes to example.py.

• 8. Run the Python Application

  Run the app using the Python 3 executable:

  Copy

  python3 example.py 

  You will see the queried rows returned from the database. Congratulations! You have successfully used Oracle Autonomous Database.

  Note: If you connect to ADB from behind a firewall, you will likely encounter a connection timeout error. Update the tnsnames.ora file to use an HTTP proxy. Learn how to do this in this ADB documentation section. Scroll down to the "Connections with an HTTP Proxy" section on the doc page.

Now you have completed this quick start tutorial, you should continue with the full Python and Oracle Database Tutorial to learn more about using cx_Oracle.

More information and resources on using cx_Oracle are available here.