CODEWITHSUNDEEP
pythonmysqldjangopython-2.7
Alok
Alok

Reputation: 789

Why does mysql return string in format of ('abc') when used with python

The code:

cur = connection.cursor()
cur.execute("SELECT username from users where customer_id = %s", (cust))
name = cur.fetchone()

gives output for name and cust as : (u'abc',) (u'abc123',)

How can I get output as a proper string without the (u' ')?

Upvotes: 2

Views: 3312

Answers (3)

Air
Air

Reputation: 8615

As Martijn said in his answer, even if you ask for only a single column, you will always fetch a one-column row, never the bare column. So it might be more clear for you to assign the results of fetchone() to a variable like row than to a variable like some_column_name. Then you can manipulate that row to extract the specific data you want.

You might also find it useful to to use a cursor that returns a dictionary instead of a tuple. Like this:

import MySQLdb.cursors

cur = connection.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cur.execute("SELECT username from users where customer_id = %s;", (cust,))
row = cur.fetchone()    # {'username': 'abc123'}
name = row['username']  # 'abc123'

This is especially nice for sending your query result into some custom function or class as keyword arguments corresponding to your column names; for example:

cur = connection.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cur.execute("SELECT name, age, years_in_residence FROM natural-born_citizens;")
query_result = cursor.fetchall()    # a tuple of dictionaries

def eligible(name, age, years_in_residence):
    if age >= 35 and years_in_residence >= 14:
        return '{} is eligible to run for U.S. President.'.format(name)
    else:
        return '{} is not eligible.'.format(name)

for row in query_result:
    print eligible(**row)

# Richard Stallman is eligible to run for U.S. President.
# Kermit the Frog is eligible to run for U.S. President.
# Miley Cyrus is not eligible.
# Oliver Smoot is eligible to run for U.S. President.

See also: Documentation for unpacking argument lists with * and **

Upvotes: 0

Martijn Pieters
Martijn Pieters

Reputation: 1123700

You are fetching a row, not just one column from the database. Each row is a tuple, and because your query returns row with just one column in them, you get tuples of length one.

If you wanted to have just the first column of a row, use indexing:

name = cur.fetchone()[0]

The column in the tuple is a unicode string, and the python representation of unicode strings use a u prefix:

>>> u'unicode value'
u'unicode value'
>>> print u'unicode value'
unicode value

This makes debugging easier; you can just copy the value straight back into a Python interpreter and know you got the exact same value.

When printing a standard container in Python (such as a tuple, dictionary, list, etc.) the contents of the container always use representations:

>>> print ['list', 'with', 'strings']
['list', 'with', 'strings']
>>> print ['list', 'with', 'strings'][0]
list

Upvotes: 3

sk1p
sk1p

Reputation: 6725

(u"foo",) is a tuple with one element. u is just the prefix for unicode strings. You can get the string by indexing: name[0]

Upvotes: 0

Related Questions