Connect to OceanBase Database with Django

This topic describes how to connect to OceanBase Database by using Django, and perform basic database operations such as 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 tenant.

Procedure

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

Step 1: Obtain the connection string of OceanBase Database

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

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

Parameters:

• $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), use the IP address of an ODP. For direct connection, use 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 used to connect to the tenant must have the CREATE, INSERT, UPDATE, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the tenant connection account. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

• $password: the account password.

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 Object-Relational Mapping (ORM) 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 is complete, run the following command to verify whether the installation was successful:

pip list | grep -i django

Step 3: Create a Django project and an 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 configurations.

   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 the INSTALLED_APPS list.

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

Step 5: Create a model

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 a migration file.

   python manage.py makemigrations
   

2. Apply the migration.

   python manage.py migrate
   

Step 7: Write views and configure URLs

1. Open the users/views.py file and create a 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 URLs.

   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. In your browser, go to http://127.0.0.1:8000/ and view the result.

   The return result is as follows:

   <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, various errors may occur. The following table describes some common errors and their solutions.

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

2. Privilege error: If you encounter a privilege error, make sure that the user has sufficient privileges to perform the required operation.

3. SQL syntax error: If there is a syntax error in your SQL statement, check whether the syntax is correct.

4. Data type error: If the data type of the inserted data does not match the table definition, make sure that the data type is correct.

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

References

• For more information about how to connect to OceanBase Database, see Overview of connection methods.

• For more information about how to create a database, see CREATE DATABASE.