mysql.connector - incorrect integer value

[Milo]

Hi all,

I've been playing around with mysql.connector and trying to insert data into a mysql database. When using the %s or %d syntax I receive a "Incorrect integer value" error but when using a value directly in the insert the code works as expected. I'm no Python buff so very likely something I'm missing but I have not idea what it might be, any help would be much appreciated.

Just to add, I am aware of sql-mode issue in the my.cnf/my.ini file that may cause this sort of issue and I have address it by changing the the mysql-mode to mysql-mode=""

The first code block below uses a value directly in the insert statement and works correctly. The second code block uses the %d syntax and throws the below error

import mysql.connector

cnx = mysql.connector.connect(user='foo', password='bar', database='db', host='ipaddress')
cursor = cnx.cursor()

stmt = ("INSERT INTO schedule (gid) VALUES ('12345')")

cursor.execute(stmt)

cnx.commit()
cursor.close()
cnx.close()

import mysql.connector

cnx = mysql.connector.connect(user='foo', password='bar', database='db', host='ipaddress')
cursor = cnx.cursor()

stmt = ("INSERT INTO schedule (gid) VALUES ('%d')")
value = 12345

cursor.execute(stmt, value)

cnx.commit()
cursor.close()
cnx.close()

Error:
Traceback (most recent call last):
 File "C:\Users\mmcsweeney\Desktop\test.py", line 9, in <module>
   cursor.execute(stmt, value)
 File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 515, in execute
   self._handle_result(self._connection.cmd_query(stmt))
 File "C:\Python27\lib\site-packages\mysql\connector\connection.py", line 488, in cmd_query
   result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
 File "C:\Python27\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
   raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1366 (HY000): Incorrect integer value: '%d' for column 'gid' at row 1

[snippsat]

Try:

cur.execute("INSERT INTO schedule (gid) VALUES (%s)", (value))

[Milo]

Thanks for the response Snippsat.

I've given it a bash and no dice. Your suggestion (below) produces the bellow Traceback

I've had a look at the mysql.connector documentation and it seems to suggest the way I was doing it previously should be correct?

import mysql.connector

cnx = mysql.connector.connect(user='foo', password='bar', database='db', host='ipaddress')
cursor = cnx.cursor()

value = 12345

cursor.execute("INSERT INTO schedule (gid) VALUES (%s)", (value))

cnx.commit()
cursor.close()
cnx.close()

Error:
[b]Traceback (most recent call last):
  File "C:\Users\mmcsweeney\Desktop\test.py", line 8, in <module>
    cursor.execute("INSERT INTO schedule (gid) VALUES (%s)", (value))
  File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 515, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python27\lib\site-packages\mysql\connector\connection.py", line 488, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Python27\lib\site-packages\mysql\connector\connection.py", line 395, 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
[Finished in 0.1s][/b]

---

Ok so I had a quick look at it again and the below seems to work as expected.
Thanks Snippsat for getting me moving in the right direction

import mysql.connector

cnx = mysql.connector.connect(user='foo', password='bar', database='db', host='ipaddress')
cursor = cnx.cursor()

value = 12345

cursor.execute("INSERT INTO schedule (gid) VALUES ('%s')" % (value))

cnx.commit()
cursor.close()
cnx.close()

[nilamo]

Ok so I had a quick look at it again and the below seems to work as expected.

Well, no.  That's using string formatting and manual quoting, which bypasses mysql's sanitation.  So it works, but only if nobody ever tries to break your database.
This is the way you should be doing it:

cursor.execute("INSERT INTO schedule (gid) VALUES (%s)", (value, ))

More examples can be found in the docs: https://dev.mysql.com/doc/connector-pyth...elect.html