Oracle REST Data Services for Python Developers—Part 1: Introduction to Oracle REST Data Services
This article is the introductory part of a three-part series that explains, by example, how Python developers can take advantage of Oracle REST Data Services when developing REST-based applications that consume Oracle Database data.
by Yuli Vasiliev

This series explains how to develop Representational State Transfer (REST) interfaces for Oracle Database data using Oracle REST Data Services, a mid-tier Java application that can be deployed to a Java EE application server or run in standalone mode. The examples provided illustrate how you can create a RESTful service on a database table or view and then consume that service from within Python code. Working through the examples, you will see different Oracle REST Data Services options in action. In particular, you will learn how an Oracle REST Data Services RESTful service can be created with just a few clicks and how you can manually build it, maintaining the necessary level of flexibility and customizability.

This part provides a brief overview of Oracle REST Data Services, as well as how to get started with it and quickly learn how to build on its foundations. In particular, it covers how to develop a RESTful service, using Oracle SQL Developer, Oracle REST Data Services, and Oracle Database, and then test it from Python. Here are the main topics covered in this article:

  • Why REST?
  • A brief overview of Oracle REST Data Services
  • Preparing your working environment
  • Installing the Database Virtual Box Appliance for Oracle VM VirtualBox
  • Installing and administering Oracle REST Data Services
  • Enabling REST access to a database table with Oracle REST Data Services
  • Testing an Oracle REST Data Services RESTful service in Python
Why REST?

REST is an architectural style used in web development to unify the way in which applications interact with their data sources. To access and manipulate data, REST-based applications use a standard set of HTTP request methods, including GET, PUT, POST, and DELETE.

The REST architecture assumes that an application does not interact with its underlying data source directly, but uses a RESTful web service in between. Such a service receives a request from the application, maps it to the corresponding operation performed against the underlying data source, and sends the retrieved results—if the operation is supposed to retrieve results—back to the application.

So, the implementation of data-handling operations is specific to an underlying data source and is defined in a RESTful service. It is fairly obvious that this design enables developing highly portable applications. You can switch from one data source to another without having to modify your application code.

A Brief Overview of Oracle REST Data Services

Oracle REST Data Services provides an easy way to build RESTful services that enable REST access to data in Oracle Database, an Oracle Database 12c JSON document store, and Oracle NoSQL Database. Being a Java EE–based application, Oracle REST Data Services can be deployed to a Java EE application server, including Oracle WebLogic Server, GlassFish Server, and Apache Tomcat. You also have the option of running Oracle REST Data Services in standalone mode.

To REST-expose database data with Oracle REST Data Services, you have the following options to choose from:

  • Automatic REST enabling (AutoREST) of database objects
  • Manually creating RESTful services, using SQL and PL/SQL operations

The first option provides a quick way to define a RESTful service on a database object, such as a table or view, or a PL/SQL function, procedure, or entire package. With AutoREST, Oracle REST Data Services automatically maps HTTP verbs (GET, POST, PUT, DELETE, and so on) to data operations allowed on a database object you want to REST-enable, thus automatically exposing it to HTTP.

Creating a RESTful service using the second option, although a more time-consuming process, provides you with more flexibility and customizability. For example, it allows you to do extra validation and customize input or output data formats, explicitly specifying SQL or PL/SQL for each HTTP method you define for a specific resource template. Figure 1 gives a simplified depiction of how this architecture works:

Making REST calls to Oracle Database with Python via Oracle REST Data Services.
Figure 1: Making REST calls to Oracle Database with Python via Oracle REST Data Services.

If you choose to implement the architecture shown in Figure 1, you'll need to create a resource module in Oracle REST Data Services, within which you can define a group of related resource templates (individual RESTful services)and implement a set of necessary HTTP method handlers for each resource template (RESTful service). A detailed example of how this can be accomplished will be provided in the next article of this series.

Note: For definitions of specific terms related to RESTful services, such as resource template and method handler, check the "RESTful Services Terminology" section in the REST Data Services Installation, Configuration, and Development Guide.

In this article, you will see an example of a simpler alternative that uses the AutoREST feature. As mentioned, with AutoREST, Oracle REST Data Services automatically creates REST endpoints for a specified database object, mapping HTTP verbs to database transactions behind the scenes for you.

Required Software

Before moving on to the article's example, you will need to prepare your working environment. In particular, make sure you have access to the following software components in your system:

  • Oracle Database 12.2 (or newer)
  • Oracle SQL Developer 4.2 (or newer)
  • Oracle REST Data Services 3.0.9 (or newer)
  • Python 3.6.x (or newer)

