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

Don't construct INSERT statement yourself using str methods like this — buran
– buran, Commented Sep 27, 2023 at 8:29
I'd suggest reviewing How to use variables in SQL statement in Python? for guidance on how to use variable values with SQL statements. This will get you the NULL behaviour that you want. — snakecharmerb
– snakecharmerb, Commented Sep 27, 2023 at 9:27
@snakecharmerb why does I have "Unknown column 'None' in 'field list' " error while it should insert NULL in imgs column? I obviously have no None column and never refer to it in code — SomeRandomDude111
– SomeRandomDude111, Commented Sep 27, 2023 at 9:47
Because SQL assumes that an unquoted character string in the values clause is a column name. — snakecharmerb
– snakecharmerb, Commented Sep 27, 2023 at 9:50

SomeRandomDude111 · Accepted Answer · 2023-09-29 05:43:19Z

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)

Collectives™ on Stack Overflow

Can not properly insert NULL value in mysql database using python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related