Use SQLAlchemy to connect to OceanBase Database

This topic describes how to use SQLAlchemy to connect to OceanBase Database and perform basic database operations, such as creating tables, inserting data, updating data, and querying data.

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 the SQLAlchemy 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 through OceanBase Database Proxy (ODP), use the IP address of the ODP. If you connect directly to an OBServer node, use the IP address of the OBServer node.

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

• $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 account. The common format for connecting through ODP is username@tenant name#cluster name or cluster name:tenant name:username. The format for direct connection is username@tenant name.

• $password: the account password.

For more information about the connection string, 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 SQLAlchemy library

SQLAlchemy is one of the most popular Object-Relational Mapping (ORM) 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

Write the 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 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 = 'test_user001@mysql001'
   DB_PASSWORD = '******'
   DB_HOST = 'xxx.xxx.xxx.xxx'
   DB_PORT = 2881
   DB_NAME = 'test'
   
   # Create the database connection URL
   DATABASE_URL = f"mysql+mysqldb://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
   
   # Create the engine
   engine = create_engine(DATABASE_URL)
   
   # Create the base class
   Base = declarative_base()
   
   # Define the 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 the table
   Base.metadata.create_all(engine)
   
   # Create the 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, and run the test.py file to query data and output the results.

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:

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

Error handling

When you use SQLAlchemy 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, username, password, and database name, are correct.

2. Permission error: If you encounter a permission-related error, make sure 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 type of the inserted data does not match the table definition, make sure that the data type of the inserted data is correct.

In the code, 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 to record error information for easier debugging and troubleshooting.

References

• For more information about how to connect to OceanBase Database, see Connection methods.

• For more information about how to create a database, see CREATE DATABASE.