This topic describes how to build an application using the PyMySQL library and OceanBase Cloud to perform basic database operations, including creating tables, inserting data, querying data, and dropping tables.
Prerequisites
- You have installed 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
- Check the versions of
Pythonandpip. - Install the PyMySQL library.
- Obtain the connection information for OceanBase Cloud.
- Modify the database connection information in the
config.pyfile. - Run the
main.pyfile.
Step 1: Check the versions of Python and pip
Open a command prompt or PowerShell terminal, and run the python --version and pip --version commands to ensure that Python and pip are properly installed.
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 implementation of the MySQL client library. It is easy to install, requires no compilation, and has good cross-platform compatibility. It provides an interface for interacting with MySQL databases and supports Python 3.x.
Open a command prompt or PowerShell terminal, and run the following commands to install the PyMySQL library.
Run the following command to navigate to the
python-pymysqldirectory of the code.Example:
cd python-pymysqlRun the following command to install the Python libraries required by the project.
Example:
pip install -r requirements.txt
Note
You can also directly open a command prompt or PowerShell terminal and run the pip install pymysql command to install the PyMySQL library. PyMySQL is a pure Python implementation library, easy to install and requiring no compilation, making it suitable for development and test environments.
Step 3: Obtain the connection information for OceanBase Cloud
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and 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 database.
obclient -h$host -P$port -u$user_name -p$password -D$database_nameParameter description:
$host: The connection address of the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.$port: The connection port of the OceanBase Cloud database. The default value is 3306.$database_name: The name of the database to be accessed.Notice
The user connecting to the tenant must have the
CREATE,INSERT,DROP, 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 of the account.
Example:
obclient -h t********.********.oceanbase.cloud -P3306 -u 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 project file based on the information obtained in Step 3: Obtain the OceanBase Cloud database connection information.
Navigate to the
python-pymysqlproject folder.Modify the database connection information in the
config.pyfile.- In a Windows environment, use a text editor to open the
config.pyfile and modify the database connection information to match your actual configuration. - In a Linux environment, you can use the
vi config.pyorvim config.pycommand to edit theconfig.pyfile and modify the database connection information to match your actual configuration.
The following example shows the database connection information in the
config.pyfile:DB_CONFIG = { 'host': 't5******.********.oceanbase.cloud', 'port': 3306, 'user': 'mysql001', 'password': '******', 'database': 'test', 'charset': 'utf8mb4' }- In a Windows environment, use a text editor to open the
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.
Navigate to the
python-pymysqlproject directory.Example:
cd D:\demo\demo\python-pymysqlRun the
main.pyfile.Example:
python main.pyExample output:
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
Sample project overview
Click python-pymysql to download the project code, which is a compressed file named python-pymysql.zip.
After decompressing it, you will get 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 creating tables, inserting data, querying data, and dropping tables.requirements.txt: Stores the Python packages required for the project and their version information.Note
The code obtained in this topic only lists the version requirement for the PyMySQL library. You can install it by running the command
sudo pip install -r requirements.txt. After executing this command, the required library will be automatically installed.
Introduction to config.py
The code in the obtained config.py file defines the database connection information. The database connection information mainly includes the following parts:
Specify the IP address, port number, username, password, database name, and character set for connecting to the database.
Code:
DB_CONFIG = {
'host': '$host',
'port': $port,
'user': '$user_name',
'password': '$password',
'database': '$database_name',
'charset': 'utf8mb4'
}
Parameter description:
$host: The connection address of OceanBase Cloud.$port: The connection port of OceanBase Cloud.$user_name: The account for accessing the database.$password: The account password.$database_name: The name of the database to connect to.charset: The character set used when connecting to the database.
Note
The parameter values here are determined by the specific environment and database settings and need to be modified according to the actual situation.
Introduction to test_sql.py
The code in the obtained test_sql.py file defines the SQL statements for database operations, including creating a table, inserting data, querying data, and dropping a table. These SQL statements can be executed after connecting to the database using PyMySQL to implement the corresponding functionality.
The code in this file mainly includes the following parts:
SQL statement for creating a table.
Define the SQL statement for creating the
test_pymysqltable. The table defines three fields:id,name, andage, whereidis an auto-increment primary key.Code:
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) ) '''SQL statement for inserting data.
Define an SQL statement for inserting data into the
test_pymysqltable. The inserted data will contain three rows, each row having two fields:nameandage. The value for each field is passed in during the execution of the SQL statement using the placeholder%s.Code:
INSERT_DATA_SQL = ''' INSERT INTO test_pymysql (name, age) VALUES (%s, %s), (%s, %s), (%s, %s) '''SQL statement for querying data.
Define an SQL statement for querying data, retrieving all data from the
test_pymysqltable.Sample code:
SELECT_DATA_SQL = ''' SELECT * FROM test_pymysql '''The SQL statement for dropping the table.
Define the SQL statement to drop the
test_pymysqltable.Sample code:
DROP_TABLE_SQL = ''' DROP TABLE test_pymysql '''
Introduction to main.py
The code in the obtained main.py file uses the pymysql module to connect to a MySQL database and the logging module to output log information, implementing operations such as creating a table, inserting data, querying data, and dropping a table.
The code in the main.py file mainly includes the following parts:
Import the required modules.
- Import the
loggingmodule. - Import the
pymysqlmodule. - Import the
config.pymodule, which defines the database connection information. - Import the
test_sql.pymodule, 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- Import the
Set the logging level and format, and output an INFO-level log message indicating the start of script execution.
Sample code:
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s') logging.info('Start executing the script')Define a function for creating a table.
Define a function named
create_table(). Output an INFO-level log message indicating the start of table creation. Use awithstatement to manage the lifecycle of database connections and cursor objects, ensuring they are safely closed and avoiding issues like memory leaks. Execute the SQL statement for creating the 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)Define a function to insert data.
Define a function named
insert_data()that outputs an INFO-level log message indicating the start of data insertion. Use thewithstatement to manage the lifecycle of database connections and cursor objects, ensuring they are safely closed and avoiding issues like 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.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)Define a function to query data.
Define a function named
select_data()to query data from the database. Use thewithstatement to manage the lifecycle of database connections and cursor objects, ensuring they are safely closed and avoiding issues like memory leaks. Use theexecute()method to execute the SQL statement defined bySELECT_DATA_SQLto query data. Use thefetchall()method to retrieve the query results and output them row by row using aforloop.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)Define a function to drop a table.
Define a function named
drop_table()that uses predefined database connection information (DB_CONFIG) and the SQL statement for dropping a table (DROP_TABLE_SQL). The function performs the table drop operation and prints corresponding log information indicating whether the operation succeeded or failed. If the table drop fails, an error message is printed.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)Define the program's entry point, which is the function that performs the database operations.
First, check if the current module is running as the main program. If so, perform the following operations:
- Call the
create_table()function to create a database table. - Call the
insert_data()function to insert data into the table. - Call the
select_data()function to query data from the table. - Call the
drop_table()function to delete the database table.
Code:
if __name__ == '__main__': create_table() insert_data() select_data() drop_table()- Call the
Output an INFO-level log message indicating that the script has completed execution.
Code:
logging.info('Script execution completed')
Complete sample 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 using PyMySQL 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 that 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 is syntactically correct.
Data type error: If the data type of the inserted value does not match the table definition, ensure that the inserted data type is correct.
In your code, use try-except statements to catch and handle these errors, ensuring that the program can handle errors gracefully instead of crashing directly. Also, 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.