Python cx_Oracle on Windows

Open all Close all

This tutorial shows you how use the Python cx_Oracle interface on Windows 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.

• 3. Install Python

  Install Python 3.

  The steps below assume you installed Python x86-64, but 32-bit Python can also be used.

• 4. Install cx_Oracle

  Install cx_Oracle:

  Copy

  python -m pip install cx_Oracle --upgrade --user

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

• 5. Install the Oracle Instant Client Basic Package

  Download the free Oracle Instant Client Basic zip file from Oracle Instant Client for Microsoft Windows (x64) 64-bit. (If your Python is 32-bit, then you will need to download the 32-bit Basic package from here instead). Remember to install the matching VS Redistributable, as shown on the download page.

  Extract the libraries to an accessible directory, for example the libraries could be in C:\oracle\instantclient_19_9

• 6. Unzip the Wallet

  Make a network\admin sub-directory in your Instant Client directory, for example C:\oracle\instantclient_19_9\network\admin.

  Unzip the previously obtained credentials zip file and move the extracted files to the new network\admin sub-directory.

• 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
    
    cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_9")
    
    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 and set the appropriate path to your Instant Client directory in the init_oracle_client() call.
  • 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 database 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 be seen in the wallet tnsnames.ora file.
  • Save the changes to example.py.

• 8. Run the Python Application

  Run the app:

  Copy

  python 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.