Use SQLAlchemy to connect to an OceanBase Cloud database

This topic describes how to use SQLAlchemy to connect to an OceanBase Cloud database 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 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 SQLAlchemy library.
3. Write 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 corresponding information in the following URL based on 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, which is 3306 by default.
   • $database_name: the name of the database to be accessed.

   Notice

   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 the SQLAlchemy library

SQLAlchemy is one of the most popular ORM (object-relational mapping) libraries in Python. It provides SQL toolkits and an ORM system to simplify database operations. SQLAlchemy supports multiple database backends, including MySQL, PostgreSQL, and SQLite.

Open the command prompt or PowerShell terminal and run the following command to install the SQLAlchemy library.

pip install sqlalchemy mysqlclient

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

pip list | grep SQLAlchemy

Step 3: Write the 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, write the test.py file 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 based on your actual situation.

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

   from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
   from sqlalchemy.ext.declarative import declarative_base
   from sqlalchemy.orm import sessionmaker
   
   # Database connection information
   DB_USER = 'mysql001'
   DB_PASSWORD = '******'
   DB_HOST = 't********.********.oceanbase.cloud'
   DB_PORT = 3306
   DB_NAME = 'test'
   
   # Create the database connection URL
   DATABASE_URL = f"mysql+mysqldb://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
   
   # Create an engine
   engine = create_engine(DATABASE_URL)
   
   # Create a base class
   Base = declarative_base()
   
   # Define a model
   class User(Base):
       __tablename__ = 'users'
   
       id = Column(Integer, primary_key=True)
       name = Column(String(50), nullable=False)
       age = Column(Integer)
   
       def __repr__(self):
           return f"<User(id={self.id}, name='{self.name}', age={self.age})>"
   
   # Create tables
   Base.metadata.create_all(engine)
   
   # Create a session
   Session = sessionmaker(bind=engine)
   session = Session()
   
   # Insert data
   new_users = [
       User(name='John', age=20),
       User(name='Lucy', age=25),
       User(name='Tom', age=30)
   ]
   session.add_all(new_users)
   session.commit()
   
   # Update data
   user = session.query(User).filter_by(name='Lucy').first()
   if user:
       user.age = 26
       session.commit()
   
   # Query data
   users = session.query(User).all()
   for user in users:
       print(user)
   
   # Close the session
   session.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 results.

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:

   <User(id=1, name='John', age=20)>
   <User(id=2, name='Lucy', age=26)>
   <User(id=3, name='Tom', age=30)>
   

Error handling

When using SQLAlchemy to connect to OceanBase Cloud, you may encounter various errors. Here 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 hostname, port, username, password, and database name.

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 inserted data type does not match the table definition, ensure that the inserted data type is correct.

In the code, we can use the try-except statement to capture and handle these errors, ensuring that the program can handle errors gracefully without crashing. We can also use the logging module to record error information for easier debugging and troubleshooting.

References

• For more information about how to connect to OceanBase Cloud, see Overview of connection methods.