Python中无法通过列名获取MySQL数据的问题求助

下面提供几种实现user['username']式访问查询结果的方法：

方法1：使用字典游标（推荐） #

mysql.connector自带DictCursor，创建游标时指定后，查询返回的结果会是字典对象，直接通过列名键值访问。

import mysql.connector
from mysql.connector import DictCursor

mydb = mysql.connector.connect(host=config['MYSQL']['DB_HOST'],
                               user=config['MYSQL']['DB_USERNAME'], passwd=config['MYSQL']['DB_PASSWORD'], database=config['MYSQL']['DB_DATABASE'])
# 两种方式初始化字典游标二选一
mycursor = mydb.cursor(buffered=True, dictionary=True)
# 或者 mycursor = mydb.cursor(cursor_class=DictCursor)

mycursor.execute("select * from users")
users = mycursor.fetchall()

for user in users:
    username = user['username']  # 直接通过列名获取数据

方法2：手动将元组转换为字典 #

如果不想修改游标类型，可以先获取查询结果的列名，再将每个元组与列名配对生成字典。

mydb = mysql.connector.connect(host=config['MYSQL']['DB_HOST'],
                               user=config['MYSQL']['DB_USERNAME'], passwd=config['MYSQL']['DB_PASSWORD'], database=config['MYSQL']['DB_DATABASE'])
mycursor = mydb.cursor(buffered=True)

mycursor.execute("select * from users")
users = mycursor.fetchall()
# 提取查询结果的列名列表
column_names = [desc[0] for desc in mycursor.description]
# 批量转换为字典
users_dict = [dict(zip(column_names, user)) for user in users]

for user in users_dict:
    username = user['username']

方法3：使用namedtuple（支持属性式访问） #

如果偏好user.username这种属性访问方式，可以用collections.namedtuple实现：

from collections import namedtuple

mydb = mysql.connector.connect(host=config['MYSQL']['DB_HOST'],
                               user=config['MYSQL']['DB_USERNAME'], passwd=config['MYSQL']['DB_PASSWORD'], database=config['MYSQL']['DB_DATABASE'])
mycursor = mydb.cursor(buffered=True)

mycursor.execute("select * from users")
users = mycursor.fetchall()
column_names = [desc[0] for desc in mycursor.description]
# 创建自定义namedtuple类
User = namedtuple('User', column_names)
# 转换为namedtuple实例列表
users_named = [User(*user) for user in users]

for user in users_named:
    username = user.username  # 属性方式访问，也兼容索引访问

内容的提问来源于stack exchange，提问作者user19704437