CODEWITHSUNDEEP
pythonmysqlmysql-connector-python
John Doe
John Doe

Reputation: 53

How can I get a MySQL database to insert a default value if there's an attempt to insert a null value with Python?

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.

Upvotes: 5

Views: 3400

Answers (2)

Barmar
Barmar

Reputation: 781761

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)), ...)"""

Upvotes: 3

Gharbad The Weak
Gharbad The Weak

Reputation: 1641

Welcome to Stackoverflow. What you need to do is in your database add a default value for the column you want to have the default value. When you create your table just use DEFAULT and then the value after you create the column in the table, like this:

CREATE TABLE `yourTable` (`id` INT DEFAULT 0, .....)

if you have already created the table and you need to alter the existing column, you would do something like this:

ALTER TABLE `yourTable` MODIFY `id` INT DEFAULT 0

so in your insert statement coming from python, as long as you pass in either NULL or Nothing for the value of that column then when the row is inserted into your database, the default value will be populated for that column

Another thing to keep in mind is that you have to pass in the proper number of values when you have a default set up for a column. Say you have a table with 3 columns, we'll call them colA, colB and colC. if you want to insert a row with colA_value for colA, nothing for colB so it will use it's default value and colC_value for colC then you need to still pass in 3 values that will be used for your insert. If you just passed in colA_value and colC_value, then colA will get colA_value and colB will get colC_value and colC will be null. you need to pass in values that will be interpreted by MySQL like this:

INSERT INTO `yourTable` (`colA`, `colB`, `colC`) 
VALUES
('colA_value', null, 'colC_value')

even though you are not passing in anything for colB you need to pass a null value from your python program by either passing null or None to MySQL for the value for colB in order to get colB to be populated with it's default value

if you only pass in 2 values to MySQL to insert a row in your table, the insert statement under the hood will look like this:

INSERT INTO `yourTable` (`colA`, `colB`, `colC`) 
VALUES
('colA_value', 'colC_value')

which would result in colA getting set to colA_value, colB getting set to colC_value and colC being left as null

if you are passing in the right number of values to be inserted into MySQL (that would mean you need to include null or None for the value to be inserted into the column with the default value) than that is another story. Please let me know if you are passing in the right number of values so I can help you troubleshoot further if needed.

Upvotes: 1

Related Questions