Can not properly insert NULL value in mysql database using python

Question

I use json format to send the data, here is the fragment of my code which returns an error

    def blocks_files(self, db, order_id, op_id, file_ids, images):
        """Creating record in blocks_files table."""
        files_list = []
        keys = list(images.keys())
        for file in file_ids:
            if str(file) in keys:
                new = str()
                for i in range(len(images[str(file)])):
                    new += '%s, ' % str((images[str(file)])[i])
                files_list.append((order_id, op_id, file, new))
            else:
                files_list.append((order_id, op_id, file, None))
        try:
            logging.debug(
                "Inserting data in blocks_files table."
            )
            cursor = db.cursor()
            query = (
                f"INSERT INTO {self.FILES_TABLE}"
                f"(order_id, op_id, file_id, imgs)"
                f"VALUES {(', '.join(map(str, files_list)))};"
            )
            cursor.execute(query)
            db.commit()
            cursor.close()
            logging.debug(
                "Data inserted successfully!"
            )
        except connector.Error as err:
            logging.error(err)

The problem is in files_list.append((order_id, op_id, file, None)) part. At first seems like everything works fine, but I get 1054 (42S22): Unknown column 'None' in 'field list' error in logs. However I expect that it should put NULL in imgs column... I used the following topic as inspiration but it's no use. Trying to send "NULL" as a string is not what I want, I need proper SQL NULL value, the difference is on the screenshot proper NULL value "NULL" value

Answer 1

I found one not so elegant solution to my problem by adding one more variable and two if statements

def blocks_files(self, db, order_id, op_id, file_ids, images):
            """Creating record in blocks_files table."""
            files_list = []
            files_list_no_imgs = []
            keys = list(images.keys())
            for file in file_ids:
                if str(file) in keys:
                    images_list = str()
                    for i in range(len(images[str(file)])):
                        images_list += '%s, ' % str((images[str(file)])[i])
                    files_list.append((order_id, op_id, file, images_list))
                else:
                    files_list_no_imgs.append((order_id, op_id, file))
            try:
                logging.debug(
                    "Inserting data in blocks_files table."
                )
                cursor = db.cursor()
                if files_list:
                    query = (
                        f"INSERT INTO {self.FILES_TABLE}"
                        f"(order_id, op_id, file_id, imgs)"
                        f"VALUES {(', '.join(map(str, files_list)))};"
                    )
                    cursor.execute(query)
                if files_list_no_imgs:
                    query = (
                        f"INSERT INTO {self.FILES_TABLE}"
                        f"(order_id, op_id, file_id)"
                        f"VALUES {(', '.join(map(str, files_list_no_imgs)))};"
                    )
                    cursor.execute(query)
                db.commit()
                cursor.close()
                logging.debug(
                    "Data inserted successfully!"
                )
            except connector.Error as err:
                logging.error(err)