Connect to OceanBase Database by using peewee

This topic describes how to use peewee to connect to OceanBase Database and perform basic database operations, including table creation, data insertion, data updating, and data querying.

Prerequisites

• You have installed Python 3.x and pip.
• You have installed OceanBase Database and created a MySQL-compatible tenant.

Procedure

1. Obtain the connection string of OceanBase Database.
2. Install the peewee and MySQL client libraries.
3. Write a test.py file and enter 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

Parameters:

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

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

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

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

• $password: the account password.

For more information about connection strings, 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 the peewee and MySQL client libraries.

Peewee is a lightweight Object-Relational Mapping (ORM) library in Python. It provides a simple yet 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 command to install the Peewee and MySQL client libraries.

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: Write the test.py file and enter the database connection information

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

1. Create a file named test.py.

2. Enter the following content into the test.py file and modify the database connection information as needed.

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

   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.

   Here is an example:

   cd D:\demo\demo
   

2. Run the test.py file.

   Here is an example:

   python test.py
   

   The return result is as follows:

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

Error handling

When using peewee to connect to the OceanBase database, you may encounter various errors. Below are some common errors and their solutions:

1. Connection errors: If you are unable to connect to the database, check whether the connection parameters are correct, including the hostname, port, username, password, and database name.

2. Permission errors: If you encounter permission-related errors, ensure that the user has sufficient privileges to perform the required operations.

3. SQL Syntax errors: If there are syntax errors in your SQL statements, verify that the SQL syntax is correct.

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

In your peewee, you can use try-except statements to catch and handle these errors, ensuring that the program can handle errors gracefully instead of crashing. Additionally, you can use the logging module of Python to record error information, which helps with 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.