SQL returning extra data.

Question

Hey there, was wondering if anyone could help a newbie on SQL and Python. I thought I had a pretty decent grasp of it, however something odd happened recently.

Here is the the following code snipped from a larger portion:

  try:
      self.db.query("SELECT * FROM account WHERE email = '{0}' AND pass = '{1}'".format(self.mail.strip(self.bchars),self.pw.strip(self.bchars)))
  except MySQLdb.Error, e:
      print "Error %d: %s" % (e.args[0], e.args[1])

  exists = self.db.store_result().fetch_row()
  print "EXISTS",exists

It use to print this:

EXISTS ((2, '[email protected]', '1234', 1, 0, 2161, '192.168.1.47', 0),)

Now, it prints this: It use to print this:

EXISTS ((2L, '[email protected]', '1234', 1L, 0L, 2161, '192.168.1.47', 0L),)

I have no idea where these L's came from. I checked the SQL Database and even reloaded it to be sure. I have reverted all my code for the last day (where all was functioning), but still can't find a solution. I have also tried searching, but I am not even sure what this problem is even called so it's hard to search. Thanks for any help or information anyone can provide.

Answer 1

Is this because the data type in the SQL is 'long'?

As you can see in the tuple, they still are valid numbers, not strings, so this should not pose any problem.

Answer 2

The L's indicate that the numbers are of the type Long. Your field types in the db are probably set to be of this type.

Answer 3

2L is the long version of 2: different object but the same value.

>>> print 2L == 2
True

>>> print 2L is 2
False

Are you running all the same (python, packages, db, OS...)?

Answer 4

I think, python's dbapi is supposed to always return integer-fields as long.

Anyway, 10L, 5L and so on is the way repr (which is used on every item of a tuple in your case) works for longs.

One more thing. I see, you are using MySQLdb. In that case, I strongly suggest, that you stop using the c-api wrapper, but instead use the "real" interface, that has all that automatic conversion/escaping and a bunch of other wonderful things (dict-cursors for one).

This will save you a lot of grief and WILL make your code more stable/secure. In short, just forget about _mysql. Trust me.

Your example can be rewritten as this (using the proper interface):

import MySQLdb

db = MySQLdb.connect(host=your_host, db=your_db,
                     user=your_user, passwd=your_password)

cur = db.cursor()
cur.execute("""SELECT * FROM account WHERE email = %s AND pass = %s """,
            (self.mail, self.pw))
result = cur.fetchall()
print "exists:", result

This does the same as you are doing (except the error handling), but without manual string-formatting, escaping and so on.

I know, this is going to be downvoted for irrelevance, probably, but if this answer helps even a single person to start using the proper database api, that would be really great.