Connect to OceanBase Database by using PyMySQL

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 table 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 ensure that Python and pip are correctly 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

PyMySQL is a pure Python MySQL client library that is easy to install, does not require compilation, and offers good cross-platform compatibility. It provides interfaces for interacting with MySQL databases and supports Python 3.x.

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

1. Run the following command to navigate to the python-pymysql code directory.

   Here is an example:

   cd python-pymysql
   

2. Run the following command to install the required Python library for the project.

   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

Parameter description:

• $host: 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: 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: the name of the database to be accessed.

  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: the tenant connection account. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

• $password: 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 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 code shows an example of 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, run the main.py file, query data, and output the result.

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 package 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

File description:

• config.py: stores database connection information.

• test_sql.py: stores SQL statements.

• main.py: the main program entry, used to perform basic database operations, including table creation, data insertion, data query, and table deletion.

• requirements.txt: stores the Python packages required for the project and their versions.

  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.

Introduction to config.py

The code in the config.py file obtained in this topic defines the database connection information. The database connection information includes the following components:

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

Code as follows:

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

Parameter description:

• $host: specifies the IP address for connecting to OceanBase Database. It is an ODP IP address for connection through ODP, or an IP address of an OBServer node for direct connection.

• $port: specifies the port for connecting to OceanBase Database. The default value is 2881 when ODP is not used, and 2883 when ODP is used.

• $user_name: the username for connecting to the database.

• $password: the password for connecting to the database.

• $database_name: the name of the database to connect to.

• charset: the character set used to connect to the database.

  Notice

  The parameter values in this example are set based on specific environments and database configurations. You must modify the parameter values based on your actual situation.

Introduction to test_sql.py

The code in the test_sql.py file obtained in this topic defines SQL statements for database operations, including table creation, data insertion, data query, and table deletion. These SQL statements can be executed after establishing a connection to the database using PyMySQL, enabling the corresponding features.

The code in the file mainly consists of the following sections:

1. SQL statement for creating a table.

   The SQL statement for creating the test_pymysql table is defined, which contains three fields: id, name, and age. The id field is an auto-incrementing primary key.

   Code 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. The SQL statement for inserting data.

   Define an SQL statement for inserting data into the test_pymysql table. The statement will insert three records, each containing two fields: name and age. The values of each field will be passed in during statement execution using the placeholder %s.

   Code as follows:

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

3. SQL query statement for querying data.

   Define an SQL query statement to query all data from the test_pymysql table.

   Code as follows:

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

4. SQL statement for dropping a table.

   Define an SQL statement to delete the test_pymysql table.

   Code as follows:

   DROP_TABLE_SQL = '''
   DROP TABLE test_pymysql
   '''
   

Introduction to main.py

The code in the main.py file obtained in this topic calls the pymysql module to connect to the MySQL database and calls the logging module to output log information. It implements operations such as creating tables, inserting data, querying data, and dropping tables.

The code in the main.py file mainly includes the following sections:

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 the SQL statements for database operations.

   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
   

2. Set the log level and format, and output an INFO level log message indicating that the script execution has begun.

   Sample code:

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

3. Define a function for creating tables.

   Define a function named create_table(). Output an INFO level log message indicating that a table is being created. Use the with statement to manage the lifecycle of database connections and cursor objects to ensure secure closure of database connections and cursor objects and prevent memory leaks. Execute the SQL statement for creating a table, commit the transaction, and output log information, or roll back the transaction and output error log information.

   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)
   

4. Define a function for inserting data.

   Define a function named insert_data(). Output an INFO level log message indicating that data is being inserted. Use the with statement to manage the lifecycle of database connections and cursor objects to ensure secure closure of database connections and cursor objects and prevent memory leaks. Execute the SQL statement for inserting data, commit the transaction, and output log information, or roll back the transaction and output error log information.

   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)
   

5. Define a function for querying data.

   Define a function named select_data() to query data from the database. Use the with statement to manage the lifecycle of database connections and cursor objects to ensure secure closure of database connections and cursor objects and prevent memory leaks. Use the execute() method to execute the SQL statement defined in the SELECT_DATA_SQL variable to query data. Use the fetchall() method to obtain the query results and use a for loop to output the results row by row.

   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)
   

6. Define a function for dropping tables.

   Define a function named drop_table(). This function uses the predefined database connection information (DB_CONFIG) and the SQL statement for dropping tables (DROP_TABLE_SQL). The function executes the table dropping operation and prints corresponding log information indicating success or failure of the operation. If the table dropping operation fails, an error message is printed.

   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)
   

7. Define the entry point of the program, which is mainly used to call the function for performing database operations.

   First, determine whether the current module is being run as the main program. If so, perform the following operations:

   1. Call the create_table() function to create a database table.
   2. Call the insert_data() function to insert data into the table.
   3. Call the select_data() function to query data from the table.
   4. Call the drop_table() function to drop the database table.

   Sample code:

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

8. Output an INFO level log message indicating that the script execution has completed.

   Sample code:

   logging.info('Script execution completed')
   

Complete code

# 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:
            try:
                cursor.execute(SELECT_DATA_SQL)
                result = cursor.fetchall()
                for row in result:
                    print(row)
            except Exception as e:
                logging.error('Data query failed, Reason：%s' % e)

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

Error handling

When you use PyMySQL to connect to OceanBase Database, various errors may occur. The following table describes some common errors and their solutions.

1. Connection errors: If you cannot connect to the database, check whether the connection parameters, including the host name, port, username, password, and database name, are correct.

2. Privilege errors: If you encounter a privilege error, make sure that the user has sufficient privileges to perform the required operation.

3. SQL syntax errors: If there is a syntax error in your SQL statement, check whether the syntax is correct.

4. Data type errors: If the data type of the inserted data does not match the table definition, make sure that the data type is correct.

In your code, you can use the try-except statement to capture and handle these errors, ensuring that the program can handle errors gracefully instead of crashing. You can also use the logging module to record error information for debugging and troubleshooting.

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.