Working with MySQL booleans in python

Question

I am querying a MySQL database in python and selecting a boolean value -- so the response from MySQL is either the string 'True' or 'False'. I would like to execute further code based on the boolean value from MySQL.

E.g.

data = 'False'  # assume this is what was returned by MySQL.
if data:
  print 'Success'  #really this would be where I would execute other if True.
else:
  print 'Fail'  #really this would be where I would execute other code if False

But I can't do this because

if data:

will always return True

So how do I convert the string that MySQL is returning into a boolean in python?

Currently I have:

data = 'False'  # assume this is what was returned by MySQL.
if data == 'True':
  print 'Success'
else:
  print 'Fail'

I believe there must be a better way to do this in python -- likely there is something simple I am missing.

Answer 1

Boolean in MySQL is TINYINT(1). Checking for 1 or 0 might work

Answer 2

If the column is always one of the actual strings "False" or "True" (as opposed to integers or something) then I'd recommend some variation on:

value = {'False' : False, 'True' : True}[data]

Answer 3

If your db connection doesn't know how to transform the values for you, then you need to use a better one. This should not be something you need to do yourself. If these are not actual booleans, but just an int column you only store 0 or 1 in, then you should fix your schema. Alternatively, if you always get the values 'True' and 'False', maybe you are accidentally converting it to a string somewhere?

Answer 4

Your solution is actually quite OK, but there are alternatives:

If you are using Python 2.5 or higher, you can shorten the if statement:

print 'Success' if data == 'True' else 'Fail'

If you are finding yourself repeating the check often, you might consider writing a function for it to make it more readable:

def is_true(mysql_boolean):
    if mysql_boolean == "True":
        return True
    else:
        return False

# now you can use this:
if is_true(data):
    # this is really going to be more than one line of code anyway.
    # or maybe a function call, in which your solution might be enough.
    print "Success"
else:
    print "Fail"

You can achieve the same thing with a dictionary, but I don't find this as elegant:

mysql_bool = {'True': True, 'False': False}

if mysql_bool[data]:
    print "Success"

That said, what are you using to connect to the DB? There is probably a way to directly get a bool out of there. Please update your Question!

Answer 5

You can find the place MySQLDdb converts the values in the converters.py file in the MySQLdb directory.

here's the snippet dealing with bool:

conversions = {
    ...
    types.BooleanType: Bool2Str,
    ...
}

And the Bool2Str function:

def Bool2Str(s, d): return str(int(s))

If you want different behavior, import the conversions dict and change it.