CODEWITHSUNDEEP
pythonmysqlpython-3.x
Varun
Varun

Reputation: 137

how to return mysql query result using python

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)

Upvotes: 1

Views: 23116

Answers (3)

Varun
Varun

Reputation: 137

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

Upvotes: 0

Daniel
Daniel

Reputation: 2459

  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.

Upvotes: 2

gold_cy
gold_cy

Reputation: 14216

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.

Upvotes: 1

Related Questions