Django is a Python-based full-featured web framework that follows the model–template–view (MTV) architectural pattern and is often chosen to simplify the development of database-backed web applications. In Django, a model (often referred to as a data model) is implemented as a Python class and is used to map to a single database table. Once you’ve created your data models, you can use Django’s database-abstraction API to access and manipulate data in the mapped database tables in a pythonic way. This article discusses how you can interact with your database from a Django app, like you would with SQL, querying and modifying database-table data through Python objects. In particular, you’ll be guided through the process of building a simple sample Django application that uses Oracle Database to collect and retrieve stock market data.

Many web applications provide information in response to user requests, using one or more public APIs behind the scenes. For example, you might have an application that gives you the high and low prices of a stock within a specified period of time (say, during the last 10 minutes) requesting that the Yahoo Finance API get live market data. To avoid unnecessary burden on the underlying API (it may have restrictions on the number of requests), such an application might request and send live data to a database in the background according to a schedule, and then use the database data to fulfill user requests.

Although Django is a web framework, you can still create a Django application that doesn’t use web pages in the front end to interact with the back end. The sample application will illustrate how you can implement such a simple application as it interacts with the persistence tier of a Django application from within an interactive Python shell.

This article assumes that you have some knowledge of how to use Python and access Oracle Database.

Installing Django

Since Django is a Python framework, make sure you have a compatible Python release installed on your machine. One simple way to check that is to type python –version at a command prompt. You might also take advantage of the whereis python command if you have more than one installation of Python. Before installing Django, check Django’s FAQ: Installation page to see if your installed Python version can be used with Django. If you need to update Python (if it’s outdated), visit the Python downloads page.

To install Python, type the following command, which uses pip, the Python interactive interpreter:

$ pip install Django

The installation process may take a few seconds to complete. To make sure the installation has been successful, type the following command:

>>> import Django

If you don’t see any error messages, the installation was successful.

Preparing to interact with Oracle Database

As of mid-September 2021, the latest stable (“documentation”) release of Django, which is version 3.2, supported Oracle Database 12.2 and newer. Django requires the cx_Oracle Python driver version 6.0 or higher to interact with the database. You can install the latest release of cx_Oracle using the pip command, as follows:

$ pip install cx_Oracle

For additional details on how to install cx_Oracle, refer to the installation page. Also make sure that the Oracle Database user you’re going to use in Django has the privileges specified in the Oracle notes.You can create such a user in your database by using the following SQL statements that you can execute in a SQL tool such as Oracle SQL*Plus or Oracle SQL Developer (using an account with SYSDBA privileges):

SQL> CREATE USER djangousr
       IDENTIFIED BY pswd
       DEFAULT TABLESPACE users
       QUOTA unlimited ON users;

User created.

SQL> GRANT create session, create table, create sequence, create procedure, create trigger TO djangousr;
Grant succeeded.

You don’t need to create tables for your data in the newly created schema. Although mapping to existing database tables is possible, Django can create those tables for you from the data models (Python objects) that you define in your Python code. (This is discussed later.)

Creating a Django project: Initial setup

Quick terminology: When talking about Django, an app is a web application, and a project is a collection of configuration and apps. A single project may include multiple apps. When creating a project, the first step is to create a folder for housing it.

$ mkdir djangoprojs
$ cd djangoprojs

Now that you are in the newly created folder, invoke the django-admin.py utility, specifying a parameter startproject followed by your custom name for the project. In this case, the name is dbproj.

$ django-admin.py startproject dbproj

The previous command will generate a dbproj folder with the dbproj subfolder in it, along with the following files within them: manage.py, dbproj/__init__.py, dbproj/settings.py, dbproj/urls.py, dbproj/asgi.py, and dbproj/wsgi.py. While each of these files plays a particular role in the project, within the framework of this discussion, these are the most interesting.

• manage.py is a command-line utility for administrative tasks within this particular project.
• dbproj/settings.py contains the project’s settings and configuration, such as database connection information, time zone, and language code, as well as information about the applications installed in the project.
• dbproj/__init__.py indicates that this directory is a Python package, thus enabling you to refer to the project’s components with the dot notation, such as dbproj.settings.

All the files composing the initial setup of a Django project are Python source code files, allowing you to view and edit them as necessary.

Setting Oracle Database as the default

Before proceeding to create an application within the newly created project, you might want to set Oracle Database as the default for the project. For that, you’ll need to update the database information in the dbproj/settings.py configuration file. Find the following setting entry in the file:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}

Replace the SQLite database settings with your Oracle Database settings, thus setting Oracle Database as the default, as follows:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'orcl',
        'USER': 'djangousr',
        'PASSWORD': 'pswd',
        'HOST': '',
        'PORT': '',
    }
}

Note that you don’t have to specify HOST and PORT fields, as long as you have a corresponding entry (orcl, in this particular example) in the tnsnames.ora database configuration file.

Testing the setup

At this stage, you should double-check the initial setup of your project, including the database settings just made. This can be easily done with the help of the development server, a lightweight web server that ships with Django and is intended for use while developing code, not for use in a production environment. To run this server, use the manage.py utility invoked with the runserver parameter. In a terminal, change to the outer dbproj folder generated by django-admin.py previously and issue the following command:

