Python with mysql query insertion - single parameter

0
votes

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

2
pythonmysqlinsert
@hiro_protagonist Thanks. You should put this in answer. This is where the problem roots. - Clinton Lam

2 Answers

4
votes

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']).

1
votes

Let's try to find a solution:

  1. 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)

  2. Next try to execute your printed query manually in bash or phpMyAdmin and fix it.

  3. Finally use fixed query in your python code.