Connect to OceanBase Database by using PyMySQL|V4.2.5| docs|Distributed Database

Connect to OceanBase Database by using PyMySQL

Last Updated：2025-01-02 01:58:40 Updated

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

Download the python-pymysql 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 PyMySQL library.
Obtain the connection information of OceanBase Database.
Modify the database connection information in the config.py file.
Run the main.py file.

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.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 PyMySQL library

Open Command Prompt or PowerShell, and run the following commands to install the PyMySQL library.

Run the following command to go to the python-pymysql project directory:

Here is an example:
```
cd python-pymysql
```
Run the following command to install the Python library required by the project:

Here is an example:
```
pip install -r requirements.txt
```

Note

You can also run the pip install pymysql command to install the PyMySQL library.

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

Notice

The user for connecting to a tenant must have the CREATE, INSERT, DROP, 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 python-pymysql/config.py file based on the information obtained in Step 3: Obtain the connection information of OceanBase Database.

Go to the python-pymysql 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.
Here is an example of the database connection information in the config.py file:
```
DB_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 main.py file to query data and output the query result.

Go to the python-pymysql project directory.

Here is an example:
```
cd D:\demo\demo\python-pymysql
```

Run the main.py file.

Here is an example:

python main.py

The return result is as follows:

2023-11-10 16:56:48,021 - INFO - Start executing the script
2023-11-10 16:56:48,021 - INFO - Start creating the table
2023-11-10 16:56:48,281 - INFO - Table creation successful
2023-11-10 16:56:48,281 - INFO - Start inserting data
2023-11-10 16:56:48,540 - INFO - Data insertion successful
(1, 'John', 20)
(2, 'Lucy', 25)
(3, 'Tom', 30)
2023-11-10 16:56:48,737 - INFO - Start dropping the table
2023-11-10 16:56:48,999 - INFO - Table dropped successfully
2023-11-10 16:56:48,999 - INFO - Script execution completed

Project code introduction

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

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

python-pymysql
├── config.py
├── test_sql.py
├── main.py
└── requirements.txt

Here is a breakdown of the files and directories:

config.py: stores database connection configurations.
test_sql.py: stores SQL statements.
main.py: serves as the entry to the application and provides a simple CLI for basic database operations such as creating tables, inserting data, querying data, and dropping tables.
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 PyMySQL library and its version requirements. You can run the sudo pip install -r requirements.txt command to automatically install the required library.

Code in config.py

Code in the config.py file defines the database connection information. Perform the following steps to configure the config.py file:

Specify the IP address, port number, username, password, database name, and character set for connecting to OceanBase Database.

Here is the sample code:

DB_CONFIG = {
    'host': '$host',
    'port': $port,
    'user': '$user_name',
    'password': '$password',
    'database': '$database_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_name specifies the username for connecting to the database.
$password specifies the password for connecting to the database.
$database_name specifies the name of the database to connect to.
charset specifies the character set for connecting to the database.

Notice

You need to modify the parameter values in the sample code based on the actual environment and database settings.

Code in test_sql.py

Code in the test_sql.py file obtained in this topic defines SQL statements for database operations such as creating tables, inserting data, querying data, and dropping tables. These SQL statements can be executed after the application connects to the database through PyMySQL.

Perform the following steps to configure the test_sql.py file:

Define an SQL statement for creating a table.

Define an SQL statement that creates the test_pymysql table with three fields: id, name, and age, where id is an auto-increment primary key.

Here is the sample code:
```
CREATE_TABLE_SQL = '''
CREATE TABLE test_pymysql (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
age INT(11) NOT NULL,
PRIMARY KEY (id)
)
'''
```
Define an SQL statement for inserting data.

Define an SQL statement that inserts three rows into the test_pymysql table, with each row containing the name and age fields. The value of each field is passed in as a placeholder %s when the SQL statement is executed.

Here is the sample code:
```
INSERT_DATA_SQL = '''
INSERT INTO test_pymysql (name, age) VALUES 
(%s, %s),
(%s, %s),
(%s, %s)
'''
```
Define an SQL statement for querying data.

Define an SQL statement that queries all data from the test_pymysql table.

Here is the sample code:
```
SELECT_DATA_SQL = '''
SELECT * FROM test_pymysql
'''
```
Define an SQL statement for dropping a table.

Define an SQL statement that drops the test_pymysql table.

Here is the sample code:
```
DROP_TABLE_SQL = '''
DROP TABLE test_pymysql
'''
```

Code in main.py

Code in the main.py file obtained in this topic calls the pymysql module to connect to the MySQL mode of OceanBase Database, calls the logging module to output logs, and implements database operations such as creating tables, inserting data, querying data, and dropping tables.

Perform the following steps to configure the main.py file:

Import the required modules.
1. Import the logging module.
2. Import the pymysql module.
3. Import the config.py module, which defines the database connection information.
4. Import the test_sql.py module, which defines SQL statements for database operations.
Here is the sample code:
```
import logging
import pymysql
from config import DB_CONFIG
from test_sql import CREATE_TABLE_SQL, INSERT_DATA_SQL, SELECT_DATA_SQL, DROP_TABLE_SQL
```

Set the level and format of logging, and generate an INFO log to indicate that script execution starts.

Here is the sample code:

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logging.info('Start executing the script')

Define a function for creating a table.
1. Define a function named create_table() and generate an INFO log to indicate that table creation starts.
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. Execute the SQL statement for creating a table, commit the transaction, and generate a success log or roll back the transaction and generate an error log.
Here is the sample code:
```
def create_table():
    logging.info('Start creating the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(CREATE_TABLE_SQL)
                conn.commit()
                logging.info('Table creation successful')
            except Exception as e:
                conn.rollback()
                logging.error('Table creation failed, Reason: %s' % e)
```

Define a function for inserting data.

Define a function named insert_data() and generate an INFO log to indicate that data insertion starts.
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.
Execute the SQL statement for inserting data, commit the transaction, and generate a success log or roll back the transaction and generate an error log.

Here is the sample code:

def insert_data():
    logging.info('Start inserting data')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                data = [('John', 20), ('Lucy', 25), ('Tom', 30)]
                flattened_data = [d for item in data for d in item]
                cursor.executemany(INSERT_DATA_SQL, [flattened_data])
                conn.commit()
                logging.info('Data insertion successful')
            except Exception as e:
                conn.rollback()
                logging.error('Data insertion failed, Reason: %s' % e)

Define a function for querying data.
1. Define a function named select_data() to query data from a 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. Use the execute() method to execute the SQL statement defined by SELECT_DATA_SQL to query data.
4. Use the fetchall() method to obtain the query results and output the results row by row through the for loop.
Here is the sample code:
```
def select_data():
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            cursor.execute(SELECT_DATA_SQL)
            result = cursor.fetchall()
            for row in result:
                print(row)
```

Define a function for dropping a table.

Define a function named drop_table() that uses pre-defined database connection information (DB_CONFIG) and the SQL statement for dropping tables (DROP_TABLE_SQL). The function drops tables and prints logs to show the operation result. If the table fails to be dropped, an error message is printed.

Here is the sample code:

def drop_table():
    logging.info('Start dropping the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(DROP_TABLE_SQL)
                conn.commit()
                logging.info('Table dropped successfully')
            except Exception as e:
                conn.rollback()
                logging.error('Table drop failed, Reason: %s' % e)

Define the entry to the application, which is mainly used to perform database operations.

First, determine whether the current module is directly run as the main program. If yes, perform the following operations:
1. Call the create_table() function to create a table.
2. Call the insert_data() function to insert data into a table.
3. Call the select_data() function to query data from a table.
4. Call the drop_table() function to drop a table.
Here is the sample code:
```
if __name__ == '__main__':
    create_table()
    insert_data()
    select_data()
    drop_table()
```
Generate an INFO log to indicate that script execution is completed.

Here is the sample code:
```
logging.info('Script execution completed')
```

Complete code

config.py

test_sql.py

main.py

# Create a database connection.
DB_CONFIG = {
    'host': '$host',
    'port': $port,
    'user': '$user_name',
    'password': '$password',
    'database': '$database_name',
    'charset': 'utf8mb4'
}

# Create a table.
CREATE_TABLE_SQL = '''
CREATE TABLE test_pymysql (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL,
  age INT(11) NOT NULL,
  PRIMARY KEY (id)
  )
'''

# Insert data.
INSERT_DATA_SQL = '''
INSERT INTO test_pymysql (name, age) VALUES 
(%s, %s),
(%s, %s),
(%s, %s)
'''

# Query data.
SELECT_DATA_SQL = '''
SELECT * FROM test_pymysql
'''

# Drop the table.
DROP_TABLE_SQL = '''
DROP TABLE test_pymysql
'''

import logging
import pymysql
from config import DB_CONFIG
from test_sql import CREATE_TABLE_SQL, INSERT_DATA_SQL, SELECT_DATA_SQL, DROP_TABLE_SQL

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logging.info('Start executing the script')

# Create a table. 
def create_table():
    logging.info('Start creating the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(CREATE_TABLE_SQL)
                conn.commit()
                logging.info('Table creation successful')
            except Exception as e:
                conn.rollback()
                logging.error('Table creation failed, Reason: %s' % e)

# Insert data.
def insert_data():
    logging.info('Start inserting data')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                data = [('John', 20), ('Lucy', 25), ('Tom', 30)]
                flattened_data = [d for item in data for d in item]
                cursor.executemany(INSERT_DATA_SQL, [flattened_data])
                conn.commit()
                logging.info('Data insertion successful')
            except Exception as e:
                conn.rollback()
                logging.error('Data insertion failed, Reason: %s' % e)

# Query data.
def select_data():
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            cursor.execute(SELECT_DATA_SQL)
            result = cursor.fetchall()
            for row in result:
                print(row)

# Drop the table.
def drop_table():
    logging.info('Start dropping the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(DROP_TABLE_SQL)
                conn.commit()
                logging.info('Table dropped successfully')
            except Exception as e:
                conn.rollback()
                logging.error('Table drop failed, Reason: %s' % e)

if __name__ == '__main__':
    create_table()
    insert_data()
    select_data()
    drop_table()

logging.info('Script execution completed')

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.

Connect to OceanBase Database by using PyMySQL

Prerequisites

Procedure

Step 1: Check the versions of Python and pip

Step 2: Install the PyMySQL library

Note

Step 3: Obtain the connection information of OceanBase Database

Notice

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

Step 5: Run the main.py file

Project code introduction

Note

Code in config.py

Notice

Code in test_sql.py

Code in main.py

Complete code

References

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

Step 5: Run the `main.py` file