Using Python to Operate MySQL Database

The Principle of Using Python to Operate Databases

Python operates the database by calling the corresponding DB API

Automation Testing Use Cases

When initializing data
Performing test assertions
When unable to call the interface for data clearing

Installing the PyMySQL Library

pip install pymysql -i http://pypi.douban.com/simple --trusted-host pypi.douban.com

Python Operates MySQL

Database Operation Code

import pymysql  # Import the database operation library
# Open database connection
db = pymysql.connect(host='Database IP', port=Database Port, user='Username', passwd='Password', database='Database', charset='utf8')
# Create a cursor
cursor = db.cursor()

# Execute SQL statement
cursor.execute('select version();')
result = cursor.fetchone()
print(result)
# Query operation
sql = 'select * from Table;'
cursor.execute(sql)
# The result returns the first result cursor.fetchone() returns a tuple
result = cursor.fetchone()
print(result)
# Get all results cursor.fetchall() returns a tuple of tuples
all_result = cursor.fetchall()
print(all_result)

# Insert operation
sql = 'insert into TableName(FieldName) values(ValueCorrespondingToField);'
cursor.execute(sql)
# Except for query operations, other operations need to commit to synchronize to the database
db.commit()

# Update operation
sql = 'update TableName set FieldName="FieldValue" where FilterCondition;'
cursor.execute(sql)
db.commit()

# Delete operation
sql = 'delete from TableName where FilterCondition;'
cursor.execute(sql)
# Rollback to restore to the previous state
db.rollback()
db.commit()

# Close and release cursor and connection
cursor.close()
db.close()

Using Python to Operate MySQL Database Exclusive for Followers