Connect to OceanBase Database by using Django

This topic describes how to connect to OceanBase Database by using Django and perform basic database operations, including table creation, data insertion, data updating, and data query.

Prerequisites

• You have installed Python 3.x and pip.
• You have installed OceanBase Database and created a MySQL-compatible tenant.

Procedure

1. Obtain the OceanBase Database connection string.
2. Install Django and the MySQL client library.
3. Create a Django project and application.
4. Configure the database connection.
5. Create models.
6. Run migrations.
7. Create views and URL configuration.
8. Run the development server.

Step 1: Obtain the OceanBase Database connection string

Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string.

obclient -h$host -P$port -u$user_name -p$password -D$database_name

The parameters are described as follows:

• $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

• $database_name: the name of the database to be accessed.

  Notice

  The user who connects to the tenant must have CREATE, INSERT, UPDATE, and SELECT privileges on the database. For more information about user privileges, see Privilege type in MySQL-compatible mode.

• $user_name: the account for connecting to the tenant. Common formats for ODP connection: username@tenant#cluster name or cluster name:tenant:username. Format for direct connection: username@tenant.

• $password: the password of the account.

For more information about connection strings, see Connect to an OceanBase tenant by using OBClient.

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Install Django and the MySQL client library

Django is a high-level Python web framework that provides an ORM (Object-Relational Mapping) system to simplify database operations. Django supports multiple database backends, including MySQL, PostgreSQL, and SQLite.

Open the command prompt or PowerShell terminal, and run the following command to install Django and the MySQL client library:

pip install django mysqlclient

After the installation, run the following command to verify the installation:

pip list | grep -i django

Step 3: Create a Django project and application

1. Create a Django project named oceanbase_demo.

   django-admin startproject oceanbase_demo
   

2. Go to the project directory.

   cd oceanbase_demo
   

3. Create an application named users.

   python manage.py startapp users
   

Step 4: Configure the database connection

1. Open the oceanbase_demo/settings.py file and modify the database configuration.

   DATABASES = {
       'default': {
           'ENGINE': 'django.db.backends.mysql',
           'NAME': 'test',
           'USER': 'test_user001@mysql001',
           'PASSWORD': '******',
           'HOST': 'xxx.xxx.xxx.xxx',
           'PORT': '2881',
           'OPTIONS': {
               'charset': 'utf8mb4',
           },
       }
   }
   

2. Add the users application to INSTALLED_APPS.

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

Step 5: Create models

1. Open the users/models.py file and create the User model.

   from django.db import models
   
   class User(models.Model):
       name = models.CharField(max_length=50)
       age = models.IntegerField()
   
       def __str__(self):
           return f"{self.name} ({self.age})"
   

Step 6: Run migrations

1. Create migration files.

   python manage.py makemigrations
   

2. Apply migrations.

   python manage.py migrate
   

Step 7: Create views and URL configuration

1. Open the users/views.py file and create the view function.

   from django.shortcuts import render
   from django.http import HttpResponse
   from .models import User
   
   def index(request):
       # Insert data
       User.objects.create(name='John', age=20)
       User.objects.create(name='Lucy', age=25)
       User.objects.create(name='Tom', age=30)
   
       # Update data
       user = User.objects.get(name='Lucy')
       user.age = 26
       user.save()
   
       # Query data
       users = User.objects.all()
       result = "<h1>Users</h1><ul>"
       for user in users:
           result += f"<li>{user}</li>"
       result += "</ul>"
   
       return HttpResponse(result)
   

2. Open the oceanbase_demo/urls.py file and configure the URL.

   from django.contrib import admin
   from django.urls import path
   from users import views
   
   urlpatterns = [
       path('admin/', admin.site.urls),
       path('', views.index, name='index'),
   ]
   

Step 8: Run the development server

1. Run the development server.

   python manage.py runserver
   

2. Open http://127.0.0.1:8000/ in your browser to view the result.

   The following result is returned:

   <h1>Users</h1>
   <ul>
       <li>John (20)</li>
       <li>Lucy (26)</li>
       <li>Tom (30)</li>
   </ul>
   

Error handling

When you use Django to connect to OceanBase Database, you may encounter various errors. The following are some common errors and their handling methods:

1. Connection errors: If you cannot connect to the database, check whether the connection parameters are correct, including the host name, port, username, password, and database name.

2. Privilege errors: If you encounter privilege-related errors, ensure that the user has sufficient privileges to perform the required operations.

3. SQL syntax errors: If the SQL statement has syntax errors, check whether the SQL syntax is correct.

4. Data type errors: If the data type of the inserted data does not match the table definition, ensure that the inserted data type is correct.

In Django, you can use the try-except statement to catch and handle these errors, ensuring that the program handles errors gracefully instead of crashing. You can also use the Django logging system to record error information for debugging and troubleshooting.

References

• For more information about connecting to OceanBase Database, see Connection methods overview.

• For more information about creating a database, see CREATE DATABASE.