Connect to OceanBase Database by using PyMySQL

This topic describes 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, 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

1. Check the versions of Python and pip.
2. Install the PyMySQL 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.

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

Open the command prompt or PowerShell terminal and run the following commands to install the PyMySQL library.

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

   Here is an example:

   cd python-pymysql
   

2. Run the following command to install the PyMySQL library:

   Here is an example:

   pip install -r requirements.txt
   

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, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL 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-pymysql/config.py file based on the information obtained in Step 3: Obtain the connection information of OceanBase Database.

1. Go to the python-pymysql 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:

   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 the command prompt or PowerShell terminal, and run the main.py file to query data and output the results.

1. Go to the python-pymysql project directory.

   Here is an example:

   cd D:\demo\demo\python-pymysql
   

2. 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 python-pymysql to download the project code, which is a compressed file named python-pymysql.zip.

After decompressing it, you will find 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: the entry to the program 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 code provided in this topic only lists the version requirement for the PyMySQL library. 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.

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

The sample code is as follows:

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, 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_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.

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

1. Define an SQL statement for creating a table.

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

   The sample code is as follows:

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

2. Define an SQL statement for inserting data.

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

   The sample code is as follows:

   INSERT_DATA_SQL = '''
   INSERT INTO test_pymysql (name, age) VALUES
   (%s, %s),
   (%s, %s),
   (%s, %s)
   '''
   

3. Define an SQL statement for querying data.

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

   The sample code is as follows:

   SELECT_DATA_SQL = '''
   SELECT * FROM test_pymysql
   '''
   

4. Define an SQL statement for dropping a table.

   Define the SQL statement that drops the test_pymysql table.

   The sample code is as follows:

   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.

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

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

   The sample code is as follows:

   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
   

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

   The sample code is as follows:

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

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

   The sample code is as follows:

   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)
   

4. Define a function for inserting data.

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

   The sample code is as follows:

   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)
   

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

   The sample code is as follows:

   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)
   

6. Define a function for dropping a table.

   Define the drop_table() function 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.

   The sample code is as follows:

   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)
   

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

   First, check whether the current module is running 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.

   The sample code is as follows:

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

8. Generate an INFO log to indicate that script execution is complete.

   The sample code is as follows:

   logging.info('Script execution completed')
   

Complete code examples

# Database Connection
DB_CONFIG = {
    'host': '$host',
    'port': $port,
    'user': '$user_name',
    'password': '$password',
    'database': '$database_name',
    'charset': 'utf8mb4'
}

# Create 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
'''

# Delete 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 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)

# Delete 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 Overview of connection methods.

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