This topic describes how to build an application using mysqlclient and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
- You have installed Python 3.x and pip.
- You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud tenant in MySQL-compatible mode. For more information, see Create a cluster instance and Create a tenant.
Procedure
- Check the versions of Python and pip.
- Install the mysqlclient library.
- Obtain the connection information for OceanBase Cloud.
- 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 the command prompt or PowerShell terminal, and run the python --version and pip --version commands to ensure that Python and pip are installed correctly.
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 implementation of the MySQL client library, which offers high performance and is suitable for scenarios with stringent performance requirements. When installing the mysqlclient library, you need to compile and link the MySQL C API to connect to OceanBase Cloud.
Method 1: Install using precompiled binaries (recommended)
Download the
whlfile compatible with your Python version and operating system platform from Download files.Open the 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 that 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 connection information of the OceanBase Cloud database
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information. Under the target tenant, choose Connect > Get connection string.
For more detailed operations, see Get connection string.
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_nameParameter description:
$host: The connection address of the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.$port: The connection port of the OceanBase Cloud database. The default value is 3306.$database_name: The name of the database to be accessed.Notice
The user connecting to the tenant must have the
CREATE,INSERT, andSELECTprivileges on this database. For more information about account privileges, see Create and manage accounts.$user_name: The account used to access the database.$password: The password of the account.
Example:
obclient -h t********.********.oceanbase.cloud -P3306 -u 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 connection information of the OceanBase Cloud database.
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, you can 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': 't5******.********.oceanbase.cloud', 'port': 3306, 'user': '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 the 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.pyExample output:
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 a name and press Enter.Example:
Enter name:A1At the
Enter age:prompt, enter an age and press Enter.Example:
Enter age:18A success message
Record inserted successfullyis returned. Finally, the command instructions are displayed, 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 failed data insertion.
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: eighteenAn error message is returned indicating the data insertion error:
(1366, 'Incorrect integer value'). Finally, the command instructions are displayed, prompting 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 CLI, enter the command
2and press Enter.Example:
Enter the command [1/2/3]> 2Displays the data of the table. Finally, it displays command instructions, prompting users to 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 number other than
1/2/3and press Enter.Example:
Enter the command [1/2/3]> AReturns an error message indicating
Invalid command, please enter command again [1/2/3]. Finally, it displays the command description, prompting the user 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
Sample project overview
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
File description:
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.
Introduction to config.py code
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 config.py. 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 config.py file obtained in this topic defines a dictionary variable named OCEANBASE_CONFIG, used to manage connection parameters for OceanBase Cloud.
Code:
OCEANBASE_CONFIG = {
'host': '$host',
'port': $port,
'user': '$user_name',
'password': '$password',
'database': '$database_name',
'charset': 'utf8mb4'
}
Parameter explanation:
$host: The connection address for OceanBase Cloud.$port: The connection port for OceanBase Cloud.$user_name: The account for accessing the database.$password: The account password.$database_name: 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.
Introduction to db.py code
The db.py file is a Python module that encapsulates database operations, primarily used to implement create, read, update, and delete (CRUD) operations on databases.
This file mainly includes the following sections:
Import the MySQLdb module and the database connection parameters.
Sample code:
import MySQLdb from config import OCEANBASE_CONFIGDefine a function to create a table.
Define the
create_tablefunction, which creates a table namedtest_tbl1in OceanBase Cloud. 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 any error messages.Sample code:
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 to insert data.
Define the
insert_recordfunction, which inserts a record into the 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 an SQL statement, perform the insertion operation, commit the transaction, and print the execution result or any error messages.Sample code:
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 to query table data.
Define the
select_allfunction, which queries all records in the 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 an SQL statement, execute the query, iterate through the results, and print all records. If an exception occurs, catch it and print the error message.Sample code:
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)
Introduction to 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 the functions defined in the
db.pyfile.Import the three functions
create_table,insert_record, andselect_allfrom the db module.Sample code:
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 them to enter again.Depending on the input command, the program performs different operations, including inserting data (1), querying data (2), and exiting the program (3).
Code:
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.Code:
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
OCEANBASE_CONFIG = {
'host': 't********.********.oceanbase.cloud',
'port': 3306, # 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 Cloud, 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 syntactically correct.
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 Cloud, see Overview of connection methods.