• • 1. Install Oracle Database

    If you do not already have access to an Oracle Database and are using macOS on Intel hardware then you can easily install a database using VirtualBox. If you do not have access to a database and you are using macOS on an M series chip, or if you would like to use an Oracle Autonomous Database in Oracle Cloud (for example from the Always Free service), then see the Developing Python Applications for Oracle Autonomous Database tutorial.

    To install Oracle Database XE when using macOS on Intel hardware, follow the instructions here. In summary:

    • Install Oracle VM VirtualBox for "macOS Intel hosts"
    • Install Vagrant
    • Download and unzip the Oracle Database 21c XE Vagrant configuration ZIP file, or use git to clone the GitHub repository. Open a terminal window and change to the OracleDatabase/21.3.0-XE directory, then run vagrant up

    At the conclusion, Oracle Database will be running. The confirmation message will display the password for the privileged accounts.

  • 2. Install Python (Optional)

    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.8 or later.

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

  • 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="demopython",
          password=pw,
          dsn="localhost/xepdb1")
      
      print("Successfully connected to Oracle Database")
      
      cursor = connection.cursor()
      
      # Create a table
      
      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))""")
      
      # Insert some data
      
      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
      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 your database username.
      • Data Source Name (DSN): If you have a different database, put its connection string here.
    • Save the changes to example.py.
  • 5. Run the Python Application

    In a terminal window, run the app:

    Copy

    python example.py

    After entering the user password, you should see a message that you connected to the database, five rows were inserted, and the task list with each task's completion status returned to the terminal window. Congratulations! You have successfully queried the Oracle Database.

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.

Using a recent version of Linux such as Oracle Linux 8 is recommended.

• • 1. Install Oracle Database

    • If you do not already have access to an Oracle Database, then download and install Oracle Database XE following these instructions.

      More resources: Oracle Database XE Installation Guide for Linux

    • Alternatively, if you would like to use an Oracle Autonomous Database in Oracle Cloud instead (for example from the Always Free service), then see the Developing Python Applications for Oracle Autonomous Database tutorial.

  • 2. Install Python (Optional)

    You may already have Python 3 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

    Install python-oracledb:

    Copy

    python3 -m pip install oracledb

    Make sure you use the python3 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

    For further assistance and options, 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="demopython",
          password=pw,
          dsn="localhost/xepdb1")
      
      print("Successfully connected to Oracle Database")
      
      cursor = connection.cursor()
      
      # Create a table
      
      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))""")
      
      # Insert some data
      
      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
      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 your database username.
      • Data Source Name (DSN): If you have a different database, put its connection string here.
    • Save the changes to example.py.
  • 5. Run the Python Application

    In a terminal window, run the app using the Python 3 executable:

    Copy

    python3 example.py

    After entering the user password, you should see a message that you connected to the database, five rows were inserted, and the task list with each task's completion status returned to the terminal window. Congratulations! You have successfully queried the Oracle Database.

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.