Connect to OceanBase Database by using mysqlclient

This topic describes how to build an application by using mysqlclient, OceanBase Database, and Python. It will cover basic operations such as creating tables, inserting data, and querying data.

To download the project code, click python-mysqlclient.

Prerequisites

• Python 3.x and pip were installed.
• OceanBase Database was installed.

Procedure

1. Obtain the connection information for OceanBase Database.
2. Get familiar with the python-mysqlclient project code.
3. Check Python and pip.
4. Install the necessary Python library.
5. Create a database in OceanBase Database.
6. Modify the database connection information in the config.py file.
7. Run the main.py file.
8. Perform database operations on the CLI.

Step 1: Obtain the connection information for OceanBase Database

Contact the OceanBase deployment team or administrator to get the required database connection details.

For example:

obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@mysql001#cluster_name -p****** -Dtest

The parameters are described as follows:

• -h: the IP address for connecting to OceanBase Database. If you connect to OceanBase Database by using an OceanBase Database Proxy (ODP), this parameter specifies the ODP address. If you connect to OceanBase Database directly, this parameter specifies the IP address of an OBServer node.
• -P: the port for connecting to OceanBase Database. If you connect to OceanBase Database by using an ODP, the default value is 2883. (You can customize the value when you deploy the ODP.) If you connect to OceanBase Database directly, the default value is 2881. (You can customize the value when you deploy OceanBase Database).
• -u: the connection account for the tenant. If you connect to OceanBase Database by using an ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. If you connect to OceanBase Database directly, the format is username@tenant name.
• -p: the account password.
• -D: the name of the database to be accessed. You can change it to the business database.

For more information about connection strings, see Connect to an OceanBase Database tenant by using the mysql client.

Step 2: Get familiar with the python-mysqlclient project code.

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

After decompressing it, you will get a folder named python-mysqlclient with the following directory structure:

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

Files in this directory are described as follows:

• config.py: This file is used to manage database connection configurations.

• db.py: This file is used to manipulate databases. For example, you can use the file to create tables, insert data, and query data.

• main.py: This file is the entrance to the application and provides a simple command-line interface (CLI). You can enter commands on the CLI to perform corresponding operations.

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

  Note

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

Introduction to the config.py file

When you connect to a database by using Python, it is necessary to specify the database connection parameters. These parameters can be stored in a separate configuration file, such as config.py. By encapsulating these parameters into a dictionary, it avoids the hassle of repeatedly writing these parameters in each file. Instead, you can directly reference this dictionary in other Python files to connect to the database.

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

The code is as follows:

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

The parameters are described as follows

• host: the IP address for connecting to OceanBase Database. If you connect to OceanBase Database directly, this parameter specifies the IP address of an OBServer node. If you connect to OceanBase Database by using an ODP, this parameter specifies the ODP address.

• port: the port for connecting to OceanBase Database. If you connect to OceanBase Database directly, the default value is 2881. (You can customize the value when you deploy OceanBase Database.) If you connect to OceanBase Database by using an ODP, the default value is 2883. (You can customize the value when you deploy the ODP.)

• user: the username for connecting to OceanBase Database. If you connect to OceanBase Database directly, the format is {username}@{tenant name}. If you connect to OceanBase Database by using an ODP, the format is {username}@{tenant name}#{cluster name}. For more information about how to connect to OceanBase Database, see Overview.

• password: the password for connecting to OceanBase Database.

• database: the name of the database to be connected to.

• charset: the character set for connecting to OceanBase Database.

Introduction to the db.py file

The db.py file is a module written in the Python language that encapsulates database operations. You can use it to implement CRUD (Create, Read, Update, Delete) operations on the database.

This file includes the following parts:

1. Import the MySQLdb module and database connection parameters.

   The code is as follows:

   import MySQLdb
   from config import OCEANBASE_CONFIG
   

2. Define the function for creating a table.

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

   The 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)
   

3. Define the function for inserting data.

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

   The 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)
   

4. Define the function for querying table data.

   Define the select_all function to query all records in a specified table. Use the with statement to manage the life cycle of database connections and cursor objects. This ensures that database connections and cursor objects can be securely closed without memory leaks. 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.

   The 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)
   

Introduction to the main.py file

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

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

   The code is as follows:

   from db import create_table, insert_record, select_all
   

2. Define a function for manipulating databases.

   Define the main function to implement a simple CLI program for database manipulation. The program 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 the corresponding function to insert or query data based on your command until you enter the 3 command to exit the program.

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

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

3. Set the application scenario of the main function.

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

   The code is as follows:

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

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()

Step 3: Check Python and pip

Open a terminal in the command prompt or PowerShell and run the python --version and pip --version commands to check whether Python and pip are properly installed.

For example:

[admin@xxx /home/admin]
$python --version
Python 3.6.8

[admin@xxx /home/admin]
$pip --version
pip 21.3.1 from /usr/local/lib/python3.6/site-packages/pip (python 3.6)

Step 4: Install the necessary Python 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 5: Create a database in OceanBase Database

1. Connect to OceanBase Database.

   obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -A
   

2. Execute the following SQL statement to create a database named test_db:

   CREATE DATABASE test_db;
   

   For more information about the syntax for creating a database, see CREATE DATABASE.

Step 6: Modify the database connection information in the config.py file

Modify the database connection information in the config.py file according to the information obtained in Step 1.

1. Go to the python-mysqlclient project directory.

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

   • In Windows, use the text editor to open the config.py file and modify the database connection information based on the actual situation.
   • In Linux, 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.

   The following example shows the database connection information in the config.py file:

   OCEANBASE_CONFIG = {
       'host': '10.10.10.1',
       'port': 2881,
       'user': 'root@mysql001',
       'password': '******',
       'database': 'test_db',
       'charset': 'utf8mb4'
   }
   

Step 7: Run the main.py file

Open a terminal in the command prompt or PowerShell and run the python main.py command to start the program.

1. Go to the python-mysqlclient project directory.

   For example:

   cd /home/admin/python-mysqlclient
   

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

   python main.py
   

   The response is as follows:

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

Step 8: Perform database operations on the CLI

• Insert data (succeeded)

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

     For example:

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

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

     For example:

     Enter name: A1
     

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

     For example:

     Enter age: 18
     

  4. The Record inserted successfully message is displayed, 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.

     For example:

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

• Insert data (failed)

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

     For example:

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

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

     For example:

     Enter name: A2
     

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

     Notice

     The data type of the age field is INT.

     For example:

     Enter age: Eighteen
     

  4. The (1366, 'Incorrect integer value') message is displayed, 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.

     For example:

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

• Query data

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

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

     For example:

     (1, 'A1', 18)
     Instruction: 1.Insert Data; 2.Query Data; 3.Exit.
     Enter the command [1/2/3]>
     

• Enter an invalid command

  1. On the CLI, enter a value other than 1/2/3 and press Enter.

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

     For 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]>
     

• Exit the program

  On the CLI, enter 3 and press Enter.

  For example:

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

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.