Connect to OceanBase Database by using SQLAlchemy

This topic describes how to connect to OceanBase Database by using SQLAlchemy and perform basic database operations, including table creation, data insertion, data updating, and data query.

Prerequisites

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

Procedure

1. Obtain the OceanBase Database connection string.
2. Install the SQLAlchemy library.
3. Create the test.py file and fill in the database connection information.
4. Run the test.py file.

Step 1: Obtain the OceanBase Database connection string

Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string.

obclient -h$host -P$port -u$user_name -p$password -D$database_name

The parameters are described as follows:

• $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is 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 who connects to the tenant must have CREATE, INSERT, UPDATE, and SELECT privileges on the database. For more information about user privileges, see Privilege type in MySQL-compatibe mode.

• $user_name: the account for connecting to the tenant. Common formats for ODP connection: username@tenant#cluster name or cluster name:tenant:username. Format for direct connection: username@tenant.

• $password: the password of the account.

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

SQLAlchemy is one of the most popular ORM (Object-Relational Mapping) libraries in Python. It provides an SQL toolkit and ORM system that simplifies 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, run the following command to verify the installation:

pip list | grep SQLAlchemy

Step 3: Create the test.py file and fill in the database connection information

Create the test.py file based on the information obtained in Step 1: Obtain the OceanBase Database connection string, and fill in the database connection information.

1. Create a file named test.py.

2. Add the following content to the test.py file and modify the database connection information as needed.

   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 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 following result is returned:

   <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 are some common errors and their handling methods:

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

2. Privilege errors: If you encounter privilege-related errors, ensure that the user has sufficient permissions to perform the required operations.

3. SQL syntax errors: If the SQL statement has syntax errors, check whether 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 inserted data type is correct.

In the code, you can use the try-except statement to catch and handle these errors, ensuring that the program handles errors gracefully instead of crashing. You can also use the logging module to record error information for debugging and troubleshooting.

References

• For more information about connecting to OceanBase Database, see Connection methods overview.

• For more information about creating a database, see CREATE DATABASE.