Connect to OceanBase Cloud using mysqlclient

This topic describes how to build an application by using mysqlclient and OceanBase Cloud. The application can perform basic operations such as creating a table, inserting data, and querying data.

Download the python-mysqlclient sample project Connect to OceanBase Cloud using mysqlclient (MySQL mode)

Prerequisites

• You have installed Python 3.x and pip.
• You have registered an OceanBase Cloud account and created an instance and an OceanBase Cloud MySQL compatible tenant. For more information, see Create an instance and Create a tenant.

Procedure

1. Check the versions of Python and pip.
2. Install the mysqlclient library.
3. Obtain the connection information of the OceanBase Cloud database.
4. Modify the database connection information in the config.py file.
5. Run the main.py file.
6. Perform the corresponding 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 properly installed.

Here is an 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

The mysqlclient library is a high-performance MySQL client library implemented in C, suitable for scenarios with high performance requirements. When installing the mysqlclient library, you need to compile and link the MySQL C API to connect to the OceanBase Cloud database.

Step 3: Obtain the connection information of the OceanBase Cloud database

1. Log in to the OceanBase Cloud console. On the instance list page, expand the information of the target instance, and in the target tenant, select Connect > Get Connection String.

   For more information, see Obtain a connection string.

2. Fill in the corresponding information in the URL based on the created OceanBase Cloud database.

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

   Parameter description:

   • $host: the connection address of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.

   • $port: the connection port of the OceanBase Cloud database, which is 3306 by default.

   • $database_name: the name of the database to be accessed.

     Notice

     The user of the tenant must have the CREATE, INSERT, and SELECT permissions on the database. For more information about account permissions, see Create and manage an account.

   • $user_name: the account for accessing the database.

   • $password: the password of the account.

   Here is an 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 the connection information of the OceanBase Cloud database.

1. Navigate to the python-mysqlclient project folder.

2. Modify the database connection information in the config.py file.

   • In a Windows environment, open the config.py file using a text editor and modify the database connection information to match the actual situation.
   • In a Linux environment, use the vi config.py or vim config.py command to edit the config.py file and modify the database connection information to match the actual situation.

   Here is an example of the database connection information in the config.py file:

   OCEANBASE_CONFIG = {
       'host': 't5******.********.oceanbase.cloud',
       'port': 3306,
       'user': 'mysql001',
       'password': '******',
       'database': 'test',
       'charset': 'utf8mb4'
   }
   

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.

1. Navigate to the python-mysqlclient project directory.

   Here is an example:

   cd /home/admin/python-mysqlclient
   

2. Run the following command to start the main.py program.

   python main.py
   

   The output is as follows:

   Table created successfully
   Instruction: 1.Insert Data; 2.Query Data; 3.Exit.
   Enter the command [1/2/3]>
   

Step 6: Perform the corresponding operations in the interactive command-line interface

• Example of successful data insertion.

  1. In the interactive command-line interface, enter 1 and press Enter.

     Here is an example:

     Enter the command [1/2/3]> 1
     

  2. After the prompt Enter name:, enter your name and press Enter.

     Here is an example:

     Enter name: A1
     

  3. After the prompt Enter age:, enter your age and press Enter.

     Here is an example:

     Enter age: 18
     

  4. The system returns a message indicating that the data was inserted successfully, Record inserted successfully. Finally, it displays a message indicating that you can enter 1, 2, or 3 and press Enter to perform the corresponding operations.

     Here is an example:

     Record inserted successfully
     Instruction: 1.Insert Data; 2.Query Data; 3.Exit.
     Enter the command [1/2/3]>
     

• Example of failed data insertion.

  1. In the interactive command-line interface, enter 1 and press Enter.

     Here is an example:

     Enter the command [1/2/3]> 1
     

  2. After the prompt Enter name:, enter your name and press Enter.

     Here is an example:

     Enter name: A2
     

  3. After the prompt Enter age:, enter your age and press Enter.

     Notice

     The age field is of the integer type.

     Here is an example:

     Enter age: eighteen
     

  4. The system returns an error message, (1366, 'Incorrect integer value'). Finally, it displays a message indicating that you can enter 1, 2, or 3 and press Enter to perform the corresponding operations.

     Here is an example:

     (1366, 'Incorrect integer value')
     Instruction: 1.Insert Data; 2.Query Data; 3.Exit.
     Enter the command [1/2/3]>
     

• Example of data query.

  1. In the interactive command-line interface, enter 2 and press Enter.

     Here is an example:

     Enter the command [1/2/3]> 2
     

  2. The system displays the data in the table. Finally, it displays a message indicating that you can enter 1, 2, or 3 and press Enter to perform the corresponding operations.

     Here is an 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.

  1. In the interactive command-line interface, enter a command other than 1/2/3 and press Enter.

     Here is an example:

     Enter the command [1/2/3]> A
     

  2. The system returns an error message, Invalid command, please enter command again [1/2/3]. Finally, it displays a message indicating that you can enter 1, 2, or 3 and press Enter to perform the corresponding operations.

     Here is an 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 3 and press Enter to exit the program.

  Here is an example:

  Enter the command [1/2/3]> 3
  

