This topic describes how to connect to OceanBase Cloud using Peewee and perform basic database operations, including creating tables, inserting data, updating data, and querying data.
Prerequisites
- You have installed Python 3.x and pip.
- You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud tenant in MySQL-compatible mode. For more information, see Create a cluster instance and Create a tenant.
Procedure
- Obtain the connection string for OceanBase Cloud.
- Install Peewee and the MySQL client library.
- Create a
test.pyfile and fill it with the database connection information. - Run the
test.pyfile.
Step 1: Obtain the connection string for OceanBase Cloud
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information. Under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get connection string.
Fill in the corresponding information in the URL below based on your created OceanBase Cloud database.
obclient -h$host -P$port -u$user_name -p$password -D$database_nameParameter description:
$host: The connection address for OceanBase Cloud, for example,t********.********.oceanbase.cloud.$port: The connection port for OceanBase Cloud, default is 3306.$database_name: The name of the database to access.Note
The user connecting to the tenant must have the
CREATE,INSERT,UPDATE, andSELECTprivileges on this database. For more information about account privileges, see Create and manage accounts.$user_name: The account used to access the database.$password: The account password.
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 Object-Relational Mapping (ORM) library that provides a simple yet powerful API to simplify database operations. Peewee supports multiple database backends, including MySQL, PostgreSQL, SQLite, and others.
Open a command prompt or PowerShell terminal and run the following commands to install peewee and the MySQL client library.
pip install peewee pymysql
After installation, you can verify whether the installation was successful by running the following command:
pip list | grep peewee
Note
Peewee is a lightweight ORM library that provides a simple yet powerful API for simplifying database operations. Peewee supports multiple database backends, including MySQL, PostgreSQL, and SQLite.
Step 3: Write the test.py file and fill in the database connection information
Write the test.py file based on the information obtained in Step 1: Obtain the OceanBase Cloud database connection string and fill in the database connection information.
Create a file named
test.py.Fill in the following content in the
test.pyfile and modify the database connection information as needed.The following example shows the content of the
test.pyfile: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 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 database connection db.close()
Step 4: Run the test.py file
Open a command prompt or PowerShell terminal and run the test.py file to query data and output the results.
Navigate to the directory where the
test.pyfile is located.Example:
cd D:\demo\demoRun the
test.pyfile.Example:
python test.pyExample output:
John (20) Lucy (26) Tom (30)
Error handling
When using peewee to connect to OceanBase Cloud, you may encounter various errors. The following are some common errors and their handling methods:
Connection error: If you cannot connect to the database, check whether the connection parameters are correct, including the hostname, port, username, password, and database name.
Permission error: If a permission-related error occurs, ensure the user has sufficient privileges to perform the required operation.
SQL syntax error: If an SQL statement contains a syntax error, check whether the SQL statement is correctly structured.
Data type error: If the data type of the inserted value does not match the table definition, ensure the data type is correct.
In Peewee, you can use try-except statements to catch and handle these errors, ensuring the program handles them gracefully instead of crashing directly. Additionally, you can use Python's logging module to record error information, which facilitates debugging and troubleshooting.
References
- For more information about connecting to OceanBase Cloud, see Overview of connection methods.