Retrieving column value, using python-mysql, tornado framework

Question

I am working on a web application, using tornado framework and mysql-python for the database connector.

The requirement is like this - When the user logs in and say buys a product, the database has a table which has userid, and all the product related columns, which should be updated.

So there is a usertable, productsbought table. Say there are 5 users with id 1,2,3,4,5. So when user 3 buys a product the productsbought table will be updated with user id 3 and all the details of the product he bought.

Coming to the problem.

username = self.current_user
print username # prints correct username say thirduser with id 3
db = MySQLdb.connect(host='localhost', user='root', db= 'db') 
    uid = None       
    cur = db.cursor()

    uid = cur.execute("""Select userid from user_table where username = %s """, (username,))

    print uid # should display 3, but displays 1

    cur.execute("""INSERT INTO productsbought (userid, pid, pname, model) VALUES ( %s, %s, %s,     %s)""", (uid, pid, pname, model,))
    # The insert is working fine but for the wrong user.

Now this uid should ideally print 3 (The id corresponding to thirduser, who has logged in to the application). But its printing 1.

So whoever logs in - their username is getting displayed correctly, but their userid is taken as 1 and the productsbought table is getting updated for firstuser.

In mysql i queried

select userid from user_table where username = "thirduser"

And it is displaying 3 correctly.

So everything looks fine but there is something wrong!! This is driving me nuts. Please help me out!!

Answer 1

Have you tried wrapping the %s in quotes? Also, is it possible there's some whitespace surrounding self.currentuser?

I'd try:

uid = cur.execute("""SELECT userid FROM user_table WHERE username='%s'""", (username,))

Answer 2

The problem is that you're trying to get your uid value from the .execute method. Please refer to the Python Database API Specification v2.0 (PEP 249) section describing the .execute method where it says:

Return values are not defined.

In other words, do not use the return value of this method – at least, not unless you're familiar enough with the module to understand exactly how it's being implemented.

Instead, you need to use another method to "fetch" the result set from the cursor. The example section in the MySQL-Python User's Guide shows the use of one of these methods, .fetchone, for getting a single row:

To perform a query, you first need a cursor, and then you can execute queries on it:

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

...

And now, the results:

>>> c.fetchone()
(3L, 2L, 0L)

If you expect that you can only ever get one row back from a particular query (e.g., when aggregating an entire table), it's reasonable to use .fetchone. If there are more rows, you can call this method repeatedly until you run out of rows. In many cases, however, you'll want to use .fetchall to store the entire set:

>>> import MySQLdb
>>> conn = MySQLdb.connect(user='foo', passwd='bar', db='test')
>>> curs = conn.cursor()
>>> curs.execute('create temporary table t (a int);')
0L
>>> curs.executemany('insert into t values (%s);', xrange(10))
10L
>>> curs.execute('select count(*) from t;')
1L
>>> curs.fetchall()
((10L,),)
>>> curs.execute('select a from t where a % 2;')
5L
>>> curs.fetchall()
((1L,), (3L,), (5L,), (7L,), (9L,))
>>> curs.fetchall()
()

Note that rows are only fetched once; if you call .fetchall a second time without executing another query, you get an empty result set (a tuple of 0 tuples). This means you should store the return value of the fetch methods in order to access them later.

So, to apply this to your example, replace this line:

uid = cur.execute("""Select userid from user_table where username = %s """, (username,))

With something more like this:

cur.execute("""Select userid from user_table where username = %s """, (username,))
result = cur.fetchone()  # get a single row
uid = result[0]  # get the first value from that row

Alternatively, using .fetchall:

cur.execute("""Select userid from user_table where username = %s """, (username,))
result = cur.fetchall()  # get any/all rows
uid = result[0][0]  # get the first value from the first row

Which pattern you use depends on the query, the circumstance, and your personal taste. In either case, you may also want to handle the possibility of fetching an empty set if the username isn't found in the table.