Python&Mysql. How to connect the list correctly so that it can be used in a query?

Question

MySQL

id = ['13364', '13365'];
ids = ','.join(id);

cursor.execute("UPDATE articles SET sticky=0 WHERE id IN (%s)", ids);

I get a warning Warning: Truncated incorrect DOUBLE value: '13364,13365' after which the query works only with the first number from the list.

Answer 1

In fact, you are perverts!
id = ['13364', '13365']
cursor.execute("UPDATE articles SET sticky=0 WHERE id IN (%s)" % (",".join(["%s"] * len(id)) , id)
I don't know the delimeter for the muscle, but don't put a semicolon in python!!!

Answer 2

Try putting a space
ids = ', '.join(id);


Answer 3

same with space Warning: Truncated incorrect DOUBLE value: '13364, 13365'
just in case Python 2.6.5

Answer 4

I understood the reason. I found the _last_executed variable in the cursor which contains the last "compiled" query.

print cursor._last_executed;
UPDATE blurb_topics SET paid_service='', vip=0 WHERE id IN ('13364, 13365')

I don't know how to get rid of the quotes

Answer 5

You have '13364', '13365' strings and it is logical that if you combine them with ',' you get the string '13364,13365'
ids = "','".join(id)
cursor.execute("UPDATE articles SET sticky=0 WHERE id IN ('%s')", ids);

Answer 6

ids = "','".join(vip_id);
cursor.execute("UPDATE articles SET sticky=0 WHERE id IN ('%s')", ids);

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '13364\\',\\'13365'')" at line 1")
all the trouble is in quotation marks

Answer 7

ids = ','.join(vip_id);
cursor.execute('UPDATE articles SET sticky=0 WHERE id IN (%s)', ids);
print cursor._last_executed;

Warning: Truncated incorrect DOUBLE value: '13364,13365'

Answer 8

Digging on stackoverflow.com led to the syntax

ids = ', '.join(vip_id);
cursor.execute('UPDATE articles SET sticky=0 WHERE id IN (%s)' % (ids));

Works. I would like to get to the bottom of the truth of how the construction above works.