Error Inserting records into mariadb table using Python : 1366 (22007): Incorrect integer value: '%s' for column

vikrant_kumar24 — Mon, 25 May 2020 04:02:18 GMT

I am inserting records in maria db table from a file using python. Population column in the file is empty. I want it to go as empty value in table as well. Population column in table is set as integer and can accept null value. I am trying the below code -

Table Definition -
CREATE TABLE `local_db`.`table_x` (
`Unique_code` varchar(50) NOT NULL,
`city` varchar(200) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`population` bigint(20) DEFAULT NULL,
`Govt` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

```
input_file = "input_file"
csv_data = csv.reader(open(input_file))
try:
connection = mysql.connector.connect(host='localhost',
database='local_db',
user='root',
password='root',
port = '3306')

cursor = connection.cursor()
for row in csv_data:
cursor.execute("""
INSERT INTO table_x(Unique_code,city,state,population,Govt)
VALUES("%s", "%s", "%s","%s", "%s")
ON DUPLICATE KEY UPDATE city = VALUES(city),state = VALUES(state), \
population = VALUES(population),Govt = VALUES(Govt)""")
connection.commit()
print(cursor.rowcount, "Record inserted successfully into table_x")
cursor.close()

except mysql.connector.Error as error:
print("Failed to insert record into table_x table {}".format(error))

finally:
if (connection.is_connected()):
connection.close()
print("MySQL connection is closed")
```

But I am getting below error -

Failed to insert record into table_x table 1366 (22007): Incorrect integer value: '%s' for column `local_db`.`table_x`.`population` at row 1
MySQL connection is closed

In other thread it was suggested to change
SET sql_mode = ""
But its not an option for me since I would be running it on organization server which I can not change for this only. Please suggest what code changes I can do here to handle this situation.

Re: Error Inserting records into mariadb table using Python : 1366 (22007): Incorrect integer value: '%s' for column

DennisJaheruddi — Thu, 13 Aug 2020 11:44:45 GMT

This message is labeled NiFi, so I assume you have NiFi available?

In that case, look at finding the right processor for the job, something like ExecuteSQL may be a good starting point.

----

If your question is purely about how to make python and mariaDB interact, this may not be the best place to ask it.

question Error Inserting records into mariadb table using Python : 1366 (22007): Incorrect integer value: '%s' for column in Support Questions

Error Inserting records into mariadb table using Python : 1366 (22007): Incorrect integer value: '%s' for column

Re: Error Inserting records into mariadb table using Python : 1366 (22007): Incorrect integer value: '%s' for column