May-27-2017, 09:22 AM
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
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