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())])

Will using a column list help INSERT INTO addition (num_1, num_2) VALUES (2, 2)? Then the statement will always insert into num_1 and num_2 even if the structure changes. — user3366507
– user3366507, Commented Apr 22, 2022 at 3:54
Yes, many people argue that you should always include the field list in an INSERT to make the intention clear and avoid confusion. — Tim Roberts
– Tim Roberts, Commented Apr 22, 2022 at 3:57
@Steve Yes that would prevent issues with structure changes, but I'm curious if I can accomplish this without the explicit table definition. That is definitely best practice as Tim Roberts points out. — Eric
– Eric, Commented Apr 22, 2022 at 4:00
@TimBiegeleisen this is just an hypothetical, so the example table is sufficient to demo the situation in question imo. If you need more details please let me know. — Eric
– Eric, Commented Apr 22, 2022 at 4:02

Tim Biegeleisen · Accepted Answer · 2022-04-22 04:05:59Z

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.

answered Apr 22, 2022 at 4:05

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Eric Over a year ago

Well put, and a helpful point, however that is already the condition I have in my question. I have updated the table definition to be a bit more clear. I'm not asking how to make a generated field, but rather if it's possible to insert to a table with a generated field without explicitly defining the column structure in the INSERT statement

Tim Biegeleisen Over a year ago

@Eric Your premise then doesn't make much sense. If you want to define the value for a virtual column yourself, then you shouldn't be using a virtual column for that. Now, a column in MySQL can have a default value. In that case, if you don't include such a column in the insert it will get whatever value is defined as the default.

Eric Over a year ago

Apologies, I must still not be explaining this well. I don't want to define the value for the column, I want it to retain the default (computed) value, I'm asking if this can be accomplished without defining the columns in the insert statement. E.g. in your answer you have INSERT INTO addition (num_1, num_2) VALUES (2, 2);, I want INSERT INTO addition VALUES (2, 2, DEFAULT), but using a MySQLdb cursor and passing the (2, 2, DEFAULT) in the data object.

Tim Biegeleisen Over a year ago

The concept of default value for a computed column doesn't make sense. How can you arbitrarily define what the sum of num_1 and num_2 are?

Eric Over a year ago

If I try cur.execute('INSERT INTO addition VALUES %s', [(2, 2, 'DEFAULT')]), this tries to insert the 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())])

|

Collectives™ on Stack Overflow

How to insert DEFAULT value with MySQLdb string formatting?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related