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, and data query.

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 database operations in the CLI.

Step 1: Check the versions of Python and pip

Open Command Prompt or PowerShell, 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

When you install the mysqlclient library, you must compile and link the MySQL C API to connect to OceanBase Database. Therefore, you must install the development files of MySQL Connector C or MySQL C API for later compilation.

Step 3: Obtain the connection information of OceanBase Database

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

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

where

• $host specifies 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 specifies 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 specifies the name of the database to be accessed.

  Notice

  The user for connecting to a 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 specifies the username of the account in the format of user@tenant#cluster name or username@SERVICE:service name. It is specified by the -u parameter. When the value is in the user@tenant#cluster name format, the default tenant is sys and the default administrator is root. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through ODP.

• $password specifies the password of the account.

For more information about the connection string, 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 config.py file in the python-mysqlclient/ directory based on the connection string 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 the 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 Command Prompt or PowerShell, and run the python main.py command to start the application.

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 application:

   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 database operations on the CLI

• Example of successful data insertion

  1. On the CLI, enter 1 and press Enter.

     Here is an example:

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

  2. Enter a name after the Enter name: prompt and press Enter.

     Here is an example:

     Enter name: A1
     

  3. Enter an age after the Enter age: prompt and press Enter.

     Here is an example:

     Enter age: 18
     

  4. The system displays Record inserted successfully, indicating that the data is successfully inserted. Finally, an instruction is displayed, prompting 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. On the CLI, enter 1 and press Enter.

     Here is an example:

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

  2. Enter a name after the Enter name: prompt and press Enter.

     Here is an example:

     Enter name: A2
     

  3. Enter an age after the Enter age: prompt and press Enter.

     Notice

     The data type of the age field is INT.

     Here is an example:

     Enter age: 18
     

  4. The system displays (1366, 'Incorrect integer value'), indicating that the data failed to be inserted. Finally, an instruction is displayed, prompting 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 data query

  1. In the 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. Finally, an instruction is displayed, prompting 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 incorrect command

  1. In the CLI, enter a value other than 1, 2, and 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]. Finally, an instruction is displayed, prompting 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 application

  In the CLI, enter 3 and press Enter.

  Here is an example:

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

Project code

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

Decompress the package to obtain a folder named python-mysqlclient. The directory structure is as follows:

python-mysqlclient
├── config.py
├── db.py
├── main.py
└── requirements.txt

Here is a breakdown of the files and directories:

• config.py: manages database connection configurations.

• db.py: operates the database, such as creating tables, inserting data, and querying data.

• main.py: serves as the entry to the application and provides 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 requirements.txt file in the package obtained in this topic lists only the mysqlclient library and its version requirements. You can run the pip install -r requirements.txt command to automatically install the required library.

Code in config.py

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

Code in 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.

Here is the sample code:

OCEANBASE_CONFIG = {
    'host': 'localhost',
    'port': port,
    'user': 'user_name',
    'password': '',
    'database': 'db_name',
    'charset': 'utf8mb4'
}

where

• host specifies 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 specifies the port for connecting to OceanBase Database. The default value is 2881 for direct connection and 2883 for connection through ODP.
• user specifies the username for connecting to the database. It is in the format of {username}@{tenant name} for direct connection or in the format of {username}@{tenant name}#{cluster name} for connection through ODP. For more information about how to connect to OceanBase Database, see Connection methods.
• password specifies the password for connecting to the database.
• database specifies the name of the database to connect to.
• charset specifies the character set for connecting to the database.

Code in db.py

The db.py file is a Python module in which database operations are encapsulated. You can use it to add, delete, modify, and query data in a database.

To configure the db.py file, perform the following steps:

1. Import the MySQLdb module and database connection parameters.

   Here is the sample code:

   import MySQLdb
   from config import OCEANBASE_CONFIG
   

2. Define a function for creating a table.

   1. Define the create_table function to create a table named test_tbl1 in OceanBase Database.
   2. Use the with statement to manage the lifecycle of database connections and cursor objects. This ensures that database connections and cursor objects can be securely closed without memory leaks.
   3. Define an SQL statement, execute the statement, and print the execution result or error message.

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

   1. Define the insert_record function to insert a record into a specified table. The record contains the name and age fields.
   2. Use the with statement to manage the lifecycle of database connections and cursor objects. This ensures that database connections and cursor objects can be securely closed without memory leaks.
   3. Define an SQL statement, execute the statement, commit the transaction, and print the execution result or error message.

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

   1. Define the select_all function to query all records in a specified table.
   2. Use the with statement to manage the lifecycle of database connections and cursor objects. This ensures that database connections and cursor objects can be securely closed without memory leaks.
   3. Define an SQL statement, execute the statement, traverse query results, and print all records. If an error occurs, capture the error and print the error message.

   Here is the 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)
   

Code in main.py

The main.py file in this topic shows how to use Python and MySQLdb to operate a database. The file provides a CLI where you can run commands to perform database operations. You can use this application to implement basic database operations such as creating tables, inserting records, and querying all records.

To configure the main.py file, perform the following steps:

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

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

   Here is the sample code:

   from db import create_table, insert_record, select_all
   

2. Define a function for operating the database.

   Define the main function to implement a simple CLI application for database operations. The application 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 application calls the corresponding function to insert or query data based on your command until you enter the 3 command to exit the application. If you enter an invalid command, the application prompts you to re-enter a command.

   Command 1 is to insert data, 2 is to query data, and 3 is to exit the application. If you enter an invalid command, the application prompts you to re-enter a command.

   Here is the 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 application scenario of the main function.

   Set the main function to be called only when the main.py file is directly executed. If the main module is just imported into another module, the main function is not called.

   Here is the sample code:

   if __name__ == "__main__":
       main()
   

   Note

   This setting can avoid automatic execution of the main function when the main module is imported, thus ensuring the reusability and extensibility of the application.

Complete code examples

OCEANBASE_CONFIG = {
    'host': 'localhost',
    'port': port,
    '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()

References

• For more information about how to connect to OceanBase Database, see Connection methods.

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