No results found

Your search did not match any results.

We suggest you try the following to help find what you’re looking for:

  • Check the spelling of your keyword search.
  • Use synonyms for the keyword you typed, for example, try “application” instead of “software.”
  • Try one of the popular searches shown below.
  • Start a new search.
Trending Questions
 

Quick Start: Developing Python Applications for Oracle Database

This tutorial shows you how to connect Python applications to Oracle Database 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.

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 cx_Oracle on Windows

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

      Video showing how to install Oracle Database XE on Windows and conduct basic administration.

      Oracle Database XE Installation Guide for Windows

    • Alternatively, if you already have a database but it is on a remote computer, then install the Oracle Instant Client "Basic" and "SQL*Plus" packages from here. Remember to install the VS Redistributable and add the directory to your PATH environment variable, as instructed. You will need to know the connect string for the database, and substitute it in the instructions below.

    • 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. Create a Database User

    We recommend using a new database user for this quick start tutorial.

    Open a command terminal and run the SQL*Plus command line tool, replacing XXXX with the privileged user password that you chose during database installation. If you are not using Oracle Database XE, then also substitute your database's hostname and database service name for "localhost/xepdb1":

    sqlplus -l system/XXXX@localhost/xepdb1

    At the SQL> prompt, enter the following statements (you can choose a different username, if you like):

    define USERNAME = demopython
    
    create user &USERNAME;
    
    alter user &USERNAME
        default tablespace users
        temporary tablespace temp
        quota unlimited on users;
    
    grant create session,
        create view,
        create sequence,
        create procedure,
        create table,
        create trigger,
        create type,
        create materialized view
        to &USERNAME;

    Still in SQL*Plus, set a password for your new user. Replace the XXXX with a valid password and run:

    alter user &USERNAME identified by XXXX;

    Finally, exit SQL*Plus

    quit
  • 3. Install Python

  • 4. Install cx_Oracle

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

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

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

  • 5. 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 cx_Oracle
      
      connection = cx_Oracle.connect(
          user="demopython",
          password="XXXXX",
          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 cx_Oracle.connect() call:

      • User: Use the username created by the SQL*Plus script.
      • Password: Use the user's password.
    • Save the changes to example.py.
  • 6. Run the Python Application

    In a terminal window, run the app:

    python example.py 
                

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

    Python cx_Oracle on macOS (Intel x86)

    Open all Close all
  • 1. Install Oracle Database

    If you do not already have access to an Oracle Database, you can easily install one using VirtualBox. (There is no native macOS port of Oracle Database). 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.

    To install Oracle Database XE, follow the instructions here. In summary:

    • Install Oracle VM VirtualBox for "OS X hosts"

    • Install Vagrant

    • Download and unzip the Oracle Database 18c XE Vagrant configuration ZIP file, or use git to clone the GitHub repository. Open a terminal window and change to the OracleDatabase/18.4.0-XE directory, then run vagrant up

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

  • 2. Install the Oracle Instant Client Basic and SQL*Plus Packages

    Download the free Oracle Instant Client Basic and SQL*Plus DMG files from Instant Client Downloads for macOS (Intel x86).

    Mount both DMG files and run one of their install_ic.sh scripts. This will install both packages at the same time. More details are in the Instant Client installation instructions.

  • 3. Create a Database User

    We recommend using a new database user for this quick start tutorial.

    Open a terminal window and add the Instant Client directory to your path:

    export PATH=$HOME/Downloads/instantclient_19_8:$PATH

    Run the SQL*Plus command line tool, replacing XXXX with the privileged user password that you chose during database installation. If you are not using Oracle Database XE, then also substitute your database's hostname and database service name for "localhost/xepdb1":

    sqlplus -l system/XXXX@localhost/xepdb1

    At the SQL> prompt, enter the following statements (you can choose a different username, if you like):

    define USERNAME = demopython
    
    create user &USERNAME;
    
    alter user &USERNAME
        default tablespace users
        temporary tablespace temp
        quota unlimited on users;
    
    grant create session,
        create view,
        create sequence,
        create procedure,
        create table,
        create trigger,
        create type,
        create materialized view
        to &USERNAME;

    Still in SQL*Plus, set a password for your new user. Replace the XXXX with a valid password and run:

    alter user &USERNAME identified by XXXX;

    Finally, exit SQL*Plus

    quit
  • 4. Install Python

    Install Python 3. The system Python on macOS cannot be used.

  • 5. Install cx_Oracle

    You need a compiler such as Xcode from the app store, and also its command line tools. These can be installed with sudo xcode-select --install

    Install cx_Oracle:

    python3 -m pip install cx_Oracle --upgrade --user 

    Make sure you are using the newly installed Python. Depending on your Python installation, you may need to execute the python command, if this overrides the default Python installation.

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

  • 6. 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 os
      import cx_Oracle
      
      cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME")+"/Downloads/instantclient_19_8")
      
      connection = cx_Oracle.connect(
          user="demopython",
          password="XXXXX",
          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 and set the appropriate path to your Instant Client directory in the init_oracle_client() call.

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

      • User: Use the username created by the SQL*Plus script.
      • Password: Use the user's password.
    • Save the changes to example.py.
  • 7. Run the Python Application

    In a terminal window, run the app:
    python3 example.py 

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

    Python cx_Oracle on Linux

    Open all Close all
  • 1. Install Oracle Database

  • 2. Create a Database User

    We recommend using a new database user for this quick start tutorial.

    In a terminal window, set the environment for accessing Oracle Database:

    export ORACLE_SID=XE
    export ORAENV_ASK=NO
    source /opt/oracle/product/18c/dbhomeXE/bin/oraenv

    This can be added to application or shell configuration files so it is automatically configured in future.

    Run the SQL*Plus command line tool, replacing XXXX with the privileged user password that you chose during database installation. If you are not using Oracle Database XE, then also substitute your database's hostname and database service name for "localhost/xepdb1":

    sqlplus -l system/XXXX@localhost/xepdb1

    At the SQL> prompt, enter the following statements (you can choose a different username, if you like):

    define USERNAME = demopython
    
    create user &USERNAME;
    
    alter user &USERNAME
        default tablespace users
        temporary tablespace temp
        quota unlimited on users;
    
    grant create session,
        create view,
        create sequence,
        create procedure,
        create table,
        create trigger,
        create type,
        create materialized view
        to &USERNAME;

    Still in SQL*Plus, set a password for your new user. Replace the XXXX with a valid password and run:

    alter user &USERNAME identified by XXXX;

    Finally, exit SQL*Plus

    quit
  • 3. Install Python

    Install Python 3. For example, on Oracle Linux 7:

    sudo yum install -y python3
  • 4. Install cx_Oracle

    Install cx_Oracle:
     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.

  • 5. 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 cx_Oracle
      
      connection = cx_Oracle.connect(
          user="demopython",
          password="XXXXX",
          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 cx_Oracle.connect() call:

      • User: Use the username created by the SQL*Plus script.
      • Password: Use the user's password.
    • Save the changes to example.py.
  • 6. Run the Python Application

    Open a terminal window. If you didn't globally set the Oracle environment, then set it again. This lets Python's cx_Oracle interface find the necessary Oracle client libraries that are included with the database software:

    export ORACLE_SID=XE
    export ORAENV_ASK=NO
    source /opt/oracle/product/18c/dbhomeXE/bin/oraenv

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

    python3 example.py 

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