Python-Warning: Truncated incorrect DOUBLE value

Question

I am trying to fetch "bar_ids" from "foo" table using mysql-python. To this end, I got the following error and I am receiving only few bar_ids. How do I get this correctly without warning error?

My table is shown below:

create table foo (foo_id int, bar_id int, foo_name varchar(255))

My query & Error:

foo_ids = 16600, 16602, 16607, 16609, 16611, 16613

Warning: Data truncated for column 'bar_id' at row 1

cursor.execute("""select bar_id from foo where foo_id in (%s)""", (foo_ids,))

Warning: Truncated incorrect DOUBLE value: '16600, 16602, 16607, 16609, 16611, 16613'

cursor.execute("""select bar_id from foo where foo_id in (%s)""", (foo_ids,))

possible duplicate of Executing "SELECT ... WHERE ... IN ..." using MySQLdb — cwallenpoole
– cwallenpoole, Commented Feb 18, 2015 at 12:45

Community · Accepted Answer · 2017-05-23 10:33:48Z

3

This question has been asked before in SO. Putting in a response so it might help to give some context and help @brisk read this sort of error going forward.

The problem is that it is taking 'foo_ids' as a string and not as individual values. So it's essentially trying to run:

select bar_id from foo where foo_id in ('16600, 16602, 16607, 16609, 16611, 16613')

Notice the quotes? You want it to run:

select bar_id from foo where foo_id in (16600, 16602, 16607, 16609, 16611, 16613)

so when you reported your error, it included 'quotes' around the numbers.

There are a few solutions that you can consider.

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Feb 18, 2015 at 12:48

Duniyadnd

4,0431 gold badge24 silver badges30 bronze badges

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

Comments

cwallenpoole · Accepted Answer · 2015-02-18 12:49:12Z

1

Basically, you need to re-create the param list yourself:

param_list = '%s,' * (len(foo_ids) - 1) + '%s'
sql = """select bar_id from foo where foo_id in (%s)""" % param_list
cursor.execute(sql, foo_ids)

answered Feb 18, 2015 at 12:49

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Collectives™ on Stack Overflow

Python-Warning: Truncated incorrect DOUBLE value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related