This topic describes how to build an application using mysqlclient and OceanBase AI Database to perform basic operations such as creating tables, inserting data, and querying data.
Download the python-mysqlclient sample project Prerequisites
- You have installed Python 3.x and pip.
- You have installed OceanBase AI Database and created a user tenant.
Procedure
- Check the versions of Python and pip.
- Install the mysqlclient library.
- Obtain the connection information for OceanBase AI Database.
- Modify the database connection information in the
config.pyfile. - Run the
main.pyfile. - Perform operations in the interactive command-line interface.
Step 1: Check the versions of Python and pip
Open a Command Prompt or PowerShell terminal, and run the python --version and pip --version commands to ensure that Python and pip are properly installed.
Example:
PS C:\Windows\system32> python --version
Python 3.11.2
PS C:\Windows\system32> pip --version
pip 23.3.1 from C:\Users\xxx\AppData\Local\Programs\Python\Python311\Lib\site-packages\pip (python 3.11)
Step 2: Install the mysqlclient library
mysqlclient is a C-language-based MySQL client library with high performance, suitable for scenarios requiring high performance. When installing the mysqlclient library, you need to compile and link the MySQL C API to connect to OceanBase AI Database.
Method 1: Install using precompiled binaries (recommended)
Download the
whlfile compatible with your Python version and operating system platform from Download files.Open a Command Prompt or PowerShell terminal, navigate to the directory where the
whlfile is located, and run the following command to install it:pip install mysqlclient-2.2.0-cp311-cp311-win_amd64.whl
Method 2: Install directly
Install MySQL or MySQL Connector/C, ensuring the development components are installed.
Install a C compiler (such as Visual Studio or MinGW).
Run the following command to install the mysqlclient library:
pip install mysqlclient
Install the Python development package:
sudo yum install python3-develInstall the MySQL development libraries:
sudo yum install mysql-develRun the following command to install the mysqlclient library:
sudo pip install mysqlclient
Note
mysqlclient is a high-performance MySQL client library, suitable for production environment applications with high performance requirements.
Step 3: Obtain OceanBase AI Database connection information
Contact the OceanBase AI Database deployment personnel or administrator 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, 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 4: Modify the database connection information in the config.py file
Modify the database connection information in the python-mysqlclient/config.py file based on the information obtained in Step 3: Obtain OceanBase AI Database connection information.
Navigate to the
python-mysqlclientproject folder.Modify the database connection information in the
config.pyfile.- In a Windows environment, use a text editor to open the
config.pyfile and modify the database connection information to match your actual configuration. - In a Linux environment, use the
vi config.pyorvim config.pycommand to edit theconfig.pyfile and modify the database connection information to match your actual configuration.
The following example shows the database connection information in the
config.pyfile:OCEANBASE_CONFIG = { 'host': '10.10.10.1', 'port': 2881, 'user': 'test_user001@mysql001', 'password': '******', 'database': 'test', 'charset': 'utf8mb4' }- In a Windows environment, use a text editor to open the
Step 5: Run the main.py file
Open a command prompt or PowerShell terminal and run the python main.py command to start the program.
Navigate to the
python-mysqlclientproject directory.Example:
cd /home/admin/python-mysqlclientRun the following command to start the
main.pyprogram.python main.pyThe result is as follows:
Table created successfully Instruction: 1.Insert Data; 2.Query Data; 3.Exit. Enter the command [1/2/3]>
Step 6: Perform operations in the interactive CLI
Example of successful data insertion.
In the interactive CLI, enter the command
1and press Enter.Example:
Enter the command [1/2/3]> 1At the
Enter name:prompt, enter your name and press Enter.Example:
Enter name:A1At the
Enter age:prompt, enter your age and press Enter.Example:
Enter age:18A success message
Record inserted successfullyis displayed. Finally, the command instructions are shown, prompting you to enter1,2, or3and press Enter to perform the corresponding operation.Example:
Record inserted successfully Instruction: 1.Insert Data; 2.Query Data; 3.Exit. Enter the command [1/2/3]>
Example of data insertion failure.
In the interactive CLI, enter command
1and press Enter.Example:
Enter the command [1/2/3]> 1At the
Enter name:prompt, enter your name and press Enter.Example:
Enter name:A2At the prompt
Enter age:, enter your age and press Enter.Notice
The data type of the age field is integer.
Example:
Enter age: eighteenReturns an error message for the data insertion, showing
(1366, 'Incorrect integer value'). Finally, it displays a command prompt, instructing the user to enter1,2, or3and press Enter to perform the corresponding operation.Example:
(1366, 'Incorrect integer value') Instruction: 1.Insert Data; 2.Query Data; 3.Exit. Enter the command [1/2/3]>
Sample data query.
In the interactive command-line interface, enter the command
2and press Enter.Example:
Enter the command [1/2/3]> 2Displays the table data. Finally, it displays a command prompt, informing users that they can enter
1,2, or3and press Enter to perform the corresponding operation.Example:
(1, 'A1', 18) Instruction: 1.Insert Data; 2.Query Data; 3.Exit. Enter the command [1/2/3]>
Example of entering an invalid command.
In the interactive command-line interface, enter a value other than
1/2/3and press Enter.Example:
Enter the command [1/2/3]> AAn error message is returned, displaying
Invalid command, please enter command again [1/2/3]. Finally, the command description is shown, prompting you to enter1,2, or3and press Enter to perform the corresponding operation.Example:
Invalid command, please enter command again [1/2/3] Instruction: 1.Insert Data; 2.Query Data; 3.Exit. Enter the command [1/2/3]>
Example of exiting the program.
In the interactive command-line interface, enter the command
3and press Enter to exit the program.Example:
Enter the command [1/2/3]> 3
Project code
Click python-mysqlclient to download the project code, which is a compressed file named python-mysqlclient.zip.
After decompressing it, you will get a folder named python-mysqlclient. The directory structure is as follows:
python-mysqlclient
├── config.py
├── db.py
├── main.py
└── requirements.txt
The files and directories are described as follows:
config.py: Manages database connection configuration information.db.py: Performs database operations, including creating tables, inserting data, and querying data.main.py: The application entry point, containing a simple user interface where users can execute corresponding operations by entering commands.requirements.txt: Lists the Python libraries required for the project.Note
The code obtained in this topic only lists the version requirement for the mysqlclient library. You can install it by running the
pip install -r requirements.txtcommand, which will automatically install the required library.
Code in config.py
When connecting to a database using Python, you need to specify the database connection parameters. These parameters can be placed in a separate configuration file, such as the config.py file. Encapsulating these parameters in a dictionary avoids the hassle of repeatedly writing them in every file; you can simply reference this dictionary from other Python files to connect to the database.
The code in the obtained config.py file defines a dictionary variable named OCEANBASE_CONFIG, which is used to manage the connection parameters for OceanBase AI Database.
The sample code is as follows:
OCEANBASE_CONFIG = {
'host': 'localhost',
'port': 2881, # Default port, can be modified based on actual conditions
'user': 'user_name',
'password': '',
'database': 'db_name',
'charset': 'utf8mb4'
}
Parameter description:
host: The IP address for connecting to OceanBase AI Database. For ODP connections, use an ODP address; for direct connections, use the IP address of an OBServer node.port: The port for connecting to OceanBase AI Database. The default is2881. When using ODP, the default port is2883.user: The username for connecting to the database. Direct connection format: {username}@{tenant name}; ODP connection format: {user name}@{tenant name}#{cluster name}. For more information about connecting to OceanBase AI Database, see Overview of connection methods.password: The password for connecting to the database.database: The name of the database to connect to.charset: The character set used when connecting to the database.
Notice
The specific attribute (parameter) configuration depends on project requirements and database characteristics. It is recommended that you adjust and configure them according to your actual situation.
Code in db.py
The db.py file is a Python module that encapsulates database operations, primarily used to implement create, read, update, and delete (CRUD) operations on a database.
This file mainly includes the following parts:
Import the MySQLdb module and the database connection parameters.
The sample code is as follows:
import MySQLdb from config import OCEANBASE_CONFIGDefine a function to create a table.
Define the
create_tablefunction, which creates a table namedtest_tbl1in OceanBase AI Database. Use thewithstatement to manage the lifecycle of database connections and cursor objects, ensuring they are closed securely and avoiding issues like memory leaks. Define an SQL statement, execute it, and print the execution result or error message.The sample code is as follows:
def create_table(): with MySQLdb.connect(**OCEANBASE_CONFIG) as conn: with conn.cursor() as cursor: try: create_table_sql = """ CREATE TABLE test_tbl1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) ENGINE=OCEANBASE AUTO_INCREMENT=1 """ cursor.execute(create_table_sql) print("Table created successfully") except MySQLdb.Error as err: print(err)Define a function for inserting data.
Define the
insert_recordfunction, which inserts a record into a specified table. The record contains two fields:nameandage. Use thewithstatement to manage the lifecycle of database connections and cursor objects, ensuring they are closed securely and avoiding issues like memory leaks. Define the SQL statement, perform the insertion operation, commit the transaction, and print the execution result or error message.The sample code is as follows:
def insert_record(table_name, name, age): with MySQLdb.connect(**OCEANBASE_CONFIG) as conn: with conn.cursor() as cursor: try: insert_sql = f"INSERT INTO {table_name} (name, age) VALUES (%s, %s)" cursor.execute(insert_sql, (name, age)) conn.commit() print("Record inserted successfully") except MySQLdb.Error as err: print(err)Define a function for querying table data.
Define the
select_allfunction, which queries all records in a specified table. Use thewithstatement to manage the lifecycle of database connections and cursor objects, ensuring they are closed securely and avoiding issues like memory leaks. Define the SQL statement, execute the query, iterate through the results, and print all records. If an exception occurs, catch it and print the error message.The sample code is as follows:
def select_all(table_name): with MySQLdb.connect(**OCEANBASE_CONFIG) as conn: with conn.cursor() as cursor: try: select_sql = f"SELECT * FROM {table_name}" cursor.execute(select_sql) result = cursor.fetchall() for row in result: print(row) except MySQLdb.Error as err: print(err)
Code in main.py
The main.py file in this topic demonstrates how to use Python and the MySQLdb module to operate a database, providing an interactive command-line interface for you to perform operations. You can use this program to create tables, insert records, and query all records, implementing basic database operations.
This file mainly includes the following parts:
Import functions defined in the
db.pyfile.Import the three functions
create_table,insert_record, andselect_allfrom the db module.The sample code is as follows:
from db import create_table, insert_record, select_allDefine a function for database operations.
Define the
mainfunction, which implements a simple interactive command-line program for database operations. It first calls thecreate_tablefunction to create a table namedtest_tbl1, then enters awhileloop to wait for user input. Based on the user's selection, the program calls different functions to perform insertion or query operations until the user enters the3command to exit. If the user enters an invalid command, the program prompts the user to enter again.Based on the different input commands, the program performs different operations, including inserting data (1), querying data (2), and exiting the program (3).
The sample code is as follows:
def main(): create_table() while True: print("Instruction: 1.Insert Data; 2.Query Data; 3.Exit.") command = input("Enter the command [1/2/3]> ") if command == "1": name = input("Enter name:") age = input("Enter age:") insert_record("test_tbl1", name, age) elif command == "2": select_all("test_tbl1") elif command == "3": break else: print("Invalid command, please enter command again [1/2/3]")Set the usage scenario for the
mainfunction.Configure the program so that the
mainfunction is called only whenmain.pyis run directly. If the module is imported into other scripts, themainfunction will not be executed.The sample code is as follows:
if __name__ == "__main__": main()Note
This approach prevents the
mainfunction from being automatically executed when the module is imported, ensuring the program's reusability and scalability.
Complete code examples
OCEANBASE_CONFIG = {
'host': 'localhost',
'port': 2881, # Default port, can be modified as needed.
'user': 'user_name',
'password': '',
'database': 'db_name',
'charset': 'utf8mb4'
}
import MySQLdb
from config import OCEANBASE_CONFIG
def create_table():
with MySQLdb.connect(**OCEANBASE_CONFIG) as conn:
with conn.cursor() as cursor:
try:
create_table_sql = """
CREATE TABLE test_tbl1 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE=OCEANBASE AUTO_INCREMENT=1
"""
cursor.execute(create_table_sql)
print("Table created successfully")
except MySQLdb.Error as err:
print(err)
def insert_record(table_name, name, age):
with MySQLdb.connect(**OCEANBASE_CONFIG) as conn:
with conn.cursor() as cursor:
try:
insert_sql = f"INSERT INTO {table_name} (name, age) VALUES (%s, %s)"
cursor.execute(insert_sql, (name, age))
conn.commit()
print("Record inserted successfully")
except MySQLdb.Error as err:
print(err)
def select_all(table_name):
with MySQLdb.connect(**OCEANBASE_CONFIG) as conn:
with conn.cursor() as cursor:
try:
select_sql = f"SELECT * FROM {table_name}"
cursor.execute(select_sql)
result = cursor.fetchall()
for row in result:
print(row)
except MySQLdb.Error as err:
print(err)
from db import create_table, insert_record, select_all
def main():
create_table()
while True:
print("Instruction: 1.Insert Data; 2.Query Data; 3.Exit.")
command = input("Enter the command [1/2/3]> ")
if command == "1":
name = input("Enter name:")
age = input("Enter age:")
insert_record("test_tbl1", name, age)
elif command == "2":
select_all("test_tbl1")
elif command == "3":
break
else:
print("Invalid command, please enter command again [1/2/3]")
if __name__ == "__main__":
main()
Error handling
When using mysqlclient 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, including the hostname, port, username, password, and database name, are correct.
Permission error: If you encounter a permission-related error, ensure that the user has sufficient permissions 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 that the inserted data type is correct.
In your code, use try-except statements to catch and handle these errors, ensuring that the program can handle errors gracefully instead of crashing directly.
References
For more information about how to connect to OceanBase AI Database, see Overview of connection methods.
For more information about how to create a database, see CREATE DATABASE.