3

I want to update a row in mysql database and use the default value of the column. i tried to make this 

session.query(TableName).update({'column1': None,
                            'column2': None,
                            'column3': None}, False)

but it didn't work. any ideas?

Mohamed Abd El Raouf
  • 918
  • 1
  • 9
  • 21

2 Answers2

2

If you have server defaults on that fields, following should do the job:

session.query(TableName).update({'column1': sa.text('default'),
                                 'column2': sa.text('default'),
                                 'column3': sa.text('default')}, False)
maciek
  • 3,198
  • 2
  • 26
  • 33
0

To set a value to the default, the column has to have been set to NOT NULL (nullable=False in SQLAlchemy) and have a DEFAULT. This could be done at the DB level or when defining the structure in the DB.

In the update query, if you set that value to NULL (or None in Python), then the it will get the default set on it. (This is true for MySQL, not sure if SQLAlchemy tries to intervene.)

And to update existing columns to NOT NULL and give them DEFAULTs, see these SO questions.

Community
  • 1
  • 1
aneroid
  • 12,983
  • 3
  • 36
  • 66
  • Is it possible achieve this without requiring the column to be NOT NULL? – jminardi Oct 01 '21 at 10:33
  • @jminardi No. If the column is Nullable then the default never gets used. In that case, you'd have to explicitly update rows to that default value, like in the other answer. – aneroid Oct 01 '21 at 11:09