Connect to OceanBase Database by using peewee|OceanBase Database|docs|OceanBase Documentations

Applicability

peewee is applicable to OceanBase Database in MySQL mode.

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

Obtain the connection string of OceanBase Database.
Install peewee and the MySQL client library.
Create a test.py file and fill in the database connection information.
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.
$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.

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

Note

Peewee is a lightweight ORM library that provides a simple and powerful API to simplify database operations. Peewee supports multiple database backends, including MySQL, PostgreSQL, and SQLite.

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.

Create a file named test.py.

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.

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

Example:
```
cd D:\demo\demo
```
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:

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.
Permission error: If you encounter a permission-related error, ensure that the user has sufficient permissions to perform the required operations.
SQL syntax error: If the SQL statement has a syntax error, check whether the syntax of the SQL statement is correct.
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.