MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

OracleDB Python Tutorial 1

without comments

This shows you how to get Python working with the Oracle Database 23c in Docker or Podman on Ubuntu. You can find useful connection strings for this in Oracle Database Free Get Started.

  1. First step requires you to install the pip3/span> utility on Ubuntu.

    sudo apt install -y python3-pip

  2. Second step requires that you pip3 install the oracledb library:

    sudo pip3 install oracledb --upgrade

  3. Third step requires you write a Python program to test your connection to Oracle Database 23c Free, like:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    
    #!/usr/bin/python
     
    # Import the Oracle library.
    import oracledb
     
    try:
      # Create a connection to local Docker or Podman installation.
      db = oracledb.connect(user='c##student', password='student', dsn='localhost:51521/FREE')
     
      # Print a connection message.
      print("Connected to the Oracle", db.version, "database.")
     
    except oracledb.DatabaseError as e:
      error, = e.args
      print(sys.stderr, "Oracle-Error-Code:", error.code)
      print(sys.stderr, "Oracle-Error-Message:", error.message)
     
    finally:
      # Close connection. 
      db.close()

    The 51521 port is the recommended port when setting up Docker or Podman services, however, it can be set to any port above 1024.

    It should print:

    Connected to the Oracle 23.3.0.23.9 database.
  4. Fourth step requires you write a Python program to test querying data from an Oracle Database 23c Free instance. I created the following avenger table and seeded it with six Avengers.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    
    /* Conditionally drop the table. */
    DROP TABLE IF EXISTS avenger;
     
    /* Create the table. */
    CREATE TABLE avenger
    ( avenger_id      NUMBER
    , first_name      VARCHAR2(20)
    , last_name       VARCHAR2(20)
    , character_name  VARCHAR2(20));
     
    /* Seed the table with data. */
    INSERT INTO avenger VALUES (1,'Anthony','Stark','Iron Man');
    INSERT INTO avenger VALUES (2,'Thor','Odinson','God of Thunder');
    INSERT INTO avenger VALUES (3,'Steven','Rogers','Captain America');
    INSERT INTO avenger VALUES (4,'Bruce','Banner','Hulk');
    INSERT INTO avenger VALUES (5,'Clinton','Barton','Hawkeye');
    INSERT INTO avenger VALUES (6,'Natasha','Romanoff','Black Widow');

    Then, I extended the program logic to include a cursor and for loop to read the values from the avenger table:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    
    #!/usr/bin/python
     
    # Import the Oracle library.
    import oracledb
     
    try:
      # Create a connection to local Docker or Podman installation.
      db = oracledb.connect(user='c##student', password='student', dsn='localhost:51521/FREE')
     
      # Create a cursor.
      cursor = db.cursor()
     
      # Execute a query.
      cursor.execute("SELECT   character_name " +
                     ",        first_name " +
                     ",        last_name " +
                     "FROM     avenger " +
                     "ORDER BY character_name")
     
      # Read the contents of the cursor.
      for row in cursor:
        print(row[0] + ':',row[2] + ',',row[1])
     
    except oracledb.DatabaseError as e:
      error, = e.args
      print(sys.stderr, "Oracle-Error-Code:", error.code)
      print(sys.stderr, "Oracle-Error-Message:", error.message)
     
    finally:
      # Close cursor and connection.
      cursor.close() 
      db.close()

    The 51521 port is the recommended port when setting up Docker or Podman services, however, it can be set to any port above 1024.

    It should print:

    Black Widow: Romanoff, Natasha
    Captain America: Rogers, Steven
    God of Thunder: Odinson, Thor
    Hawkeye: Barton, Clinton
    Hulk: Banner, Bruce
    Iron Man: Stark, Anthony
  5. Fifth step requires you write a Python program to test querying data filtered by a local variable from an Oracle Database 23c Free instance. This example looks only for the Hulk among the six Avengers.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    
    #!/usr/bin/python
     
    # Import the Oracle library.
    import oracledb
     
    try:
      # Create a connection to local Docker or Podman installation.
      db = oracledb.connect(user='c##student', password='student', dsn='localhost:51521/FREE')
     
      # Create a cursor.
      cursor = db.cursor()
     
      # Execute a query.
      stmt = "SELECT   character_name "            \
             ",        first_name "                \
             ",        last_name "                 \
             "FROM     avenger "                   \
             "WHERE    character_name = :avenger " \
             "ORDER BY character_name"
     
      # Execute with bind variable.
      cursor.execute(stmt, avenger = "Hulk")
     
      # Read the contents of the cursor.
      for row in cursor:
        print(row[0] + ':',row[2] + ',',row[1])
     
    except oracledb.DatabaseError as e:
      error, = e.args
      print(sys.stderr, "Oracle-Error-Code:", error.code)
      print(sys.stderr, "Oracle-Error-Message:", error.message)
     
    finally:
      # Close cursor and connection. 
      cursor.close() 
      db.close()

    It should print:

    Hulk: Banner, Bruce

As always, I hope this puts everything together for setting up Python with Oracle Database 23c Free.

Written by maclochlainn

December 10th, 2023 at 12:27 am