Connect to OceanBase Database by using mysqlclient

This topic describes 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 the command prompt or PowerShell terminal, and run the python --version and pip --version commands to check whether Python and pip are properly installed.

Here is an example:

PS C:\Windows\system32> python --version
Python 3.7.0
PS C:\Windows\system32> pip --version
pip 22.3.1 from d:\python\python37\lib\site-packages\pip (python 3.7)

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 database 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), this parameter is the IP address of an ODP. For direct connection, this parameter is 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 access.

  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-compatible mode.

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

• $password specifies the account password.

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.

   The following example shows 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 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 database operations in the CLI

• Example: Successfully insert data

  1. In the 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 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: Failed to insert data

  1. In the 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 data type of the age field is INT.

     Here is an example:

     Enter age: 18
     

  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.

     Here is an example:

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

• Example: Query data

  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: Enter an invalid command

  1. In the CLI, enter a value other than 1/2/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: Exit the program

  In the CLI, enter 3 and press Enter.

  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

Here is a breakdown of the files and directories:

• config.py: manages database connection configurations.

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

• main.py: 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 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.

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

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.

The sample code is as follows:

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, this parameter is the IP address of an ODP. For direct connection, this parameter is 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 Overview of connection methods.
• password specifies the password for connecting to the database.
• database specifies the name of the database that you want 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.

   The sample code is as follows:

   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.

   The sample 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 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.

   The sample 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 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.

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

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

   The sample code is as follows:

   from db import create_table, insert_record, select_all
   

2. Define a function for operating databases.

   Define the main function to implement a simple CLI program for database operations. 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. If you enter an invalid command, the program prompts you to re-enter a command.

   Based on the different input commands, the program executes different operations, including data insertion (command 1), data query (command 2), and program exit (command 3).

   The sample 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 executed. If the main module is just imported into another module, the main function is not called.

   The sample 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 scalability 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()

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.