How to insert DEFAULT value with MySQLdb string formatting?

Question

I have run into this a few times now, where I'm trying to insert (or bulk insert) into a MySQL table using VALUES without defining the columns explicitly, but there is an auto_increment field I want to let auto_increment, or a generated column that I can't insert a value for.

Specifically, let's say I have a table with three columns, two for numbers and one generated column that's the sum of those numbers:

CREATE TABLE `addition` (
  `num_1` int DEFAULT NULL,
  `num_2` int DEFAULT NULL,
  `sum` int GENERATED ALWAYS AS ((`num_1` + `num_2`)) VIRTUAL
)

If I want to insert values to this database with a MySQLdb cursor object cur, I can't do:

cur.execute('INSERT INTO addition VALUES %s', [(2, 2, 'DEFAULT')])

...because you can't define the value for the generated field "sum", and 'DEFAULT' here is interpreted as the literal string. You'll get MySQL error 3105: The value specified for generated column 'sum' in table 'addition' is not allowed.

But the same error occurs for any value I could think to put in place of 'DEFAULT', for example None or False.

So is there any way to pass a value in the data section (i.e. [(2, 2, <something>)]) to tell MySQL to use the default value for the sum column? Or is the only way to define it in the SQL itself, i.e.

cur.execute('INSERT INTO addition VALUES (%s, %s, DEFAULT)', [2, 2])

This would be helpful when the table structure isn't known, or is prone to change, and you don't want to hard-code which fields should insert as DEFAULT.

--Edit--

Some clarification post-discussion in the comments, if I were to try cur.execute('INSERT INTO addition VALUES %s', [(2, 2, 'DEFAULT')]), this tries to insert the literal string 'DEFAULT', similarly for None, or any other value I could think of. So the question is really a Python question, is there a field (e.g. MySQLdb.DEFAULT()) that I can pass to accomplish this. So the final result would look something like cur.execute('INSERT INTO addition VALUES %s', [(2, 2, MySQLdb.DEFAULT())])

Answer 1

If you want a computed sum column, than handle it on the database side via a generated column:

CREATE TABLE addition (
    num_1 INT,
    num_2 INT,
    sum AS (num_1 + num_2)
);

Then, when you insert two numbers, MySQL will handle the math for you:

INSERT INTO addition (num_1, num2) VALUES (2, 2);

Note that generated columns in MySQL are virtual by default, meaning that the sum won't actually be persisted, by rather would happen at the time you do a select.