title: How can I get a MySQL database to insert a default value if there's an attempt to insert a null value with Python?
body: <p>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 <code>NULL</code> value in a column will result instead in that column's <code>DEFAULT</code> value being inserted.</p>
  <p>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:</p>
  <pre><code>qry = &quot;INSERT INTO table (col1, col2, ...) VALUES (%s, %s, ...)&quot;
  row_data_dict = defaultdict(lambda : None, {...})
  params = []
  for col in [col1, col2, ...]:
      params.append(row_data_dict[col])
  cursor.execute(qry, tuple(params))
  </code></pre>
  <p>My main problem is that setting <code>None</code> as the default in the dictionary results in either <code>NULL</code> being inserted or an error if I specify the row as <code>NOT NULL</code>. 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.</p>
  <p>I can't do the typical way of inserting <code>DEFAULT</code> by skipping over columns on the insert because while those columns might have the <code>DEFAULT</code> 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.</p>
  <p>The other way of inserting <code>DEFAULT</code> seems to be to have <code>DEFAULT</code> as one of the parameters (e.g. <code>INSERT INTO table (col1,...) VALUES (DEFAULT,...)</code>) but in my case setting the default in the dictionary to <code>'DEFAULT'</code> 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).</p>
  <p>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.</p>
  <p>EDIT: All the of the relevant columns are already labeled with a <code>DEFAULT</code> value, it doesn't seem to actually replace <code>NULL</code> (or python's <code>None</code>) when it's inserted.</p>
  <p>EDIT 2: The reason why I want to avoid <code>NULL</code> so badly is because <code>NULL != NULL</code> 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 <code>NULL</code> you end up with a bunch of copies of the same record because one of the values is unknown.</p>