Connect to OceanBase Database by using mysqlclient - ApsaraDB for OceanBase

This topic describes how to use mysqlclient and OceanBase Database to build an application that performs basic database operations such as creating tables, inserting data, and querying data.

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

Check the versions of Python and pip.
Install the mysqlclient library.
Obtain the connection information of OceanBase Database.
Modify the database connection information in the config.py file.
Run the main.py file.
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.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.

Windows

Method 1: Install the mysqlclient library using precompiled binary files

In a Windows environment, you can install the mysqlclient library using precompiled binary files. Download the whl file that is compatible with your Python version and operating system from Download files and use the pip install command to install it.

Here is an example:

Open the Command Prompt or PowerShell terminal and run the following commands to install the necessary Python libraries.

Navigate to the directory where the whl file is stored.
```
cd D:\download
```

Run the following command to install the mysqlclient library:

PS D:\download> pip install mysqlclient-2.2.0-cp311-cp311-win_amd64.whl
Processing d:\download\mysqlclient-2.2.0-cp311-cp311-win_amd64.whl
Installing collected packages: mysqlclient
Successfully installed mysqlclient-2.2.0

Method 2: Directly install the mysqlclient library

Install MySQL.
Before you can use mysqlclient, you need to install the MySQL database server. The mysqlclient library depends on the MySQL client programs and library files to communicate with MySQL. You can download the installer from the MySQL Community Downloads page and follow the instructions to install it.
Install a C compiler.
In a Windows environment, you can choose to install Visual Studio or MinGW as the C compiler.
Install development tools.
In a Windows environment, you can use MySQL Connector/C to obtain the MySQL development libraries. To install MySQL Connector/C, perform the following steps:
1. Go to the MySQL Community Downloads page.
2. Download the appropriate installation package for your operating system.
3. Run the downloaded installation package and follow the instructions in the installation wizard to install it.
4. During the installation, select the Custom installation type and ensure that Development Components are installed.
5. After the installation is complete, restart your computer.
Install the mysqlclient library.
Open the Command Prompt or PowerShell terminal and run the following commands to install the necessary Python libraries.
1. Run the following command to navigate to the python-mysqlclient directory:
```
cd python-mysqlclient
```
2. Run the following command to install the mysqlclient library:
```
pip install -r requirements.txtB
```
Note
You can also directly run the pip install mysqlclient command to install the mysqlclient library.

Linux

In Linux, Python C extensions rely on the Python.h header file, which is usually contained in the python3-devel package. If the python3-devel package is not installed, an error will be returned when you compile the MySQL C API, indicating that the Python.h header file cannot be found.

Open the Command Prompt or PowerShell terminal and run the following commands to install the necessary Python libraries.

Check whether the python3-devel package is installed.
Run the following command to check whether the python3-devel package is installed:
```
rpm -q python3-devel
```
- If the package is installed, the following information is displayed:
```
python3-devel-3.x.x-x.el7.x86_64
```
  Here, 3.x.x indicates the version of Python3, and x86_64 indicates the CPU architecture of the system.
- If the package is not installed, the following information is displayed:
```
package python3-devel is not installed
```
  In this case, you can run the following command to install the python3-devel package:
```
sudo yum install python3-devel
```
  After the installation is complete, run the rpm -q python3-devel command again to check whether the installation is successful.
Install the mysqlclient library.
1. Run the following command to navigate to the python-mysqlclient project directory:
```
cd python-mysqlclient
```
2. Run the following command to install the mysqlclient library:
```
sudo pip install -r requirements.txt
```
Note
You can also directly run the sudo pip install mysqlclient command to install the mysqlclient library.

Step 3: Obtain the connection information of OceanBase Database

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

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

The parameters are described as follows:

$host: the IP address for connecting to OceanBase Database.
$port: the port for connecting to OceanBase Database. The default port is 3306 in MySQL mode.
$database_name: the name of the database to access.
$user_name: the username of the tenant account.
$password: the password of the tenant account.

For more information about connection strings, see Obtain connection parameters.

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P3306 -utest_user001 -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.

Go to the python-mysqlclient project directory.
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 config.py:
```
OCEANBASE_CONFIG = {
 'host': 'xxx.xxx.xxx.xxx',
 'port': 3306,
 'user': 'test_user001',
 '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.

Go to the python-mysqlclient project directory.
```
cd /home/admin/python-mysqlclient
```

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.
  Note
  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 described 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'
}

The parameters are described as follows:

host: the IP address for connecting to OceanBase Database.
port: the port for connecting to OceanBase Database. The default port is 3306.
user: the username of the tenant account.
password: the password of the tenant account.
database: the name of the database to connect to.
charset: the character set for connecting to the database.

Important

You need to modify the parameter values in the sample code based on your project requirements and database settings.

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:

Import the MySQLdb module and database connection parameters.
The sample code is as follows:
```
import MySQLdb
from config import OCEANBASE_CONFIG
```
Define a 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 lifecycle 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 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)
```
Define a 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 lifecycle 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 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)
```
Define a 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 lifecycle 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 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:

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
```
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]")
```
Set the application scenario of the main function.
Specify that the main function is called only when the main.py file is directly executed. If the main module is 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

config.py

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

db.py

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)

main.py

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.