how to return mysql query result using python

Question

the query result and expected output below.

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

This is my code. In the output in just getting no of records (i.e 2). How can i make it to show the full table?

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)

• tadman: That return looks like it's in the wrong spot here.
• gold_cy: also not exactly sure what exactly is it returning for you?
• Varun: edited my code.. im getting '2' as the output

Answer 1

1. The indentation in your code is off; as written, your code won't compile (you might just have to correct the formatting in your question submission).

2. @aws_apprentice is correct. You should include fetchall in your code. I would write it like this:

   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. I'm not 100% sure, but I don't think that MySQLdb will pretty-print the output as you indicated that you expect it, as this code is written. There may be some method in the library that does this; might be worth some more research. The code above will output a list of tuples (I think; untested) containing your data.

• Varun: Thanks Daniel, This code is giving count of rows (2) rather than the actual row data. so i used index for getting the row data. max = cursor.fetchall() #printing max temp for years print(max[0]) print(max[1])

Answer 2

You're missing fetchall

All you're doing is returning the number of results as opposed to the actual results themselves.

Change this:

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)")

To this:

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)")
a = cursor.fetchall()

Also there's no point of having commit there. You're not modifying any data within the actual table in this function, which is what it is intended for.

• Varun: I got this error AttributeError: 'int' object has no attribute 'fetchall'
• Varun: Okay i guess need to do fetchall() on cursor. cusror.fetchall()
• Varun: now im getting an object as output - <MySQLdb.cursors.Cursor object at 0x7fcf38a04160> : I tried converting it to string str(a) but still get the same output

Answer 3

I managed to get the output, this is the code is used.

databasename = 'vkp'
host = 'localhost'
user = 'root'
password = 'root'
#mysql connection
conn = MySQLdb.connect(host,user,password,databasename)
cursor = conn.cursor()
#defining sql select statements
print ("Opened database successfully");
sql = "SELECT year(dt) AS YEAR, max(air_temp) AS MAX_TEMP from TEMP_DATA WHERE air_temp != 9999 GROUP BY year(dt)"
sql1 = "SELECT year(dt) AS YEAR, max(air_temp) AS MAX_TEMP from MAX_TEMP9293 WHERE air_temp != 9999 GROUP BY year(dt)"
#list for storing sql queries
list = [sql,sql1]
#function for executing select statements 
def select():
 #iterating for loop
 for l in list:
 #executing query
 cursor.execute(l)
 max = cursor.fetchall()
 #printing max temp for years
 print(max[0])
 print(max[1])