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

Question

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.

Answer 1

The population column is a bigint(20) DEFAULT NULL & hence you cannot provide the value "%s" format it should be a integer which is being passed. Also on values front.

1. It can have NULL
2. Any integer value.

So incase you want it to be null & not empty string("") you can skip to insert value to population column all together by removing it from the insert command

INSERT INTO table_x(Unique_code,city,state,Govt)
        VALUES("%s", "%s", "%s","%s")
        ON DUPLICATE KEY UPDATE city = VALUES(city),state = VALUES(state), Govt = VALUES(Govt)""")

Answer 2

Assuming that each row of your CSV has 5 values, corresponding to the code, city, state, population, and government, in that order, you should be using this syntax for the insert query:

for row in csv_data:
    params = row.split(",")
    sql = """
    INSERT INTO table_x (Unique_code, city, state, population, Govt)
    VALUES(?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE city = VALUES(city), state = VALUES(state),
                            population = VALUES(population),Govt = VALUES(Govt)"""
    cursor.execute(sql, params)

The first parameter to execute() should be the SQL prepared statement, and the second parameter should be a tuple. In this case, it should be a tuple with 5 values, corresponding to the five ? placeholders in the VALUES clause of your query. An example of a valid tuple here might be:

params = ('ABC123', 'New York', 'New York', 15000000, 'USA',)

Then call via:

cursor.execute(sql, params)