$ python manage.py runserver

In the previous command you must specify the executable of that Python installation in which you’ve installed Django. For example, it might be python, as on my system; in others, it might be something else such as python3.

Upon starting, the development server will perform some system checks. If no issues are identified, you will see several messages implying that the server is now running and ready for use. You may also see a warning about unapplied migrations and be advised to run python manage.py migrate to apply them. This is discussed shortly.

In the meantime, you can point your web browser to http://127.0.0.1:8000/. If everything is good, you should see a message that says, “The install worked successfully!” in your browser. Then, come back to the system terminal where you launched the server and quit the development server by pressing CTRL+C.

Propagating the project’s initial data into the database

Although you have not created custom data structures—or even an application—you already have the project’s initial administrative data to be propagated into the database. That’s the meaning of the unapplied migration warning that you got upon launching the development server. To apply these migrations, use the manage.py utility invoked with the migrate parameter, as follows:

$ python manage.py migrate

As the objects are created in the database, the following messages will appear in the terminal window:

  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
 ...

As can be seen from the first message, the migrations being applied are required by the admin, auth, contenttypes, and sessions applications. These applications come with Django by default; the migrate command finds the names of these applications in the INSTALLED_APPS setting in the settings.py configuration file and creates any necessary database tables for them.

If a migration is applied successfully, you’ll see OK on the right. All migrations should be applied successfully. To learn which tables have been created on the database side after the migrations, issue the following SQL statement in your database console:

SQL> SELECT table_name FROM all_tables WHERE owner = 'DJANGOUSR';

This statement should output dozens of table names. Some of these tables will already contain some data; for example, the AUTH_PERMISSION table will contain a couple of dozen rows. Many other tables will be empty at the moment.

Creating a Django application

Now that you have completed the initial setup of the project, the following step is to create an application in it. Create an application skeleton by invoking the manage.py utility with the startapp parameter from the terminal, followed by a custom name for the application. In this case, the name will be dbapp.

$ python manage.py startapp dbapp

On Linux, you may need to do the following:

$ chmod +x manage.py
$ python ./manage.py startapp dbapp

If everything works correctly, this command generates no messages. You can check, however, to see that it generated a folder named dbapp that contains the following files:

• __init__.py is generated to mark this application folder as a Python package.
• admin.py is used to display your application models in the Django admin panel.
• apps.py contains configuration data for the application.
• models.py holds the application’s models; these are Python classes that you build on top of the django.db.models.Model class, each of which maps to a single database table.
• tests.py can be used to construct a test suite for your application.
• views.py is designed to hold views; these are Python functions that return an HttpResponse object with the content to be displayed on the requested page, or which or raises an exception.

At the moment, you have the application skeleton generated for you by the manage.py utility. Now you can customize this skeleton as needed. Depending on what you want your application to do, you might need to create models, templates, and views. You will also need to configure URL patterns in urls.py.

You also need to register the dbapp application in the dbproj project; this is not done automatically. The manage.py utility, which is used to perform the project’s administrative tasks, such as propagating changes to the database, looks at the INSTALLED_APPS setting in the settings.py configuration file to determine which applications are installed in this Django project.

Tie the newly created application to the project by appending dbapp to the INSTALLED_APPS setting in the project’s settings.py file, so that it looks like the following:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'dbapp',
]

Building models for Django

In Django, each model is implemented as a Python class that inherits from django.db.models.Model and is mapped to a certain database table. Define a model’s attributes to represent the fields in the underlying database table. For the sample financial application being discussed, you’ll need a single model defined as follows in the dbapp/models.py file:

class stocks(models.Model):
  date_time = models.DateTimeField(primary_key=True)
  price_low = models.FloatField()
  price_high = models.FloatField()

Migrate this model definition to the database, creating a table in accordance with the set of fields defined. To accomplish this, you first need to create a corresponding migration. This can be done by invoking manage.py with the makemigrations parameter, as follows:

$ python manage.py makemigrations dbapp

The output should look as follows:

Migrations for 'dbapp':
  dbapp/migrations/0001_initial.py
    - Create model stocks

If you want to look at the SQL statement generated for a migration, you can see it by using the following command:

$ python manage.py sqlmigrate dbapp 0001

The output contains the SQL statement to be executed when the migration is applied.

--
-- Create model stocks
--
CREATE TABLE "DBAPP_STOCKS" ("DATE_TIME" TIMESTAMP NOT NULL PRIMARY KEY,
                             "PRICE_LOW" DOUBLE PRECISION NOT NULL,
                             "PRICE_HIGH" DOUBLE PRECISION NOT NULL);

As you can see, this is a very simple implementation intended to hold a time series with the low and high prices of a single stock. Apply the migration by using manage.py.

$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, background_task, contenttypes, dbapp, sessions
Running migrations:
  Applying dbapp.0001_initial... OK

As a result, the SQL statement shown previously is executed against the database, creating the table for the stocks model.

Performing background tasks within the application

