This tutorial shows you how use the Python python-oracledb driver on macOS to connect Python applications to Oracle Autonomous AI Database (ADB).

• • 1. Create an ADB Instance

    Follow the steps below to walk through the process of creating an ADB instance:

    1.1 Create a free Oracle Cloud account.

    1.2 Navigate to the Autonomous AI Database page and click Create Autonomous AI Database.

    1.3 Choose a database name, for example PYDB. Select Transaction Processing as the Workload Type.

    1.3.1 In the Database Configuration section select the Always Free slider if you are elgible for an Always Free instance.

    1.4 In the Administrator Credentials Creation section, set the database ADMIN user password.

    1.5 In the Network Access Type section, select Secure access from allowed IPs and VCNs only, and add your IP address. If your IP address changes in future, you will need to update the database service's access control list.

    Note: The Secure access from everywhere Network Access Type option will use mTLS with a wallet which is not covered in this quickstart. Refer to the python-oracledb driver documentation if you need to use it (for example if you are unable to add your IP address).

    1.6 When the database has been created, go to your Autonomous AI Database detail page and select the Database Connection button and scroll down to view Connection Strings. Select "TLS" in the TLS Authentication drop down and then copy any connection string. Save this connection string for later.

  • 2. Install Python

    You may already have Python in /usr/bin/python3. If so, use the full path and binary name in commands below. Otherwise install Python 3.10 or later.

    The steps below assume that the executable is called 'python'. In some environments it might be called something like 'python3.10'.

  • 3. Install python-oracledb

    Install python-oracledb:

    Copy

    python -m pip install oracledb

    Adding the pip --user option may useful when you do not have permission to write to system directories. If you are behind an HTTP proxy, you can also add an option like --proxy=http://proxy.example.com:80

    For further assistance and options, see Installing python-oracledb on macOS.

  • 4. 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 getpass
      import oracledb
      
      pw = getpass.getpass("Enter password: ")
      
      connection = oracledb.connect(
          user="admin",
          password=pw,
          dsn="(description=...)")  # the connection string copied from the cloud console
      
      print("Successfully connected to Oracle Database")
      
      # Create a table
      with connection.cursor() as cursor:
      
          cursor.execute("""
              begin
                  execute immediate 'drop table todoitem';
                  exception when others then if sqlcode <> -942 then raise; end if;
              end;""")
      
          cursor.execute("""
              create table todoitem (
                  id number generated always as identity,
                  description varchar2(4000),
                  creation_ts timestamp with time zone default current_timestamp,
                  done number(1,0),
                  primary key (id))""")
      
          print("Table created")
      
      # Insert some data
      with connection.cursor() as cursor:
      
          rows = [ ("Task 1", 0 ),
                   ("Task 2", 0 ),
                   ("Task 3", 1 ),
                   ("Task 4", 0 ),
                   ("Task 5", 1 ) ]
      
          cursor.executemany("insert into todoitem (description, done) values(:1, :2)", rows)
          print(cursor.rowcount, "Rows Inserted")
      
      connection.commit()
      
      # Now query the rows back
      with connection.cursor() as cursor:
      
          for row in cursor.execute('select description, done from todoitem'):
              if (row[1]):
                  print(row[0], "is done")
              else:
                  print(row[0], "is NOT done")

    • Modify example.py to use your database connection information in the oracledb.connect() call:

      • User: Use Admin which is the user created by default during the creation of Autonomous AI Database. (If you created another ADB user, you can use that user instead.)
      • Data Source Name (DSN): Use the connection string copied from the Autonomous AI Database console.
    • Save the changes to example.py.
  • 5. Run the Python Application

    Run the app:

    Copy

    python example.py

    After entering the user password, you will see the queried rows returned from the database. Congratulations! You have successfully used Oracle Autonomous AI Database.

    Note: If you connect to ADB from behind a firewall, you may need to add a proxy name and port to the address clause of the connection string similar to: (description= (retry_count=20)(retry_delay=3)(address=(https_proxy=proxy.example.com)(https_proxy_port=80)(protocol=tcps)(. . .)

Now you have completed this tutorial, you should continue with the full Python and Oracle Database Tutorial or Getting Started with Python and Oracle Database LiveLabs Tutorial to learn more about using python-oracledb.

More information and resources on using python-oracledb are available here.

This tutorial shows you how use the Python python-oracledb driver on Linux to connect Python applications to Oracle Autonomous AI Database (ADB). Using a recent version of Linux is recommended.

• • 1. Create an ADB Instance

    Follow the steps below to walk through the process of creating an ADB instance:

    1.1 Create a free Oracle Cloud account.

    1.2 Navigate to the Autonomous AI Database page and click Create Autonomous AI Database.

    1.3 Choose a database name, for example PYDB. Select Transaction Processing as the Workload Type.

    1.3.1 In the Database Configuration section select the Always Free slider if you are elgible for an Always Free instance.

    1.4 In the Administrator Credentials Creation section, set the database ADMIN user password.

    1.5 In the Network Access Type section, select Secure access from allowed IPs and VCNs only, and add your IP address. If your IP address changes in future, you will need to update the database service's access control list.

    Note: The Secure access from everywhere Network Access Type option will use mTLS with a wallet which is not covered in this quickstart. Refer to the python-oracledb driver documentation if you need to use it (for example if you are unable to add your IP address).

    1.6 When the database has been created, go to your Autonomous AI Database detail page and select the Database Connection button and scroll down to view Connection Strings. Select "TLS" in the TLS Authentication drop down and then copy any connection string. Save this connection string for later.

  • 2. Install Python (Optional)

    You may already have Python 3.10 or later installed. If not, see Python for Oracle Linux.

    The steps below assume that the executable is called 'python3'. If you also have Python 2 installed, make sure to use the newer Python 3 binary.

  • 3. Install python-oracledb

    Upgrade Python's pip utility:

    Copy

    python3 -m pip install pip

    Make sure you use the Python 3 executable.

    Adding the pip --user option may useful when you do not have permission to write to system directories. If you are behind an HTTP proxy, you can also add an option like --proxy=http://proxy.example.com:80

    Install python-oracledb:

    Copy

    python3 -m pip install oracledb

    Use the pip --user or --proxy options again if needed.

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

  • 4. 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 getpass
      import oracledb
      
      pw = getpass.getpass("Enter password: ")
      
      connection = oracledb.connect(
          user="admin",
          password=pw,
          dsn="(description=...)")  # the connection string copied from the cloud console
      
      print("Successfully connected to Oracle Database")
      
      # Create a table
      with connection.cursor() as cursor:
      
          cursor.execute("""
              begin
                  execute immediate 'drop table todoitem';
                  exception when others then if sqlcode <> -942 then raise; end if;
              end;""")
      
          cursor.execute("""
              create table todoitem (
                  id number generated always as identity,
                  description varchar2(4000),
                  creation_ts timestamp with time zone default current_timestamp,
                  done number(1,0),
                  primary key (id))""")
      
          print("Table created")
      
      # Insert some data
      with connection.cursor() as cursor:
      
          rows = [ ("Task 1", 0 ),
                   ("Task 2", 0 ),
                   ("Task 3", 1 ),
                   ("Task 4", 0 ),
                   ("Task 5", 1 ) ]
      
          cursor.executemany("insert into todoitem (description, done) values(:1, :2)", rows)
          print(cursor.rowcount, "Rows Inserted")
      
      connection.commit()
      
      # Now query the rows back
      with connection.cursor() as cursor:
      
          for row in cursor.execute('select description, done from todoitem'):
              if (row[1]):
                  print(row[0], "is done")
              else:
                  print(row[0], "is NOT done")

    • Modify example.py to use your database connection information in the oracledb.connect() call:

      • User: Use Admin which is the user created by default during the creation of Autonomous AI Database. (If you created another ADB user, you can use that user instead.)
      • Data Source Name (DSN): Use the connection string copied from the ADB console.
    • Save the changes to example.py.
  • 5. Run the Python Application

    Run the app using the Python 3 executable:

    Copy

    python3 example.py

    After entering the user password, you will see the queried rows returned from the database. Congratulations! You have successfully used Oracle Autonomous AI Database.

    Note: If you connect to ADB from behind a firewall, you may need to add a proxy name and port to the address clause of the connection string similar to: (description= (retry_count=20)(retry_delay=3)(address=(https_proxy=proxy.example.com)(https_proxy_port=80)(protocol=tcps)(. . .)

Now you have completed this tutorial, you should continue with the full Python and Oracle Database Tutorial or Getting Started with Python and Oracle Database LiveLabs Tutorial to learn more about using python-oracledb.

More information and resources on using python-oracledb are available here.