PostgreSQL联表查询结果导入Pandas DataFrame出现NaN如何解决

核心原因 #

SQL语句中SELECT后额外添加了一层括号包裹所有查询字段，PostgreSQL会将括号内的多个字段识别为单个行类型复合值，导致cursor.fetchall()返回的每条结果仅含1个元素（为包含所有字段值的嵌套元组）。你为DataFrame指定了13个列名，除第一列存储嵌套元组外，剩余12列会全部填充为NaN。

解决步骤 #

• 调整SQL语句，删除SELECT后包裹字段的外层括号
  将原SQL开头的

  SELECT(
      distance_data."Municipality",
      ...
  )
  

  修改为（同时删除最后一个字段后的闭合括号）：

  SELECT
      distance_data."Municipality",
      distance_data."Childcare/Nursery",
      distance_data."Leisure/Culture/Library",
      sales_details."Purchase_price",
      sales_details."Publication_date",
      sales_details."Date_of_signature",
      house_details."Type_of_house",
      house_details."Object_categorie",
      house_details."Construction_year",
      house_details."Energy_label_class",
      demo_data."Age_Group_Relation_(15-20)",
      demo_data."Age_Group_Relation_(20-25)",
      demo_data."Age_Group_Relation_(25-45)"
  

• （推荐优化）直接使用pandas内置方法读取SQL，避免手动处理游标出错
  无需手动创建游标、执行查询再转换DataFrame，使用pd.read_sql_query可一步完成操作，还能自动匹配字段无需手动指定列名，参考代码如下：

  import pandas as pd
  import psycopg2 as psy
  
  # 创建数据库连接
  conn = psy.connect(
      dbname = "funda_project", 
      host = "localhost", 
      user = "postgres", 
      password = "ledidhima2021."
  ) 
  
  # 修正后的查询语句
  createjointable2 = '''SELECT
      distance_data."Municipality",
      distance_data."Childcare/Nursery",
      distance_data."Leisure/Culture/Library",
      sales_details."Purchase_price",
      sales_details."Publication_date",
      sales_details."Date_of_signature",
      house_details."Type_of_house",
      house_details."Object_categorie",
      house_details."Construction_year",
      house_details."Energy_label_class",
      demo_data."Age_Group_Relation_(15-20)",
      demo_data."Age_Group_Relation_(20-25)",
      demo_data."Age_Group_Relation_(25-45)"
  
  FROM "distance_data"
  INNER JOIN "zip_data" 
      ON "distance_data"."Municipality" = "zip_data"."Municipality" 
  INNER JOIN "demo_data" 
      ON "zip_data"."Municipality" = "demo_data"."Municipality"
  INNER JOIN "sales_details"
      ON "zip_data"."globalId" = "sales_details"."GlobalID"
  INNER JOIN "house_details"
      ON "zip_data"."globalId" = "house_details"."GlobalID"
  ;'''
  
  # 直接读取查询结果到DataFrame
  results = pd.read_sql_query(createjointable2, conn)
  # 关闭连接
  conn.close()
  

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