Skip to content

Latest commit

 

History

History
489 lines (343 loc) · 16.4 KB

build-a-python-app-with-cockroachdb-django.md

File metadata and controls

489 lines (343 loc) · 16.4 KB
title summary toc twitter
Build a Python App with CockroachDB and Django
Learn how to use CockroachDB from a simple Django application.
true
false

{% unless site.cockroachcloud %}

{% endunless %}

This tutorial shows you how build a simple Python application with CockroachDB and the Django framework.

CockroachDB supports Django versions 2.2 and 3.0.

{% unless site.cockroachcloud %}

Before you begin

{% include {{page.version.version}}/app/before-you-begin.md %}

{% endunless %}

{{site.data.alerts.callout_info}} The example code and instructions on this page use Python 3 and Django 3.0. {{site.data.alerts.end}}

Step 1. Install Django and the CockroachDB backend for Django

Install Django:

{% include copy-clipboard.html %}

$ python -m pip install django==3.0.*

Before installing the CockroachDB backend for Django, you must install one of the following psycopg2 prerequisites:

  • psycopg2, which has some prerequisites of its own. This package is recommended for production environments.

  • psycopg2-binary. This package is recommended for development and testing.

After you install the psycopg2 prerequisite, install the CockroachDB Django backend:

{% include copy-clipboard.html %}

$ python -m pip install django-cockroachdb==3.0.*

{{site.data.alerts.callout_info}} The major version of django-cockroachdb must correspond to the major version of django. The minor release numbers do not need to match. {{site.data.alerts.end}}

{% unless site.cockroachcloud %}

Step 2. Create the django user and bank database and generate certificates

Open a SQL shell to the running CockroachDB cluster:

{% include copy-clipboard.html %}

$ cockroach sql --certs-dir=certs --host=localhost:26257

In the SQL shell, issue the following statements to create the django user and bank database:

{% include copy-clipboard.html %}

> CREATE USER IF NOT EXISTS django;

{% include copy-clipboard.html %}

> CREATE DATABASE bank;

Give the django user the necessary permissions:

{% include copy-clipboard.html %}

> GRANT ALL ON DATABASE bank TO django;

Exit the SQL shell:

{% include copy-clipboard.html %}

> \q

Create a certificate and key for the django user by running the following command:

{% include copy-clipboard.html %}

$ cockroach cert create-client django --certs-dir=certs --ca-key=my-safe-directory/ca.key

Step 2. Create the django user and bank database

Open a SQL shell to the running CockroachDB cluster:

{% include copy-clipboard.html %}

$ cockroach sql --insecure --host=localhost:26257

In the SQL shell, issue the following statements to create the django user and bank database:

{% include copy-clipboard.html %}

> CREATE USER IF NOT EXISTS django;

{% include copy-clipboard.html %}

> CREATE DATABASE bank;

Give the django user the necessary permissions:

{% include copy-clipboard.html %}

> GRANT ALL ON DATABASE bank TO django;

Exit the SQL shell:

{% include copy-clipboard.html %}

> \q

{% endunless %}

{% if site.cockroachcloud %}

Step 2: Connect to your CockroachCloud cluster and create the django user and bank database

Connect to your CockroachCloud cluster using the SQL shell.

In the SQL shell, issue the following statements to create the django user and bank database:

{% include copy-clipboard.html %}

> CREATE USER IF NOT EXISTS django WITH PASSWORD 'password';

{% include copy-clipboard.html %}

> CREATE DATABASE bank;

Give the django user the necessary permissions:

{% include copy-clipboard.html %}

> GRANT ALL ON DATABASE bank TO django;

Exit the SQL shell:

{% include copy-clipboard.html %}

> \q

{% endif %}

Step 3. Create a Django project

In the directory where you'd like to store your code, use the django-admin command-line tool to create an application project:

{% include copy-clipboard.html %}

$ django-admin startproject myproject

This creates a new project directory called myproject. myproject contains the manage.py script and a subdirectory, also named myproject, that contains some .py files.

Open myproject/myproject/settings.py, and add 0.0.0.0 to the ALLOWED_HOSTS in your settings.py file, so that it reads as follows:

{% include copy-clipboard.html %}

ALLOWED_HOSTS = ['0.0.0.0']

In myproject/myproject/settings.py, add myproject to the list of INSTALLED_APPS, so that it reads as follows:

{% include copy-clipboard.html %}

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

The other installed applications listed are added to all starter Django applications by default.

{% unless site.cockroachcloud %}

In myproject/myproject/settings.py, change DATABASES to the following:

{% include copy-clipboard.html %}

DATABASES = {
    'default': {
        'ENGINE': 'django_cockroachdb',
        'NAME': 'bank',
        'USER': 'django',
        'HOST': 'localhost',
        'PORT': '26257',
        'OPTIONS': {
            'sslmode': 'require',
            'sslrootcert': '<path>/certs/ca.crt',
            'sslcert': '<path>/certs/client.django.crt',
            'sslkey': '<path>/certs/client.django.key',
        },
    },
}

</section>

<section class="filter-content" markdown="1" data-scope="insecure">

{% include copy-clipboard.html %}
~~~ python
DATABASES = {
    'default': {
        'ENGINE': 'django_cockroachdb',
        'NAME': 'bank',
        'USER': 'django',
        'HOST': 'localhost',
        'PORT': '26257',
    }
}

{% endunless %}

{% if site.cockroachcloud %}

In the CockroachCloud console, generate the connection parameters. Then in myproject/myproject/settings.py, change DATABASES to the following:

