Python with mysql query insertion - single parameter

Question

Below are the code that is not working

#!/usr/bin/python

import urllib
import datetime
import mysql.connector

data['price'] = 100

# Write to database
cnx = mysql.connector.connect(user='user', password='pw', database='db')
cursor = cnx.cursor()

query = ("INSERT INTO records(price) VALUES(%s) ")
cursor.execute(query, (data['price']))

cnx.commit()

cursor.close()
cnx.close()

MySQL has the corresponding table and column.

id (PRIMARY INT AUTO_INCREMENT)
price (FLOAT)

There is no connection problem.

It gives the following error. Any idea?

Traceback (most recent call last): File "./sys-fetchdata.py", line 22, in cursor.execute(query, (data['price'])) File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 507, in execute self._handle_result(self._connection.cmd_query(stmt)) File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 722, in cmd_query result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 640, in _handle_result raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1064 (42000): 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 '%s)' at line 1

@hiro_protagonist Thanks. You should put this in answer. This is where the problem roots. — Clinton Lam
– Clinton Lam, Commented Aug 14, 2017 at 13:30

hiro protagonist · Accepted Answer · 2017-08-14 13:54:47Z

4

the MySQLCursor.execute() method takes a tuple (or a dict) as second (params) argument.

in your case: it should be (data['price'], ) (note the additional comma; without it the brackets have no effect) instead of (data['price']).

answered Aug 14, 2017 at 13:54

hiro protagonist

47.3k17 gold badges93 silver badges119 bronze badges

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

2 Comments

Clinton Lam Over a year ago

Thanks, this is some strange bug. Takes me hours before posting this question.

hiro protagonist Over a year ago

i agree: the error message should be a lot more informative than it is...

akash karothiya · Accepted Answer · 2017-08-14 13:56:49Z

1

Let's try to find a solution:

Do not execute your query, instead mogrify and print it. For example:

query = ("INSERT INTO records(price) VALUES(%s) ")
query_to_be_executed = cursor.mogrify(query, (data['price']))
print(query_to_be_executed)

Next try to execute your printed query manually in bash or phpMyAdmin and fix it.
Finally use fixed query in your python code.

edited Aug 14, 2017 at 13:56

akash karothiya

5,9601 gold badge21 silver badges29 bronze badges

answered Aug 14, 2017 at 13:07

tpszer

531 silver badge6 bronze badges

3 Comments

Clinton Lam Over a year ago

Thanks for your answer. This should help the debugging process, but hiro had pointed out the exact root of problem.

Odysseas Over a year ago

mogrify is specific to PostgreSQL database adapter psycopg. MySQL Connector/Python does not have mogrify, so it cannot be used for the context of this question.

tpszer Over a year ago

Odysseas, I didn't know. I know this is wayaround but maybe author can use PostgreSQL database?

Collectives™ on Stack Overflow

Python with mysql query insertion - single parameter

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related