cursor.fetchall() returns extra characters using MySQldb and python

Question

When I'm using python to fetch results from a SQL database I get extra charters at the beginning and end of the returned value. For example the below code returns ((56L,),) instead of 56, does anyone know how to get just the value... and what the (( ,),) actually mean...?

hp= 56
id= 3

database = MySQLdb.connect (host="localhost", user = "root", passwd = "", db = "db")

cursor = database.cursor()

cursor.execute("UPDATE period_option SET points =%s WHERE period_option_id =%s", (hp, id))

cursor.execute("SELECT points FROM period_option WHERE period_option_id =%s", (po_id_home))
results = cursor.fetchall()
print results

Hai Vu · Accepted Answer · 2013-02-13 19:30:19Z

10

fetchall() returns a list (really: a tuple) of tuples. Think of it as a sequence of rows, where each row is a sequence of items in the columns. If you are sure your search will return only 1 row, use fetchone(), which returns a tuple, which is simpler to unpack. Below are examples of extracting what you want from fetchall() and fetchone():

# Use fetchall():
((points,),) = cursor.fetchall()  # points = 56L

# Or, if you use fetchone():
(points,) = cursor.fetchone()     # points = 56L

answered Feb 13, 2013 at 19:30

Hai Vu

41.5k16 gold badges75 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

skipper21 · Accepted Answer · 2017-08-31 12:29:53Z

4

If you are using your query to get just one value you can just take the 0th index.

results = cursor.fetchall()

if the result has only one value useresults[0]. It should give you the value you are looking for. Sometimes we get a huge result when we run a query, in this situation, we will have to iterate through the values and assign it to the list.

>>> result
('58',)
>>> result[0]
'58'

When you run the query for huge items you get output something like this when you use curosr.fetchall()

(('58',),('50',),('10'),)

Use follow code to get the data in list format

>>> results=(('58',),('50',),('10'),)
>>>[x[0] for x in results] --> code
   ['58', '50', '1']

answered Aug 31, 2017 at 12:29

skipper21

2,4952 gold badges20 silver badges14 bronze badges

Comments

SiHa · Accepted Answer · 2016-10-21 11:09:34Z

3

Try the following method, it will be help to convert fetchall() output into better list :

 row = cursor.fetchall()
 print row 
 output is : [(1,), (2,), (3,), (4,)]
 num = list(sum(row, ()))
 print num
 output is : [1, 2, 3, 4]

edited Oct 21, 2016 at 11:09

SiHa

8,50613 gold badges39 silver badges48 bronze badges

answered Oct 21, 2016 at 9:45

Nagesh HS

952 gold badges3 silver badges14 bronze badges

1 Comment

framontb Over a year ago

I did it to create a set: fechasSet = set(sum(rows, ()))

mechanical_meat · Accepted Answer · 2013-02-13 19:53:15Z

1

56L is a long integer. "Long integers have unlimited precision." They can be used just like plain integers.

You can see just the long value by doing something like:

for result in results:
    print result[0]

Example REPL session:

>>> results = ((56L,),)
>>> for result in results:
...     print(result[0])
...
56

.fetchall() is defined in the DB-API as returning the remaining rows "as a sequence of sequences (e.g. a list of tuples)". MySQLdb's default is to use a tuple of tuples which looks like this (( ,),) or even like this ( (1,2,3), (4,5,6) ) where the individual rows of the resultset are the inner tuples -- and thus are always of the same length.

edited Feb 13, 2013 at 19:53

answered Feb 13, 2013 at 19:19

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

Collectives™ on Stack Overflow

cursor.fetchall() returns extra characters using MySQldb and python

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related