Quick Start: Developing Python Applications for Oracle Database

This tutorial shows you how to connect Python applications to Oracle Database using the python-oracledb interface. This interface lets you quickly develop applications that execute SQL or PL/SQL statements, allowing you to work with many data types including JSON. Your applications can also use Oracle's document storage SODA calls. Python-oracledb is the new name for Oracle's popular cx_Oracle driver. It conforms to the Python Database API v2.0 Specification with a considerable number of additions and a couple of exclusions.

The steps below show connecting to an on-premise database, but 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.

Python python-oracledb on Windows

Open all Close all
    • 1. Install Oracle Database
    • 2. Install Python
    • 3. Install python-oracledb
      Install python-oracledb:
      python -m pip install oracledb

      Depending on your Python installation, you may need to execute the python3 command instead.

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

    • 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:

        
        
        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:

      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.

Python python-oracledb on macOS

Open all Close all
    • 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:

      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:

        
        
        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:

      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.

Python python-oracledb on Linux

Open all Close all

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

    • 1. Install Oracle Database
    • 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:

      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:

        
        
        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:

      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.