mysql 查询结果转置

CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    score INT
);

INSERT INTO students (id, name, score) VALUES
(1, 'Alice', 85),
(2, 'Bob', 90),
(3, 'Charlie', 78);

SELECT 'name' AS attribute, value FROM (
    SELECT name FROM students
    UNION ALL
    SELECT score FROM students
) AS data ORDER BY attribute;

attribute | value
----------|------
name      | Alice
name      | Bob
name      | Charlie
score     | 85
score     | 90
score     | 78

import pandas as pd
import mysql.connector

# 连接MySQL数据库
conn = mysql.connector.connect(user='your_user', password='your_password', host='your_host', database='your_database')
cursor = conn.cursor()

# 查询数据
cursor.execute("SELECT name, score FROM students")
data = cursor.fetchall()

# 转置数据
df = pd.DataFrame(data, columns=['name', 'score'])
transposed_df = df.T.reset_index()
transposed_df.columns = ['attribute', 'value']

# 打印结果
print(transposed_df)

# 关闭连接
cursor.close()
conn.close()