This topic describes how to connect to and use OceanBase Cloud by using the MySQL Connector/Python driver.
Prerequisites
- You have installed Python 2.7 or Python 3.x and pip.
- You have registered an OceanBase Cloud account and created a cluster instance and a MySQL-compatible tenant. For more information, see Create a cluster instance and Create a tenant.
Procedure
- Obtain the connection string for OceanBase Cloud.
- Install the MySQL Connector/Python driver.
- Create a
test.pyfile and enter the database connection information. - Run the
test.pyfile.
Step 1: Obtain the connection string for OceanBase Cloud
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information. Under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get connection string.
Fill in the corresponding information in the URL below based on your created OceanBase Cloud account.
obclient -h$host -P$port -u$user_name -p$password -D$database_nameParameter description:
$host: The connection address for OceanBase Cloud, for example,t********.********.oceanbase.cloud.$port: The connection port for OceanBase Cloud, default is 3306.$database_name: The name of the database to access.Note
The user connecting to the tenant must have the
CREATE,INSERT, andSELECTprivileges on this database. For more information about account privileges, see Create and manage accounts.$user_name: The account used to access the database.$password: The password for the account.
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 is used to connect to and operate MySQL databases in 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 installation, you can verify its success with the following command:
pip3 list | grep mysql-connector-python
Step 3: Write the test.py file and fill in the database connection information
Write the test.py file based on the information obtained in Step 1: Obtain the OceanBase Cloud database connection string, and fill in the database connection information.
Create a file named
test.py.Fill in the following content in the
test.pyfile and modify the database connection information as needed.The following is an example of the
test.pyfile content: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 Connection cursor.close() conn.close()
Step 4: Run the test.py file
Open the command prompt or PowerShell terminal and run the test.py file to query data and output the results.
Navigate to the directory where the
test.pyfile is located.Example:
cd D:\demo\demoRun the
test.pyfile.Example:
python test.pyExample output:
(1, 'John', 20) (2, 'Lucy', 26) (3, 'Tom', 30)
Error handling
When using MySQL-connector-python to connect to OceanBase Cloud, you may encounter various errors. The following are some common errors and their handling methods:
Connection error: If you cannot connect to the database, check whether the connection parameters, including the hostname, port, username, password, and database name, are correct.
Permission error: If you encounter a permission-related error, ensure the user has sufficient permissions to perform the required operation.
SQL syntax error: If an SQL statement contains a syntax error, check whether the SQL statement syntax is correct.
Data type error: If the data type being inserted does not match the table definition, ensure the inserted data type is correct.
In your code, you can use try-except statements to catch and handle these errors, ensuring the program handles them gracefully instead of crashing directly. Additionally, you can use the logging module to record error information, which facilitates debugging and troubleshooting.
References
- For more information about connecting to OceanBase Cloud, see Overview of connection methods.