This tutorial shows you how to connect Python applications to Oracle Autonomous Database (ADB) using the cx_Oracle interface. This interface lets you quickly develop applications that execute SQL or PL/SQL statements. Your applications can also use Oracle's document storage SODA calls. The cx_Oracle API conforms to the Python Database API v2.0 Specification with a considerable number of additions and a couple of exclusions.
If you would like to use a local database instead, then see the Developing Python Applications for Oracle Database tutorial.
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.
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.
You may select the Always Free option to use the Always Free Oracle ADB. Choose the Shared Infrastructure deployment type.
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.
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.
Install Python 3.
The steps below assume you installed Python x86-64, but 32-bit Python can also be used.
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.
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
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.
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:
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)
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.
This tutorial shows you how use the Python cx_Oracle interface on macOS (Intel x86) to connect applications to Oracle Autonomous Database (ADB). Follow the steps below.
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.
You may select the Always Free option to use the Always Free Oracle ADB. Choose the Shared Infrastructure deployment type.
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.
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.
python -m pip install cx_Oracle --upgrade --user
Make sure you are using the newly installed Python.
For further assistance and options, such as for installing behind an HTTP proxy, see Installing cx_Oracle on macOS.
Download the free Oracle Instant Client Basic DMG file from Instant Client Downloads for macOS (Intel x86).
Mount the DMG and run its install_ic.sh script. More details are in the Instant Client installation instructions.
Move the credentials zip file to the network/admin sub-directory of your Instant Client directory and unzip it. For example:
mv Wallet_*.zip $HOME/Downloads/instantclient_19_8/network/admin
cd $HOME/Downloads/instantclient_19_8/network/admin
unzip Wallet_*.zip
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:
import os
import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME")+"/Downloads/instantclient_19_8")
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)
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.
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.
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.
You may select the Always Free option to use the Always Free Oracle ADB. Choose the Shared Infrastructure deployment type.
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.
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.
Install Python 3. For example, on Oracle Linux 7:
sudo yum install -y python3
With Linux ARM (aarch64), also install the python3-devel package using yum.
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.
Install Instant Client, for example on Oracle Linux 7 x86_64:
Configure the Instant Client repository:
sudo yum install oracle-instantclient-release-el7
Install the Basic package:
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:
sudo yum install oracle-release-el7
Install the Basic package, for example:
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.
Move the credentials zip file to the network/admin sub-directory of your Instant Client directory and unzip it. For example:
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.
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)
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.