With the Database Virtual Box Appliance for Oracle VM VirtualBox, the above software stack can be installed on your machine extremely easily. The appliance comes with these components preinstalled, configured, and ready to run. The only component missing in the appliance is Python 3.6. You may install it as described in the "Installing Python on Oracle Linux" section later in this article, or you can use the Python installation you may already have in your system.

Note: The Database Virtual Box Appliance discussed here is installed into Oracle VM VirtualBox, which is available for a number of operating systems, including Microsoft Windows, macOS, Oracle Solaris, and a number of Linux platforms. For further details, check the Oracle VM VirtualBox downloads page.

Alternatively, the above Oracle software components can be downloaded from Oracle Technology Network (OTN) and then installed in your system. Another option is to take advantage of Oracle Database in a cloud, choosing one of the Oracle Database Cloud Services that normally come preconfigured with Oracle REST Data Services.

Installing and Deploying Oracle REST Data Services

Oracle REST Data Services comes packed in a WAR file (ords.war) with both Oracle Database and Oracle SQL Developer. Or you can download the latest package from the Oracle REST Data Services Downloads page on OTN. In any case, before you can make use of Oracle REST Data Services, you must install and deploy it.

Note: If you choose to download and install the latest package, make sure that your system meets the requirements listed in the documentation for this release.

Once you have the WAR file in your system, you can install and configure Oracle REST Data Services. This can be done using a command-line interface. The following command starts the simple installation that requires minimum user input:

java -jar ords.war

To perform an advanced installation, use the following command:

java -jar ords.war install advanced

The entire installation process is described in detail in the "Installing Oracle REST Data Services" section of the Oracle REST Data Services Installation, Configuration, and Development Guide.

However, the quickest way to install Oracle REST Data Services is with the help of Oracle SQL Developer's ORDS Installation Wizard, which can be invoked by selecting the Tools > REST Data Services > Install menu item in Oracle SQL Developer.

The first screen of the ORDS Installation Wizard in Oracle SQL Developer.
Figure 2: The first screen of the ORDS Installation Wizard in Oracle SQL Developer.

The entire installation process is described in detail in the "Installing and Administering REST Data Services with SQL Developer" hands-on lab on OTN.

Installing Python on Oracle Linux

The Database Virtual Box Appliance mentioned previously uses Oracle Linux as the operating system. So, if you chose this appliance for your working environment, you may want to install Python in the virtual machine built in Oracle VM VirtualBox from this appliance.

The following steps will guide you through the process of installing Python from the source code on Oracle Linux. These same steps can also be used on CentOS, RHEL, and Fedora.

1. You must have the GCC compiler in your system to build Python from the source code. You can download and install it with yum, as follows:

# yum install gcc openssl-devel bzip2-devel

2. Download a gzipped source tarball of the latest Python 3 release:

# cd /usr/src
# wget https://www.python.org/ftp/python/3.6.4/Python-3.6.4.tgz

3. Unpack the package:

# tar xzf Python-3.6.4.tgz

4. Compile the source code and install Python:

# cd Python-3.6.4
# ./configure --enable-optimizations
# make altinstall

The altinstall option is used to avoid overwriting the default Python executable used in your system.

5. Locate the newly installed Python executable:

# which python3.6
/usr/local/bin/python3.6

6. Launch the Python prompt to make sure it works:

