Connect to OceanBase Database by using peewee

This topic describes how to use peewee to connect to OceanBase Database and perform basic database operations, such as table creation, data insertion, data update, 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 peewee and the MySQL client library.
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 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. If you connect to OceanBase Database through OceanBase Database Proxy (ODP), use the IP address of an ODP. If you connect to OceanBase Database directly, use the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. If you connect to OceanBase Database through ODP, the default port is 2883, which can be customized when ODP is deployed. If you connect to OceanBase Database directly, the default port is 2881, which can be customized when OceanBase Database is deployed.

• $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. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the tenant connection account. The format of the account for connecting to OceanBase Database through ODP is username@tenant name#cluster name or cluster name:tenant name:username. The format of the account for connecting to OceanBase Database directly is username@tenant name.

• $password: the password of the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@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. Peewee 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 complete, run the following command to verify whether the installation was successful:

pip list | grep peewee

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

Create a test.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.py.

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

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

   from peewee import *
   
   # Database connection information
   DB_USER = 'test_user001@mysql001'
   DB_PASSWORD = '******'
   DB_HOST = 'xxx.xxx.xxx.xxx'
   DB_PORT = 2881
   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.

   Example:

   cd D:\demo\demo
   

2. Run the test.py file.

   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 Database, you may encounter various errors. The following lists 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 number, username, password, and database name, are correct.

2. Permission error: If you encounter a permission-related error, 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 types of the inserted data do not match the table definition, ensure that the data types of the inserted data are correct.

In peewee, you can use the try-except statement to capture and handle these errors. This way, the program can handle errors gracefully without crashing. You can also use the logging module of Python to record error information for 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.