Connect to OceanBase Database with Tortoise ORM

Tortoise ORM is an asynchronous Object-Relational Mapping (ORM) library built for modern Python applications using asyncio. Its async capabilities make it especially well-suited for:

• Handling large numbers of concurrent database connections
• Building high-performance web servers
• Applications that need to handle multiple network requests concurrently

With Tortoise ORM, asynchronous communication ensures that database operations do not block your application, letting other tasks run in parallel and significantly improving overall performance and responsiveness.

This topic introduces how to use the Tortoise ORM to connect to OceanBase Database and perform basic operations such as creating tables, inserting data, updating data, and querying data.

Prerequisites

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

Procedure

1. Obtain the connection string of OceanBase Database.
2. Install Tortoise ORM and the MySQL client library.
3. Create 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

Parameters:

• $host: the IP address for connecting to OceanBase Database. For connections through OceanBase Database Proxy (ODP), use an ODP endpoint. For direct connections, use the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. For connections through ODP, the default port is 2883, which can be customized during ODP deployment. For direct connections, the default port is 2881, which can be customized during OceanBase Database deployment.

• $database_name: the name of the database to access.

  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-compatible mode.

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

• $password: the password for the account.

For more information about the connection string, 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 Tortoise ORM and the MySQL client library

Tortoise ORM is an async Python ORM designed for asyncio, supporting MySQL, PostgreSQL, SQLite, and more.

Open your command prompt or PowerShell and run the following command to install Tortoise ORM and the MySQL client library:

pip install tortoise-orm aiomysql

To verify the installation:

pip list | grep tortoise

Step 3: Create and configure test_tortoise.py

Use the connection details from Step 1: Obtain the OceanBase Database connection string to set up your script.

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.

   Sample content in the test_tortoise.py file:

   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 tables
       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 database connection
       await Tortoise.close_connections()
   
   if __name__ == '__main__':
       run_async(main())
   

Step 4: Run the test_tortoise.py file

Open your command prompt or PowerShell, navigate to the directory containing test_tortoise.py, and run the script to query and output data.

1. Navigate to the directory where test_tortoise.py is located.

   Example:

   cd ~/orm-demo
   

2. Run the test_tortoise.py file.

   Example:

   python test_tortoise.py
   

   The output will be:

   john_updated (john@example.com)
   

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.

• Tortoise ORM official documentation: https://tortoise.github.io/