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.
... View more
