Connect to OceanBase Database by using mysqlclient

This topic introduces how to build an application by using mysqlclient and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data query, and other basic operations.

Download the python-mysqlclient sample project

Prerequisites

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

Procedure

1. Check the versions of Python and pip.
2. Install the mysqlclient library.
3. Obtain the connection information of OceanBase Database.
4. Modify the database connection information in the config.py file.
5. Run the main.py file.
6. Perform corresponding operations in the interactive CLI.

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 correctly 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

mysqlclient is a C language-based MySQL client library that offers high performance and is suitable for scenarios with high performance requirements. When you install mysqlclient, you need to compile and link the MySQL C API to connect to OceanBase Database.

Step 3: Obtain the connection information of OceanBase Database

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

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

Parameters:

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

• $user_name: the tenant connection account. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

• $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 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 OceanBase Database.

1. Go to the python-mysqlclient project directory.

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

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

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

   OCEANBASE_CONFIG = {
       'host': '10.10.10.1',
       'port': 2881,
       'user': 'test_user001@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. Go 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 return 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 the corresponding operations in the interactive CLI

• Example of successfully inserting data.

  1. In the interactive CLI, enter 1 and press Enter.

     Here is an example:

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

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

     Here is an example:

     Enter name：A1
     

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

     Here is an example:

     Enter age：18
     

  4. The Record inserted successfully message is displayed, indicating that the data is inserted successfully. Then, the CLI displays a message that prompts you to enter 1, 2, or 3 and press Enter to perform the corresponding operation.

     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 CLI, enter 1 and press Enter.

     Here is an example:

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

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

     Here is an example:

     Enter name：A2
     

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

     Notice

     The age field requires an integer value.

     Here is an example:

     Enter age: 18
     

  4. The (1366, 'Incorrect integer value') message is displayed, indicating that the data insertion failed. Then, the CLI displays a message that prompts you to enter 1, 2, or 3 and press Enter to perform the corresponding operation.

     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 querying data.

  1. In the interactive CLI, enter 2 and press Enter.

     Here is an example:

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

  2. The data of the table is displayed. Then, the CLI displays a message that prompts you to enter 1, 2, or 3 and press Enter to perform the corresponding operation.

     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 CLI, enter a value other than 1, 2, or 3 and press Enter.

     Here is an example:

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

  2. The following error message is displayed: Invalid command, please enter command again [1/2/3]. Then, the CLI displays a message that prompts you to enter 1, 2, or 3 and press Enter to perform the corresponding operation.

     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 CLI, 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 package 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: manages database connection configurations.

• db.py: operates the database, including table creation, data insertion, data query, etc.

• main.py: the entry to the application, featuring a simple CLI. You can enter commands in the CLI to perform corresponding operations.

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

  Note

  The code provided in this topic only lists the version requirement for the mysqlclient library. You can run the pip install -r requirements.txt command to automatically install the required library.

Introduction to config.py

When you use Python to connect to a database, you need to specify the database connection parameters. These parameters can be placed in a separate configuration file, such as the config.py file. You can encapsulate the parameters in a dictionary so that other Python files can reference the dictionary to connect to a database, without the need to write the parameters to each file.

The config.py file obtained in this topic defines a dictionary variable named OCEANBASE_CONFIG, which is used to manage the connection parameters of OceanBase Database.

Sample code:

OCEANBASE_CONFIG = {
    'host': 'localhost',
    'port': 2881,  # The default port. You can modify it as needed.
    'user': 'user_name',
    'password': '',
    'database': 'db_name',
    'charset': 'utf8mb4'
}

Parameter description:

• host: the IP address for connecting to OceanBase Database. For connection through ODP, use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.
• port: the port for connecting to OceanBase Database. The default value is 2881 for direct connection and 2883 for connection through ODP.
• user: the username for connecting to the database. The format varies depending on the connection method. For direct connection, the format is {username}@{tenant name}. For connection through ODP, the format is {username}@{tenant name}#{cluster name}. For more information about how to connect to OceanBase Database, see Overview of connection methods.
• password: the password for connecting to the database.
• database: the name of the database that you want to connect to.
• charset: the character set for connecting to the database.

Introduction to db.py

The db.py file is a Python module that encapsulates database operations. It is mainly used for adding, deleting, modifying, and querying data in a database.

The file contains the following sections:

1. Import the MySQLdb module and database connection parameters.

   Sample code:

   import MySQLdb
   from config import OCEANBASE_CONFIG
   

2. Define a function for creating a table.

   The create_table function creates a table named test_tbl1 in OceanBase Database. The with statement is used to manage the lifecycle of database connections and cursor objects, ensuring secure closure of database connections and cursor objects and preventing memory leaks. An SQL statement is defined, executed, and the execution result or exception information is printed.

   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.

   The insert_record function inserts a record into a specified table. The record contains the name and age fields. The with statement is used to manage the lifecycle of database connections and cursor objects, ensuring secure closure of database connections and cursor objects and preventing memory leaks. An SQL statement is defined, an insert operation is performed, a transaction is committed, and the execution result or exception information is printed.

   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.

   The select_all function queries all records in a specified table. The with statement is used to manage the lifecycle of database connections and cursor objects, ensuring secure closure of database connections and cursor objects and preventing memory leaks. An SQL statement is defined, a query operation is performed, the query result is traversed, and all records are printed. 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 main.py

The main.py file in this topic demonstrates how to use Python and MySQLdb to operate a database. It provides an interactive CLI for performing database operations. You can use this program to create tables, insert records, and query all records, thereby implementing basic database operations.

This file mainly includes the following sections:

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 databases.

   Define the main function to implement a simple interactive CLI program for database operations. It first calls the create_table function to create a table named test_tbl1, and then enters a while loop to wait for you to enter a command. The program calls different functions for insertion or query operations based on your selection, until you enter the 3 command to exit the program. If you enter an invalid command, the program prompts you to re-enter it.

   The program performs different operations based on the different commands entered, including data insertion (1), data query (2), and program exit (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.

   Configure the main function to be called only when main.py is directly executed. If the program is imported into another module, the main function will not be executed.

   Sample code:

   if __name__ == "__main__":
       main()
   

   Note

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

Complete code

OCEANBASE_CONFIG = {
    'host': 'localhost',
    'port': 2881,  # The default port. You can modify the port number 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 you use mysqlclient to connect to OceanBase Database, various errors may occur. The following table describes some common errors and their solutions.

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

2. Privilege error: If you encounter a privilege error, make sure that the user has sufficient privileges to perform the required operation.

3. SQL syntax error: If there is a syntax error in your SQL statement, check whether the syntax is correct.

4. Data type error: If the data type of the inserted data does not match the table definition, make sure that the data type is correct.

In your code, you can use the try-except statement to capture and handle these errors, ensuring that the program can handle errors gracefully instead of crashing.

References

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

• For more information about how to create a database, see CREATE DATABASE.