I've read answers that do something similar but not exactly what I'm looking for, which is: attempting to insert a row with a NULL value in a column will result instead in that column's DEFAULT value being inserted.
I'm trying to process a large number of inserts in the mySQL Python connector with a large number of column values that I don't want to deal with individually, and none of the typical alternatives work here. Here is a sketch of my code:
qry = "INSERT INTO table (col1, col2, ...) VALUES (%s, %s, ...)"
row_data_dict = defaultdict(lambda : None, {...})
params = []
for col in [col1, col2, ...]:
params.append(row_data_dict[col])
cursor.execute(qry, tuple(params))
My main problem is that setting None as the default in the dictionary results in either NULL being inserted or an error if I specify the row as NOT NULL. I have a large number of columns that might change in the future so I'd want to avoid setting different 'default' values for different entries if at all possible.
I can't do the typical way of inserting DEFAULT by skipping over columns on the insert because while those columns might have the DEFAULT value, I can't guarantee it and considering I'm doing a large number of inserts I don't want to change the query string each time I insert depending on if it's default or not.
The other way of inserting DEFAULT seems to be to have DEFAULT as one of the parameters (e.g. INSERT INTO table (col1,...) VALUES (DEFAULT,...)) but in my case setting the default in the dictionary to 'DEFAULT' results in error (mySQL complains about it being an incorrect integer value on trying to insert into an integer column, making it seem like it's interpreting the default as a string and not a keyword).
This seems like it would be a relatively common use case, so it kind of shocks me that I can't figure out a way to do this. I'd appreciate any way to do this or get around it that I haven't already listed here.
EDIT: All the of the relevant columns are already labeled with a DEFAULT value, it doesn't seem to actually replace NULL (or python's None) when it's inserted.
EDIT 2: The reason why I want to avoid NULL so badly is because NULL != NULL and I want to have unique rows, so that if there's one row (1, 2, 3, 'Unknown'), INSERT IGNORE'ing a row (1, 2, 3, 'Unknown') won't insert it. With NULL you end up with a bunch of copies of the same record because one of the values is unknown.
You can use the DEFAULT() function in the VALUES list to specify that default value for the column should be used. And you can put this in an IFNULL() call so it will be used when the supplied value is NULL.
qry = """INSERT INTO table (col1, col2, ...)
VALUES (IFNULL(%s, DEFAULT(col1)), IFNULL(%s, DEFAULT(col2)), ...)"""