Batch Updating MySQL Records with pymysql for Interface Automation

When writing interface automation, especially with a large number of simulated users, login information can become invalid; the recommended approach is to define which test case uses which user credentials, initialize these credentials before running the tests, and then perform batch updates.

Required dependency:

pip3 install pymysql

Sample code:

import pymysql
# Connect to MySQL
def mysql_connect():
    return pymysql.connect(
        host="127.0.0.1",
        port=3307,
        user='root',
        passwd='root',
        db='locust_huaan',
        charset='utf8'
    )

conn = mysql_connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)

def mysqlUpdate(sql):
    '''
    Update data
    :param sql: SQL statement
    :return:
    '''
    try:
        conn.ping(reconnect=True)  # Ensure the connection is valid before each batch update
        cursor.execute(sql)
        conn.commit()
        return '更新成功'
    except Exception as e:
        conn.rollback()
        return '更新失败'

if __name__ == '__main__':
    # Batch data update example
    a = ["111", "222"]
    q = [11, 22]
    s = []
    for i, j in zip(range(len(a)), range(len(q))):
        s.append([a[i], q[j]])
    for i in range(len(s)):
        mysqlUpdate('update user set usercookies="%s" where userid=%s' % (s[i][0], s[i][1]))

The conn.ping(reconnect=True) line is crucial for batch updates because it verifies that the MySQL connection remains alive before each execution.

This script demonstrates both single‑record and batch‑record updates, showing how to construct parallel lists of user identifiers and cookie values, combine them, and iterate through the combined list to execute the update statements.

Follow the steps, adapt the database credentials and table/column names as needed, and integrate the script into your automated test framework to keep login data fresh during large‑scale testing.