Use the MySQL-connector-python driver to connect to and use OceanBase Cloud

This topic describes how to connect to and use OceanBase Cloud by using the MySQL-connector-python driver.

Prerequisites

• You have installed Python2.7/Python 3.x and pip.
• You have registered an account for OceanBase Cloud and created an instance and a MySQL-compatible tenant. For more information, see Create an instance and Create a tenant.

Procedure

1. Obtain the connection string of OceanBase Cloud.
2. Install the MySQL-connector-python driver.
3. Write a test.py file and fill in the database connection information.
4. Run the test.py file.

Step 1: Obtain the connection string of OceanBase Cloud

1. Log in to the OceanBase Cloud console. In the instance list page, expand the information of the target instance, and in the target tenant, choose Connect > Get Connection String.

   For more information, see Obtain the connection string.

2. Fill in the corresponding information in the URL based on the created OceanBase Cloud database.

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

   Parameter description:

   • $host: the connection address of OceanBase Cloud, for example, t********.********.oceanbase.cloud.

   • $port: the connection port of OceanBase Cloud, which is 3306 by default.

   • $database_name: the name of the database to be accessed.

     Notice

     The user for connecting to the tenant must have the CREATE, INSERT, and SELECT permissions on the database. For more information about account permissions, see Create and manage an account.

   • $user_name: the account for accessing the database.

   • $password: the password of the account.

   Here is an example:

   obclient -h t********.********.oceanbase.cloud -P3306 -u mysql001 -p****** -Dtest
   

Step 2: Install the MySQL-connector-python driver

MySQL Connector/Python is an official driver provided by MySQL for connecting to MySQL databases. It is written in Python and allows you to connect to and operate MySQL databases from Python. MySQL Connector/Python supports Python 2.7 and Python 3.x.

Open the command prompt or PowerShell terminal and run the following command to install the MySQL-connector-python driver.

pip3 install mysql-connector-python

After the installation is complete, run the following command to verify whether the installation was successful:

pip3 list | grep mysql-connector-python

Step 3: Write a test.py file and fill in the database connection information

Write a test.py file based on the information obtained in Step 1: Obtain the connection string of OceanBase Cloud and fill in the database connection information.

1. Create a file named test.py.

2. Fill in the following content in the test.py file and modify the database connection information as needed.

   Here is an example of the content of the test.py file:

   import mysql.connector
   
   # Database connection information
   config = {
       'host': 't********.********.oceanbase.cloud',
       'port': 3306,
       'user': 'mysql001',
       'password': '******',
       'database': 'test'
   }
   
   # Create a database connection
   conn = mysql.connector.connect(**config)
   cursor = conn.cursor()
   
   # Create a database
   cursor.execute("CREATE DATABASE IF NOT EXISTS test")
   
   # Use the database
   cursor.execute("USE test")
   
   # Create a table
   cursor.execute("""
   CREATE TABLE IF NOT EXISTS test_table (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255) NOT NULL,
       age INT
   )
   """)
   
   # Insert data
   cursor.execute("""
   INSERT INTO test_table (name, age) VALUES
   ('John', 20),
   ('Lucy', 25),
   ('Tom', 30)
   """)
   
   # Update data
   cursor.execute("""
   UPDATE test_table SET age = 26 WHERE name = 'Lucy'
   """)
   
   # Query data
   cursor.execute("SELECT * FROM test_table")
   rows = cursor.fetchall()
   for row in rows:
       print(row)
   
   # Close the connection
   cursor.close()
   conn.close()
   

Step 4: Run the test.py file

Open the command prompt or PowerShell terminal, run the test.py file, query data, and output the results.

1. Go to the directory where the test.py file is located.

   Here is an example:

   cd D:\demo\demo
   

2. Run the test.py file.

   Here is an example:

   python test.py
   

   The returned result is as follows:

   (1, 'John', 20)
   (2, 'Lucy', 26)
   (3, 'Tom', 30)
   

Error handling

When you use the MySQL-connector-python driver to connect to OceanBase Cloud, you may encounter various errors. The following are some common errors and their solutions:

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

2. Permission error: If you encounter a permission-related error, ensure that the user has sufficient permissions to perform the required operations.

3. SQL syntax error: If the SQL statement has a syntax error, check whether the SQL statement is correct.

4. Data type error: If the data types of the inserted data do not match the table definition, ensure that the data types are correct.

In the code, you can use the try-except statement to capture and handle these errors. This ensures that the program can handle errors gracefully without crashing. Additionally, you can use the logging module to record error information, which facilitates debugging and troubleshooting.

References

• For more information about how to connect to OceanBase Cloud, see Overview of connection methods.