Connect to an OceanBase Cloud database by using peewee

This topic describes how to connect to an OceanBase Cloud database by using peewee 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 Alibaba Cloud account for OceanBase Cloud 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 the peewee and MySQL client libraries.
3. Create a test.py file and fill in the database connection information.
4. Run the test.py file.

Step 1: Obtain the connection string of the OceanBase Cloud database

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 with the information of the created OceanBase Cloud database.

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

   Parameter description:

   • $host: the connection address of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.

   • $port: the connection port of the OceanBase Cloud database. The default value is 3306.

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

     Note

     The user of the tenant must have the CREATE, INSERT, UPDATE, and SELECT privileges on the database. For more information about account privileges, 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 peewee and the MySQL client library

Peewee is a lightweight Python ORM (object-relational mapping) library that provides a simple and powerful API to simplify database operations. It supports multiple database backends, including MySQL, PostgreSQL, and SQLite.

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

pip install peewee pymysql

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

pip list | grep peewee

Step 3: Create a test.py file and fill in the database connection information

Create a test.py file and fill in the database connection information based on the information obtained in Step 1: Obtain the connection string of the OceanBase Cloud database.

1. Create a file named test.py.

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

   Here is an example of the content of the test.py file:

   from peewee import *
   
   # Database connection information
   DB_USER = 'mysql001'
   DB_PASSWORD = '******'
   DB_HOST = 't********.********.oceanbase.cloud'
   DB_PORT = 3306
   DB_NAME = 'test'
   
   # Create a database connection
   db = MySQLDatabase(
       DB_NAME,
       user=DB_USER,
       password=DB_PASSWORD,
       host=DB_HOST,
       port=DB_PORT
   )
   
   # Define a model
   class User(Model):
       name = CharField(max_length=50)
       age = IntegerField()
   
       class Meta:
           database = db
           table_name = 'users'
   
       def __str__(self):
           return f"{self.name} ({self.age})"
   
   # Connect to the database
   db.connect()
   
   # Create a table
   db.create_tables([User])
   
   # Insert data
   User.create(name='John', age=20)
   User.create(name='Lucy', age=25)
   User.create(name='Tom', age=30)
   
   # Update data
   user = User.get(User.name == 'Lucy')
   user.age = 26
   user.save()
   
   # Query data
   users = User.select()
   for user in users:
       print(user)
   
   # Close the database connection
   db.close()
   

Step 4: Run the test.py file

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

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

   Here is an example:

   cd D:\demo\demo
   

2. Run the test.py file.

   Here is an example:

   python test.py
   

   The returned result is as follows:

   John (20)
   Lucy (26)
   Tom (30)
   

Error handling

When you use peewee to connect to OceanBase Cloud, you may encounter various errors. The following 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 SQL statement is correctly written.

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 peewee, you can use the try-except statement to capture and handle these errors, ensuring that the program can handle errors gracefully without crashing. Additionally, you can use the logging module in Python 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.