Now that the stocks model is mapped to a table in the underlying database, the next task adds a mechanism for obtaining and storing live market data to be used for fulfilling requests from the application’s users. Since that data must be gathered continuously, this task should be implemented as a background task. For that, take advantage of the Django Background Tasks library, which can be installed with pip.

$ pip install django-background-tasks

Add the background_task entry to the INSTALLED_APPS setting in the project’s settings.py file, as follows:

INSTALLED_APPS = [

    ...

    'background_task',
    'dbapp',
]

This change requires a migration to be applied because the background_task module will use the database to hold this new information. Run manage.py with the migrate parameter again.

$ python manage.py migrate

In the Django Background Tasks work queue, all tasks are implemented as functions decorated by @background() and placed in a file named tasks.py in the application folder. Create the file in the dbapp folder and edit it, as follows:

#dbapp/tasks.py
from background_task import background
from dbapp.models import stocks
import yfinance as yf

@background(schedule=10)
def get_stock():
    try:
     data = yf.download(tickers='TSLA', period='3m', interval='1m')
     data = data.reset_index()
     dt = data.iloc[0]['Datetime'].replace(second=0).replace(microsecond=0)
     low = data.iloc[0]['Low']
     high = data.iloc[0]['High']
     new_entry = stocks(date_time=dt, price_low=low, price_high=high)
     new_entry.save()
    except:
     pass
get_stock(repeat=60, repeat_until=None)

In the above code, note the use of the yfinance module, a Python wrapper for the Yahoo Finance API that provides historical and real-time data for stock quotes. The most recent releases of yfinance (version 0.1.63 at the time of this writing) let you get live stock-market data with less than a second of lag for free. You can install yfinance with pip.

$ pip install yfinance

This particular example uses yfinance to get real-time data for the TSLA (Tesla, Inc.) ticker. You, of course, may choose any other ticker, such as ORCL. You set the fields of a stocks model instance to the TSLA ticker live data of the last minute, and then save this entry to the database (to the dbapp_stocks table, to be precise). Since the application stores the seconds and microseconds from an obtained entry and the dbapp_stocks table uses the date_time field as the primary key, you won’t be able to save more than one entry obtained within the same minute. Failure to insert an entry won’t cause a problem because you use a try/except block here.

Testing the finance application

Because the code is designed to process live data, the best times to test the application are the trading hours for the US stock market: 9:30 a.m. to 4:00 p.m. Eastern time on weekdays, excluding US holidays.

To test the above code, you need to run the background tasks in the Django project being discussed. Do this with the following command:

$ python manage.py process_tasks

After a few minutes, check if everything works as expected; that is, verify that the stock data is being collected and saved to the database. A quick way to check is to use the SQL command-line tool, connect to the database as djangousr, and issue the following SQL query:

SQL> SELECT * FROM dbapp_stocks WHERE ROUND(date_time, 'DDD') >= SYSDATE ORDER BY date_time DESC;

The condition in the WHERE clause restricts the rowset to include only today’s rows. In the ORDER BY clause, specify DESC to get the results in descending order so that the more recent rows are first in the retrieved rowset. The generated output should look like the following test run on July 28, 2021:

DATE_TIME                     PRICE_LOW   PRICE_HIGH
----------------------------  ----------  ----------
28-JUL-21 02.17.00.000000 PM  651.22998   652.599976
28-JUL-21 02.16.00.000000 PM  649.919983  652.630005
28-JUL-21 02.15.00.000000 PM  649.747009  650.830017
28-JUL-21 02.14.00.000000 PM  649.119995  651.200012
28-JUL-21 02.13.00.000000 PM  650.590027  651.559998

A better way: Using Django’s QuerySet API

It’s not a best practice to issue direct data read/write requests to a database. A more natural, safer, and more scalable way to access and manipulate database data in Django is via corresponding model objects. You can derive data from a model using its objects attribute, which provides access to the QuerySet API methods, allowing you to query data in the model’s underlying table. To test this functionality, take advantage of the interactive Python shell invoked with manage.py. This invocation of the Python shell gives Django the import path to the dbapp/settings.py file.

$ python manage.py shell

Implement the SQL statement from the previous section with the QuerySet API, as follows:

>>> from dbapp.models import stocks
>>> import datetime
>>> today = datetime.date.today()
>>> stock_list = stocks.objects.filter(date_time__gte=today).order_by('-date_time')

Print the results with the following:

>>> for s in stock_list:
      print(s.date_time, s.price_low, s.price_high)

This command should output the same rowset as in the previous section. Suppose now you want to determine the highest price for the last four minutes. This can be implemented with the following lines of code:

>>> stock_list = stock_list[:4]
>>> from django.db.models import Max
>>> stock_list.aggregate(Max('price_high'))

Here’s the output.

{'price_high__max': 652.6300048828125}

Conclusion

By following these steps to create a simple Django application that interacts with Oracle Database, you learned to do the initial setup of such an application and then customize it to your needs. In particular, you saw how to create a database schema for a Django application and then automatically create tables in it, using the data model objects defined in Python. Then, you learned to implement a background task that continuously collects data from a publicly available API, storing that data to the database. Finally, you saw how to access database data, using Django’s QuerySet API.

Illustration: Wes Rowell