在 Python MySQL 中输出时未定义值? [英] Values not being defined when outputted in Python MySQL?

问题描述

借助先前提出的问题，请参阅 (如何正确地将 SQL 行转换为列?)，我正在尝试应用到用 Python 编写的 MySQL 连接器脚本.正如@Popeye 的小提琴 http://sqlfiddle.com/#!9/997790/25，我现在尝试使用 Person C 的空值返回所有行，但其输出如下

Aided by a previously asked question see (How to correctly convert SQL rows to columns?), I'm attempting to apply to a MySQL connector script written in python. As seen by @Popeye 's fiddle http://sqlfiddle.com/#!9/997790/25, my attempt at returning all rows now with a null value for Person C but its output is seen below

import mysql.connector

db = mysql.connector.connect(
     host="localhost",
     port="3307",
     user="root",
     passwd="#",
     database="#"
  )
mycursor = db.cursor()
mycursor.execute("CREATE TABLE trial157 (Name TEXT(13) NULL , A1 TEXT(13) NULL ,  A2 TEXT(13) NULL ,  A3 TEXT(13) NULL ,  A4 TEXT(13) NULL)")


sql = "INSERT INTO trial157(Name, A1, A2, A3, A4) VALUES (%s, %s, %s, %s, %s)"
val = [
  ('Person A', 'C', 'T', 'R', 'S'),
  ('Person B', 'M', 'V', 'R', 'S'),
  ('Person C', 'M', None , 'R', 'C')
]
mycursor.executemany(sql, val)
db.commit()


mycursor.execute("SELECT * From (select name_new, max(case when name = 'PersonA' then A end) as PersonA, max(case when name = 'PersonB' then A end) as PersonB, max(case when name = 'PersonC' then A end) as PersonC from (select name, 'A1' name_new, A1 A from trial157 union all select name, 'A2' name_new, A2 A from trial157 union all select name, 'A3' name_new, A3 A from trial157 union all select name, 'A4' name_new, A4 A from trial157 ) t group by name_new) t where PersonC is null")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

 - OUTPUT
('A1', None, None, None)                                                                                     
('A2', None, None, None)
('A3', None, None, None)
('A4', None, None, None)
[Finished in 1.2s]

推荐答案

您使用了错误的名称.(在 case 表达式名称中使用了不带 space 的名称.您应该使用它与 val) 完全相同)

You have used wrong names.(in case expression names are used without space. You should use it exactly same as per val)

替换

max(case when name = 'PersonA' then A end) as PersonA

与

max(case when name = 'Person A' then A end) as PersonA

查看name中Person和A之间的space.对 Person B 和 Person C

See the space between Person and A in name. Do the same for Person B and Person C

这篇关于在 Python MySQL 中输出时未定义值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！