This topic describes how to connect to OceanBase AI Database using SQLAlchemy 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 installed OceanBase AI Database and created a user tenant.
Procedure
- Obtain the connection string for OceanBase AI Database.
- Install the SQLAlchemy library.
- Create a
test.pyfile and fill it with database connection information. - Run the
test.pyfile.
Step 1: Obtain the connection string for OceanBase AI Database
Contact the deployment personnel or administrator of OceanBase AI 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 AI Database. For connections through OceanBase Database Proxy (ODP), use an ODP address; for direct connections, use the IP address of an OBServer node.$port: The port for connecting to OceanBase AI Database. For ODP connections, the default port is2883, which can be customized during ODP deployment; for direct connections, the default port is2881, which can be customized during OceanBase AI Database deployment.$database_name: The name of the database to access.Notice
The user connecting to the tenant must have the
CREATE,INSERT,UPDATE, andSELECTprivileges on this database. For more information about user privileges, see Privilege types.$user_name: The connection account for the tenant. For ODP connections, the common formats areusername@tenant_name#cluster_nameorcluster_name:tenant_name:username; for direct connections, the format isusername@tenant_name.$password: The account password.
For more information about the connection string, see Connect to OceanBase AI Database by using OBClient. ).
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 an SQL toolkit and an ORM system that simplifies database operations. SQLAlchemy supports multiple database backends, including MySQL, PostgreSQL, SQLite, and others.
Open the command prompt or PowerShell terminal and run the following command to install the SQLAlchemy library.
pip install sqlalchemy mysqlclient
After installation, you can verify the installation by running the following command:
pip list | grep SQLAlchemy
Note
SQLAlchemy is a powerful ORM library that provides two usage modes: Core and ORM. Core offers an SQL expression language, while ORM provides object-relational mapping functionality. This example primarily uses the ORM mode.
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 AI 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 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 a 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 base class Base = declarative_base() # Define 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 Table Base.metadata.create_all(engine) # Create 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 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.
Navigate to the directory where the
test.pyfile is located.Example:
cd D:\demo\demoRun the
test.pyfile.Example:
python test.pyThe return 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 AI Database, 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 you encounter a permission-related error, ensure the user has sufficient permissions to perform the required operation.
SQL syntax error: If the SQL statement contains a syntax error, check whether the SQL syntax is correct.
Data type error: If the data type of the inserted value does not match the table definition, ensure that the data type is correct.
In your code, you can use try-except statements to catch and handle these errors, ensuring that the program handles errors gracefully instead of crashing directly. Additionally, you can use the logging module to record error information, which facilitates debugging and troubleshooting.
References
For more information about connecting to OceanBase AI Database, see Overview of connection methods. ).
For more information about creating a database, see CREATE DATABASE. ).