Connect to an OceanBase Cloud database using Django

This topic describes how to connect to an OceanBase Cloud database using Django and perform basic database operations, such as creating a table, inserting data, updating data, and querying data.

Prerequisites

• You have installed Python 3.x and pip.
• You have registered an OceanBase Cloud account and created an instance and a MySQL compatible tenant. For more information, see Create an instance and Create a tenant.

Procedure

1. Obtain the connection string of the OceanBase Cloud database.
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. Write views and URL configurations.
8. Run the development server.

Step 1: Obtain the connection string of OceanBase Cloud

1. Log in to the OceanBase Cloud console. In the instance list, expand the information of the target instance, and in the target tenant, choose Connect > Get Connection String.

   For more information, see Obtain the connection string.

2. Fill in the URL below based on the information of the created OceanBase Cloud instance.

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

   Parameter description:

   • $host: the connection address of OceanBase Cloud, for example, t********.********.oceanbase.cloud.
   • $port: the connection port of OceanBase Cloud. The default value is 3306.
   • $database_name: the name of the database to be accessed.

   Notice

   The user of the tenant must have the CREATE, INSERT, UPDATE, and SELECT permissions on the database. For more information about account permissions, see Create and manage an account.

   • $user_name: the account for accessing the database.
   • $password: the password of the account.

   Here is an example:

   obclient -h t********.********.oceanbase.cloud -P3306 -u 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 commands 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 is 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 configuration.

   DATABASES = {
       'default': {
           'ENGINE': 'django.db.backends.mysql',
           'NAME': 'test',
           'USER': 'mysql001',
           'PASSWORD': '******',
           'HOST': 't********.********.oceanbase.cloud',
           'PORT': '3306',
           '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 a model

1. Open the users/models.py file and create a 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 the browser, go to http://127.0.0.1:8000/ and view the result.

   The returned result is as follows:

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

Error handling

When using Django to connect to OceanBase Cloud, you may encounter various errors. Here are some common errors and their solutions:

1. Connection error: 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. Permission error: If you encounter permission-related errors, ensure that the user has sufficient permissions to perform the required operations.

3. SQL syntax error: If the SQL statement has a syntax error, check whether the syntax of the SQL statement is correct.

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

In Django, you can use the try-except statement to catch and handle these errors, ensuring that the program can handle errors gracefully without crashing. Additionally, you can use Django's logging system to record error information, which facilitates debugging and troubleshooting.

References

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