Fetch array after SELECT statement

Question

I am trying to write a script that works with MySql.

The script must select a row of a table and then print the result. This is the script:

#! /usr/bin/env python
import MySQLdb
import sys
id_room=sys.argv[1]
connection = MySQLdb.connect("localhost","residente","pinkrabbits","domotica")
cursor=connection.cursor()
command="SELECT active FROM home WHERE id_room = " + str(id_room)
print (command)
cursor.execute(command)
active=cursor.fetchall()
print(active)
cursor.close()
connection.close()
if active == 1:
 print "1"
else:
 print "Not 1"

and this is the result:

pi@raspberrypi /var/www/domotica/python $ sudo python luce.py 1
SELECT active FROM home WHERE id_room = 1
((1,),)
Not 1
pi@raspberrypi /var/www/domotica/python $

In the table on database the value "active" is 1, but when I compare the value in the python script with 1 the result is "Not 1".

Why?

Answer 1

The type of two variables is different. Try to print type(attivo) to figure out what is the type of two variables.

The function fetchall() return a tuple of tuples. So you need to compare the value inside it to 1, not the tuple itself.

if attivo[0][0] == 1:
 print "1"

Answer 2

The mysql interface is returning a tuple for each row returned in the query. So you need to actually look at each row and then pull out the first (and only) element:

first_row_matched = attivo[0]
first_column_of_first_row = first_row_matched[0]
if first_column_of_first_row == 1:
 print 'It works!'

Of course, it is designed this way so that when you issue a query like:

SELECT foo, bar FROM baz WHERE qux = 1;

The structure of the data from fetchall will still be the same.