{% include copy-clipboard.html %}

DATABASES = {
    'default': {
        'ENGINE': 'django_cockroachdb',
        'NAME': 'bank',
        'USER': 'django',
        'PASSWORD': 'password',
        'HOST': '<host>',
        'PORT': '26257',
    }
}

{% endif %}

Step 4. Write the application logic

After you generate the initial Django project files, you need to build out the application with a few .py files in myproject/myproject.

Models

Start by building some models, defined in a file called models.py. You can copy the sample code below and paste it into a new file, or you can download the file directly.

{% include copy-clipboard.html %}

{% include {{page.version.version}}/app/django-basic-sample/models.py %}

In this file, we define some simple classes that map to the tables in the example database bank.

Views

Next, build out some class-based views for the application in a file called views.py. You can copy the sample code below and paste it into a new file, or you can download the file directly.

{% include copy-clipboard.html %}

{% include {{page.version.version}}/app/django-basic-sample/views.py %}

This file defines the application's views as classes. Each view class corresponds to one of the table classes defined in models.py. The methods of these classes define read and write transactions on the tables in the database.

Importantly, the file defines a transaction retry loop in the decorator function retry_on_exception(). This function decorates each view method, ensuring that transaction ordering guarantees meet the ANSI SERIALIZABLE isolation level. For more information about how transactions (and retries) work, see Transactions.

URL routes

Lastly, define some URL routes in a file called urls.py. The django-admin command-line tool generated this file when you created the Django project, so it should already exist in myproject/myproject. You can copy the sample code below and paste it into the existing urls.py file, or you can download the file directly and replace the existing one.

{% include copy-clipboard.html %}

{% include {{page.version.version}}/app/django-basic-sample/urls.py %}

Models

Start by building some models, defined in a file called models.py. You can copy the sample code below and paste it into a new file, or you can download the file directly.

{% include copy-clipboard.html %}

{% include {{page.version.version}}/app/insecure/django-basic-sample/models.py %}

In this file, we define some simple classes that map to the tables in the example database bank.

Views

Next, build out some class-based views for the application in a file called views.py. You can copy the sample code below and paste it into a new file, or you can download the file directly.

{% include copy-clipboard.html %}

{% include {{page.version.version}}/app/insecure/django-basic-sample/views.py %}

This file defines the application's views as classes. Each view class corresponds to one of the table classes defined in models.py. The methods of these classes define read and write transactions on the tables in the database.

Importantly, the file defines a transaction retry loop in the decorator function retry_on_exception(). This function decorates each view method, ensuring that transaction ordering guarantees meet the ANSI SERIALIZABLE isolation level. For more information about how transactions (and retries) work, see Transactions.

URL routes

Lastly, define some URL routes in a file called urls.py. The django-admin command-line tool generated this file when you created the Django project, so it should already exist in myproject/myproject. You can copy the sample code below and paste it into the existing urls.py file, or you can download the file directly and replace the existing one.

{% include copy-clipboard.html %}

{% include {{page.version.version}}/app/insecure/django-basic-sample/urls.py %}

Step 5. Set up and run the Django app

In the top myproject directory, use the manage.py script to create Django migrations that initialize the database for the application:

{% include copy-clipboard.html %}

$ python manage.py makemigrations myproject

{% include copy-clipboard.html %}

$ python manage.py migrate

This initializes the bank database with the tables defined in models.py, in addition to some other tables for the admin functionality included with Django's starter application.

{% unless site.cockroachcloud %}

To verify that the migration succeeded, open a SQL shell to the running CockroachDB cluster:

{% include copy-clipboard.html %}

$ cockroach sql --certs-dir=certs --host=localhost:26257

To verify that the migration succeeded, open a SQL shell to the running CockroachDB cluster:

{% include copy-clipboard.html %}

$ cockroach sql --insecure --host=localhost:26257

{% endunless %}

{% if site.cockroachcloud %}

To verify that the migration succeeded, connect to your CockroachCloud cluster using the SQL shell and issue the following statements:

{% endif %}

{% include copy-clipboard.html %}

> USE bank;

{% include copy-clipboard.html %}

> SHOW TABLES;
          table_name
+----------------------------+
  auth_group
  auth_group_permissions
  auth_permission
  auth_user
  auth_user_groups
  auth_user_user_permissions
  django_admin_log
  django_content_type
  django_migrations
  django_session
  myproject_customers
  myproject_orders
  myproject_orders_product
  myproject_products
(14 rows)

In a new terminal, navigate to the top of the myproject directory, and start the app:

{% include copy-clipboard.html %}

$ python manage.py runserver 0.0.0.0:8000

To perform simple reads and writes to the database, you can send HTTP requests to the application.

For example, in a new terminal, you can use curl to send a POST request to the application that inserts a new row into the customers table:

{% include copy-clipboard.html %}

$ curl --header "Content-Type: application/json" \
--request POST \
--data '{"name":"Carl"}' http://0.0.0.0:8000/customer/

You can then send a GET request to read from that table:

{% include copy-clipboard.html %}

$ curl http://0.0.0.0:8000/customer/
[{"id": 523377322022797313, "name": "Carl"}]

You can also query the tables directly in the SQL shell to see the changes:

{% include copy-clipboard.html %}

> SELECT * FROM myproject_customers;
          id         | name
+--------------------+------+
  523377322022797313 | Carl
(1 row)

What's next?

Read more about writing a Django app.

{% include {{page.version.version}}/app/see-also-links.md %}