# python3.6
Python 3.6.4 (default, Dec 20 2017, 20:48:42) 
[GCC 4.4.7 20120313 (Red Hat 4.4.7-18)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>

Now that you have installed Python 3, you need to install Requests, a Python HTTP library that simplifies working with HTTP requests, providing an easy-to-use interface to make REST calls from Python. You can use it as your primary tool when it comes to writing Python code that consumes RESTful services.

There are several ways in which you can install the Requests library. The steps below describe how you can install Requests from the source code:

1. Download the Requests library as the source code from GitHub, where it is actively developed:

https://github.com/requests/requests/tarball/master

2. Unpack the downloaded gzipped source tarball in your system and run the cd command to change to the unpacked source directory.

3. Install the request library using the pip command:

$ pip3.6 install .

4. After the installation completes, check to make sure it was successful:

$ python3.6

...

>>> import requests
>>>

This completes the process of setting up your working environment. And now you are ready to proceed to create the article's sample.

Building a RESTful Service with Oracle REST Data Services

By following the instructions in this section, you will create a RESTful service upon a database table, using the AutoREST feature of Oracle REST Data Services. In particular, you will REST-expose the departments table from the HR sample schema that comes with Oracle Database.

Note: As mentioned earlier in this article, AutoREST provides a no-code solution for exposing database data to HTTP. And if you want more control, then you can roll your own resource modules. The latter option will be covered in detail in the third article of this series.

Follow these steps to AutoREST-enable the hr.departments table with Oracle SQL Developer:

1. In Oracle SQL Developer, move to the Connections pane and find the HR schema. If you do not see this schema there, you have to create a new connection for it:

In the Connections pane, right-click the Connections node and select New Connection.

In the New / Select Database Connection dialog box, enter the required information (if you are using the VM appliance, all passwords are set to oracle):

Figure 3: Setting up a connection to the HR schema in Oracle SQL Developer.
Figure 3: Setting up a connection to the HR schema in Oracle SQL Developer.

After filling in the dialog box's fields, click Test to test the newly created connection. If it is successful, click Save.

2. In the Connections pane, find and expand the HR schema.

3. Under HR schema node, expand Tables(Filtered). Then, right-click DEPARTMENTS and select Enable REST Service from the pop-up menu to invoke the RESTful Services Wizard:

Figure 4: Auto-enabling a database object with the RESTful Services Wizard in Oracle SQL Developer.
Figure 4: Auto-enabling a database object with the RESTful Services Wizard in Oracle SQL Developer.

4. On the first screen of the wizard, select the Enable object checkbox and deselect the Authorization required checkbox, as shown in the above figure.

5. On the Summary screen of the wizard, view the summary information, and click Finish.

Before that, however, you can look at the PL/SQL code that will be executed behind the scenes when you click Finish. To see it, click the SQL tab in the Summary screen. You should see the following:

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_object => 'DEPARTMENTS',
                       p_object_type => 'TABLE',
                       p_object_alias => 'departments',
                       p_auto_rest_auth => FALSE);
    
    commit;

END;

6. Finally, check to see if the Oracle REST Data Services process is running. You should see it in the Processes pane. If you do not see it there, run it using the ORDS Run Wizard, which can be launched by selecting the Tools > REST Data Services > Run menu item.

Note: The ORDS Installation Wizard discussed in the "Installing and Deploying Oracle REST Data Services" section earlier has the Run Standalone Mode screen, where you can select the Run in Standalone Mode when installation completes checkbox. If you did so, Oracle REST Data Services should already be running. However, the next time you load your system, you need to explicitly run Oracle REST Data Services.

That's it. A RESTful service upon the hr.departments database table is created and you are now ready to make an HTTP request to it, to make sure it works. The quickest way to test a RESTful service is with a web browser. So, point your browser to the following location:

http://yourdbserver:9090/ords/hr/departments/

This assumes that 9090 is used as the HTTP port for Oracle REST Data Services to listen on in your system.

Figure 5 shows what you should see as a result:

Figure 5: Using a web browser to quickly test the RESTful service.
Figure 5: Using a web browser to quickly test the RESTful service.

Figure 5 indicates that the RESTful service you created works as expected and you can now test it from your Python environment. Before moving on to Python, however, let's take a closer look at what just happened. Oracle REST Data Services received a GET request on /ords/hr/departments/ from the browser and issued the SELECT * FROM HR.DEPARTMENTS query to the underlying database in response. The query result was then converted to JSON and sent back to the browser.

Testing Your RESTful Service in Python

In this simple test, you will make a GET HTTP request to the RESTful service you just created. Using the Request library, this can be done very easy:

>>> import requests
>>> r = requests.get('http://yourdbserver:9090/ords/hr/departments/')

Then, you can convert the result to JSON and output it to see exactly the same as what you saw in your browser before:

>>> import json
>>> jo = r.json()
>>> print(json.dumps(jo, indent=4))

The output should be the following:

{
    "items": [
        {
            "department_id": 10,
            "department_name": "Administration",
            "manager_id": 200,
            "location_id": 1700,
            "links": [
                {
                    "rel": "self",
                    "href": "http://yourdbserver:9090/ords/hr/departments/10"
                }
            ]
        },
        {
            "department_id": 20,
            "department_name": "Marketing",
            "manager_id": 201,
            "location_id": 1800,
            "links": [
                {
                    "rel": "self",
                    "href": "http://yourdbserver:9090/ords/hr/departments/20"
                }
            ]
        },

...
Conclusion

This article is designed to help you get started quickly with Oracle REST Data Services. Following the instructions provided, you learned how easy it is to build an Oracle REST Data Services RESTful service to expose Oracle Database data to HTTP and then consume the service from Python.

See Also
About the Author

Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, databases, business intelligence (BI), machine learning, and virtualization. He is the author of a series of books on Oracle technology, including Oracle Business Intelligence: An introduction to Business Analysis and Reporting (Packt Publishing) and PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax (Packt Publishing).

Join the Database Community Conversation