python - 如何使用python返回mysql查询结果

Question

查询结果和预期输出如下。

+------+----------+
| YEAR | MAX_TEMP |
+------+----------+
| 1990 |      603 |
| 1991 |      605 |
+------+----------+

这是我的代码。在输出中没有记录(即 2)。我怎样才能让它显示完整的表格？

import MySQLdb

    conn = MySQLdb.connect("localhost","root","root","vkp")
    cursor = conn.cursor()
    print ("Opened database successfully");

    def select():
        #database_conn()
        print ("inside select")
        a = cursor.execute("SELECT year(dt) AS YEAR, max(air_temp) AS MAX_TEMP from TEMP_DATA WHERE air_temp != 9999 AND (quality_at = '0' || quality_at = '1' || quality_at = '4' || quality_at = '5' || quality_at = '9') GROUP BY year(dt); SELECT year(dt) AS YEAR, max(air_temp) AS MAX_TEMP from MAX_TEMP9293 WHERE air_temp != 9999 AND (quality_at = '0' || quality_at = '1' || quality_at = '4' || quality_at = '5' || quality_at = '9') GROUP BY year(dt)")

        return a
        conn.commit()
        conn.close
    a = select()
    print (a)

Answer 1

1. 您的代码中的缩进已关闭；如所写，您的代码将无法编译(您可能只需要更正问题提交中的格式)。

2. @aws_apprentice 是正确的。您应该在代码中包含 fetchall。我会这样写:

   import MySQLdb
   
   conn = MySQLdb.connect("localhost","root","root","vkp")
   curs = conn.cursor()
   print ("Opened database successfully");
   
   def select():
       #database_conn()
       print ("inside select")
       curs.execute("""
           SELECT year(dt) AS YEAR, max(air_temp) AS MAX_TEMP 
           FROM TEMP_DATA 
           WHERE air_temp != 9999 
           AND (quality_at = '0' || quality_at = '1' || quality_at = '4' || quality_at = '5' || quality_at = '9') 
           GROUP BY year(dt); 
           SELECT year(dt) AS YEAR, max(air_temp) AS MAX_TEMP 
           FROM MAX_TEMP9293 
           WHERE air_temp != 9999 
           AND (quality_at = '0' || quality_at = '1' || quality_at = '4' || quality_at = '5' || quality_at = '9') 
           GROUP BY year(dt)
           """
           )
   
       result = curs.fetchall()
   
       return result
   
   a = select()
   conn.close()
   print(a)
   

3. 我不是 100% 确定，但我不认为 MySQLdb 会按照您所指示的那样漂亮地打印输出，因为编写了这段代码。库中可能有一些方法可以做到这一点；可能值得更多研究。上面的代码将输出包含您的数据的元组列表(我认为；未经测试)。