Connect to OceanBase Cloud by using mysqlclient

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

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

python-mysqlclient
├── config.py
├── db.py
├── main.py
└── requirements.txt

The files and directories are described as follows:

• config.py: manages database connection configurations.

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

• main.py: serves as the entry to the application and provides a simple CLI. You can enter commands on the CLI to perform corresponding operations.

• 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 mysqlclient library and its version requirements. You can run the pip install -r requirements.txt command to automatically install the required library.

Code in config.py

When you connect to a database by using Python, you must specify database connection parameters. You can store the parameters in a separate configuration file such as config.py. You can encapsulate the parameters in a dictionary so that other Python files can reference the dictionary to connect to the 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 Cloud.

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 access address of OceanBase Cloud. The value is sourced from the -h parameter in the connection string.
• port: the access port of OceanBase Cloud. The value is sourced from the -P parameter in the connection string.
• user: the account name. The value is sourced from the -u parameter in the connection string.
• password: the account password. The value is sourced from the -p parameter in the connection string.
• database: the name of the database to be accessed. The value is sourced from the -D parameter in the connection string.
• charset: 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.

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

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.

   Define the create_table function to create a table named test_tbl1 in OceanBase Cloud. 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)
   

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

4. 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 application to implement basic database operations such as creating tables, inserting records, and querying all records.

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

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 the database.

   Define the main function to implement a simple CLI application for database operations. The application 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 application calls the corresponding function to insert or query data based on your command until you enter the 3 command to exit the application. If you enter an invalid command, the application prompts you to re-enter a command.

   Command 1 is to insert data, 2 is to query data, and 3 is to exit the application.

   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 run. 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 extensibility of the application.

Complete code

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