Project code introduction

Click python-mysqlclient to download the project code, which is a compressed file named python-mysqlclient.zip.

After decompressing it, you will find 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: used to manage database connection configuration information.

• db.py: used to operate the database, including creating tables, inserting data, and querying data.

• main.py: the entry point of the application, which contains a simple user interaction interface. Users can execute corresponding operations by entering commands.

• requirements.txt: lists the required Python libraries for the project.

  Note

  The code obtained in this topic only lists the version requirements of the mysqlclient library. You can run the pip install -r requirements.txt command to install the required libraries.

Introduction to the config.py file

When you use Python to connect to a database, you need to specify the database connection parameters. You can store these parameters in a separate configuration file, such as the config.py file. By encapsulating these parameters in a dictionary, you can avoid repeatedly writing the parameters in each file. Instead, you can directly reference the dictionary in other Python files to connect to the database.

The config.py file obtained in this topic defines a dictionary variable named OCEANBASE_CONFIG to manage the connection parameters of the OceanBase Cloud database.

Sample code:

OCEANBASE_CONFIG = {
    'host': '$host',
    'port': $port,
    'user': '$user_name',
    'password': '$password',
    'database': '$database_name',
    'charset': 'utf8mb4'
}

Parameter description:

• $host: the connection address of the OceanBase Cloud database.
• $port: the connection port of the OceanBase Cloud database.
• $user_name: the account for accessing the database.
• $password: the password of the account.
• $database_name: the name of the database to be connected.
• charset: the character set used to connect to the database.

Introduction to the db.py file

The db.py file is a module written in Python that encapsulates database operations. It is mainly used to implement the add, delete, modify, and query operations on a database.

The file includes the following parts:

1. Import the MySQLdb module and the database connection parameters.

   Sample code:

   import MySQLdb
   from config import OCEANBASE_CONFIG
   

2. Define a function for creating a table.

   Define the create_table function, which creates a table named test_tbl1 in the OceanBase Cloud database. The with statement is used to manage the lifecycle of the database connection and cursor objects, ensuring the safe closure of the database connection and cursor objects and avoiding memory leaks. Define the SQL statement, execute the SQL statement, and print the execution result or exception information.

   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)
   

3. Define a function for inserting data.

   Define the insert_record function, which inserts a record containing the name and age fields into the specified table. The with statement is used to manage the lifecycle of the database connection and cursor objects, ensuring the safe closure of the database connection and cursor objects and avoiding memory leaks. Define the SQL statement, execute the insert operation, commit the transaction, and print the execution result or exception information.

   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)
   

4. Define a function for querying table data.

   Define the select_all function, which queries all records in the specified table. The with statement is used to manage the lifecycle of the database connection and cursor objects, ensuring the safe closure of the database connection and cursor objects and avoiding memory leaks. Define the SQL statement, execute the query operation, traverse the query results, and print all records. If an exception occurs, the exception is captured and the exception information is printed.

   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 the main.py file

The main.py file demonstrates how to use Python and the MySQLdb module to operate on a database. It also provides an interactive command-line interface for you to perform the corresponding operations. You can use this program to create tables, insert records, and query all records, thereby achieving basic database operations.

The file includes the following parts:

1. Import the functions defined in the db.py file.

   Import the create_table, insert_record, and select_all functions from the db module.

   Sample code:

   from db import create_table, insert_record, select_all
   

2. Define a function for operating on the database.

   Define the main function, which implements a simple command-line interactive program for operating on the database. It first calls the create_table function to create a table named test_tbl1. Then, it enters a while loop to wait for user input. Based on the user's selection, the program calls different functions to perform insert or query operations until the user enters the 3 command to exit the program. If the user enters an invalid command, the program prompts the user to re-enter the command.

   The program performs different operations based on the input command, including inserting data (1), querying data (2), and exiting the program (3).

   Sample 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]")
   

3. Set the usage scenario of the main function.

   Set the scenario where the main function is called only when main.py is directly run. If the program is imported into another module, the main function is not executed.

   Sample code:

   if __name__ == "__main__":
       main()
   

   Note

   This writing method ensures that the main function is not automatically executed when the module is imported, thereby ensuring the reusability and scalability of the program.

Complete code

OCEANBASE_CONFIG = {
    'host': 't********.********.oceanbase.cloud',
    'port': 3306,  # Default port. You can modify the port 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. Here are some common errors and their solutions:

1. Connection error: 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. Permission error: If you encounter a permission-related error, ensure 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, ensure that the data type of the inserted data is correct.

In the code, we use the try-except statement to capture and handle these errors, ensuring that the program can handle errors gracefully without crashing.

References

• For more information about how to connect to OceanBase Cloud, see Overview of connection methods.