This topic describes how to build an application using the PyMySQL library and OceanBase AI Database to perform basic database operations, including creating tables, inserting data, querying data, and dropping tables.
Download the python-pymysql sample project Prerequisites
- You have installed Python 3.x and pip.
- You have installed OceanBase AI Database and created a user tenant.
Procedure
- Check the versions of
Pythonandpip. - Install the PyMySQL library.
- Obtain the connection information for OceanBase AI Database.
- 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, does not require 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 AI Database
Contact the deployment personnel or administrator of OceanBase AI Database to obtain the corresponding 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 AI Database. For connections through OceanBase Database Proxy (ODP), use an ODP address; for direct connections, use the IP address of an OBServer node.$port: The port for connecting to OceanBase AI Database. For ODP connections, the default port is2883, which can be customized during ODP deployment; for direct connections, the default port is2881, which can be customized during OceanBase AI Database deployment.$database_name: The name of the database to access.Notice
The user connecting to the tenant must have the
CREATE,INSERT,DROP, andSELECTprivileges on this database. For more information about user privileges, see Privilege types.$user_name: The connection account for the tenant. Common formats for ODP connections areusername@tenant_name#cluster_nameorcluster_name:tenant_name:username. For direct connection, the format isusername@tenant_name.$password: The account password.
For more information about the connection string, see Connect to OceanBase AI Database by using OBClient.
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 project file python-pymysql/config.py based on the information obtained in Step 3: Obtain OceanBase AI 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': '10.10.10.1', 'port': 2881, 'user': 'test_user001@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.pyThe 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
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
The files and directories are described as follows:
config.py: Stores database connection information.test_sql.py: Stores SQL statements.main.py: The main program entry point for performing basic database operations, including creating tables, inserting data, querying data, and dropping tables.requirements.txt: Stores the Python packages required by 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
sudo pip install -r requirements.txtcommand, which will automatically install the required library.
Code in 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.
The sample code is as follows:
DB_CONFIG = {
'host': '$host',
'port': $port,
'user': '$user_name',
'password': '$password',
'database': '$database_name',
'charset': 'utf8mb4'
}
Parameter description:
$host: The IP address for connecting to OceanBase AI Database. For connections through ODP, use an ODP address; for direct connections, use the IP address of an OBServer node.$port: The port for connecting to OceanBase AI Database. The default is2881. When using ODP, the default port is2883.$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 when connecting to the database.Notice
The parameter values here are determined by the specific environment and database settings and need to be modified according to the actual situation.
Code in test_sql.py
The code in the obtained test_sql.py file defines 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 with 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.The sample code is 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) ) '''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 execution of the SQL statement using the placeholder%s.The sample code is as follows:
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.The sample code is as follows:
SELECT_DATA_SQL = ''' SELECT * FROM test_pymysql '''The SQL statement for dropping the table.
Define the SQL statement for dropping the
test_pymysqltable.The sample code is as follows:
DROP_TABLE_SQL = ''' DROP TABLE test_pymysql '''
Code in 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.
The sample code is as follows:
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.
The sample code is as follows:
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()that outputs an INFO-level log message indicating the start of table creation. Use thewithstatement to manage the lifecycle of database connections and cursor objects, ensuring safe closure and avoiding issues like memory leaks. Execute the table creation SQL statement, commit the transaction and output log information, or roll back the transaction and output error logs.The sample code is as follows:
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 closed securely 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.The sample code is as follows:
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 closed securely and avoiding issues like memory leaks. Use theexecute()method to execute the SQL statement defined bySELECT_DATA_SQLto query data. Use thefetchall()method to obtain the query results, and output the results row by row using aforloop.The sample code is as follows:
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 operation fails, it prints an error message.The sample code is as follows:
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 used to perform 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.
The sample code is as follows:
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.
The sample code is as follows:
logging.info('Script execution completed')
Complete code examples
# 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 AI Database, 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 correctly written.
Data type error: If the data type of the inserted value does not match the table definition, ensure that the data type is correct.
In your code, use try-except statements to catch and handle these errors, ensuring that the program handles errors gracefully instead of crashing directly. Also, use the logging module to record error information for easier debugging and troubleshooting.
References
For more information about how to connect to OceanBase AI Database, see Overview of connection methods.
For more information about how to create a database, see CREATE DATABASE.