Use Tortoise ORM to connect to OceanBase Database

Tortoise ORM is an asynchronous object-relational mapping (ORM) library based on asyncio, designed for modern Python applications. Its asynchronous nature makes it particularly suitable for the following scenarios:

• Handling a large number of concurrent database connections
• Building high-performance web servers
• Applications that need to process multiple network requests simultaneously

In Tortoise ORM, asynchronous communication ensures that database operations do not block the entire application, allowing it to handle other tasks simultaneously, significantly improving the overall performance and response speed of the application.

This topic describes how to use Tortoise ORM to connect to OceanBase Database and perform basic database operations, including table creation, data insertion, data updates, and data queries.

Prerequisites

• You have installed Python 3.7 or later and pip.
• You have installed OceanBase Database and created a MySQL tenant.

Procedure

1. Obtain the connection string of OceanBase Database.
2. Install Tortoise ORM and the MySQL client library.
3. Write a test_tortoise.py file and enter the database connection information.
4. Run the test_tortoise.py file.

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

Parameter description:

• $host: the IP address for connecting to OceanBase Database. For the 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 the connection through ODP, the default port is 2883, which can be customized during ODP deployment. For direct connection, the default port is 2881, which can be customized during OceanBase Database deployment.

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

  Notice

  The user for connecting to a tenant must have the CREATE, INSERT, UPDATE, and SELECT privileges on the database.

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

• $password: the password of the account.

Here is an example:

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

Step 2: Install Tortoise ORM and the MySQL client library

Tortoise ORM is an asynchronous Python ORM library designed for asyncio. It supports MySQL, PostgreSQL, and SQLite databases.

Open the command prompt or PowerShell terminal and run the following commands to install Tortoise ORM and the MySQL client library.

pip install tortoise-orm aiomysql

After the installation is complete, run the following command to verify whether the installation was successful:

pip list | grep tortoise

Step 3: Write the test_tortoise.py file and fill in the database connection information

Write the test_tortoise.py file based on the information obtained in Step 1: Obtain the connection string of OceanBase Database and fill in the database connection information.

1. Create a file named test_tortoise.py.

2. Fill in the following content in the test_tortoise.py file and modify the database connection information as needed.

   The content of the test_tortoise.py file is as follows:

   from tortoise import Tortoise, run_async
   from tortoise.models import Model
   from tortoise import fields
   import asyncio
   
   # Define the model
   from datetime import datetime
   
   class User(Model):
       id = fields.IntField(pk=True)
       username = fields.CharField(max_length=50, null=False)
       email = fields.CharField(max_length=100, unique=True, null=False)
       password_hash = fields.CharField(max_length=255, null=False)
       created_at = fields.DatetimeField(auto_now_add=True, null=False)
       updated_at = fields.DatetimeField(auto_now=True, null=True)
   
       def __str__(self):
           return f"{self.username} ({self.email})"
   
       class Meta:
           table = "users"
   
   # Database configuration
   DB_CONFIG = {
       'connections': {
           'oceanbase': {
               'engine': 'tortoise.backends.mysql',
               'credentials': {
                   'host': 'xxx.xxx.xxx.xxx',
                   'port': 2881,
                   'user': 'test_user001@mysql001',
                   'password': '******',
                   'database': 'test',
               }
           },
       },
       'apps': {
           'models': {
               'models': ['__main__'],  # Models in the current file
               'default_connection': 'oceanbase',
           }
       },
       'use_tz': False,
       'timezone': 'Asia/Shanghai',
   }
   
   async def init_db():
       # Initialize the database connection
       await Tortoise.init(config=DB_CONFIG)
       # Create the table
       await Tortoise.generate_schemas()
   
   async def main():
       # Initialize the database
       await init_db()
   
       # Insert data
       from datetime import datetime
   
       await User.create(
           username='john_doe',
           email='john@example.com',
           password_hash='hashed_password_123',
           created_at=datetime.now(),
           updated_at=datetime.now()
       )
   
       # Update data
       user = await User.filter(email='john@example.com').first()
       if user:
           user.username = 'john_updated'
           user.updated_at = datetime.now()
           await user.save()
   
       # Query data
       users = await User.all()
       for user in users:
           print(user)
   
       # Close the database connection
       await Tortoise.close_connections()
   
   if __name__ == '__main__':
       run_async(main())
   

Step 4: Run the test_tortoise.py file

Open the command prompt or PowerShell terminal, run the test_tortoise.py file, query the data, and output the result.

1. Go to the directory where the test_tortoise.py file is located.

   Example:

   cd ~/orm-demo
   

2. Run the test_tortoise.py file.

   Example:

   python test_tortoise.py
   

   The returned result is as follows:

   john_updated (john@example.com)
   

References

• Tortoise ORM official documentation: https://tortoise-orm.readthedocs.io/