How to Convert MySQL Query Results to String, List, and JSON in Python

When performing automated testing, validating only the API response is insufficient; the underlying data stored in the database must also be verified. MySQL queries in Python return results as tuple objects, which cannot be directly used for assertions. The following examples demonstrate how to convert these results into more convenient data types.

1. Raw MySQL query (tuple output)

import pymysql
from conf import datas

def mysql_self(type, sql):
    if type == 'mysql':
        conn = pymysql.connect(
            host=datas['sit']['数据库hosts'],
            port=int(datas['sit']['数据库端口']),
            user=datas['sit']['用户名'],
            passwd=datas['sit']['用户密码'],
            db=datas['sit']['数据库名称'],
            charset='utf8'
        )
        cur = conn.cursor()
        r = cur.execute(sql)
        info = cur.fetchmany(r)
        for i in info:
            logger.info(i)
        cur.close()
        conn.commit()
        conn.close()
        for row in info:
            print('row数据类型：', row)
            return row

mysql_self('mysql', "select username from userinfo ")

The function returns a tuple, which is not suitable for direct validation.

2. Convert tuple to string

import pymysql
from conf import datas
from mylogs import logger

def mysql_self(type, sql):
    if type == 'mysql':
        conn = pymysql.connect(
            host=datas['sit']['数据库hosts'],
            port=int(datas['sit']['数据库端口']),
            user=datas['sit']['用户名'],
            passwd=datas['sit']['用户密码'],
            db=datas['sit']['数据库名称'],
            charset='utf8'
        )
        cur = conn.cursor()
        r = cur.execute(sql)
        info = cur.fetchmany(r)
        for i in info:
            logger.info(i)
        cur.close()
        conn.commit()
        conn.close()
        for row in info:
            return row

testdata = mysql_self('mysql', "select username from userinfo ")
after_testdata = str(testdata)  # convert to string
print('after_testdata 数据类型:', type(after_testdata))

Using str() casts the tuple to a string, making it printable but still not ideal for element‑wise checks.

3. Convert tuple to list

#encoding:utf-8
import pymysql
from coin_web_autoApiTest.con.conf import datas
from coin_web_autoApiTest.con.mylogs import logger

def mysql_self(type, sql):
    if type == 'mysql':
        conn = pymysql.connect(
            host=datas['sit']['数据库hosts'],
            port=int(datas['sit']['数据库端口']),
            user=datas['sit']['用户名'],
            passwd=datas['sit']['用户密码'],
            db=datas['sit']['数据库名称'],
            charset='utf8'
        )
        cur = conn.cursor()
        r = cur.execute(sql)
        info = cur.fetchmany(r)
        for i in info:
            logger.info(i)
        cur.close()
        conn.commit()
        conn.close()
        listdata = []
        for row in info:
            listdata.append(row[0])
        return listdata

list_data = mysql_self('mysql', "select username from userinfo ")
print('list_data 数据类型:', type(list_data))
print('list_data 数据:', list_data)

The function extracts the first column of each row and appends it to a Python list, which can be directly used in assertions such as assert expected in list_data.

4. Convert result to JSON

# coding=utf-8
import json, MySQLdb

def TableToJson():
    try:
        conn = MySQLdb.Connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='coin_web_autoApiTest',
            port=3306,
            charset='utf8'
        )
        cur = conn.cursor()
        sql = "select username,password from coin_web_autoApiTest_userinfo order by id desc limit 1"
        cur.execute(sql)
        data = cur.fetchall()
        jsonData = []
        for row in data:
            result = {}
            result['username'] = str(row[0])
            result['password'] = str(row[1])
            jsonData.append(result)
        cur.close()
        conn.close()
    except:
        print('数据库连接失败')
        return ''
    else:
        jsondatar = json.dumps(jsonData, ensure_ascii=False)
        # remove surrounding brackets
        return jsondatar[1:len(jsondatar)-1]

if __name__ == '__main__':
    jsonData1 = TableToJson()
    print('转换为json格式的数据：', jsonData1)

The function fetches rows, builds a list of dictionaries, and serializes it to a JSON string. The returned string can be parsed by the test framework or compared directly with expected JSON payloads.

These conversion techniques enable comprehensive validation of both API responses and the corresponding database state during automated testing.