My table contains default values for some fields. If I don't list these fields in my insert statement the default values are correctly inserted. But if I list them and put "None" into the parameters, I get an error:
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Programme\Python24\Lib\site-packages\MySQLdb\cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "C:\Programme\Python24\Lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1048, "Column 'eform_id' cannot be null")
Quite right, column 'eform_id' cannot be null, but column 'eform_id' has got a default value which should be inserted instead. The data I'm trying to get into my table sometimes have a value for this column, but not always, and I don't want to construct a new field list for my insert statement with every new record.
How can I tell MySQL to use the default value for a column? Or do I have to put this value explicitly into the parameter tuple?
Googling was no help (searching for "MySQL insert default").
Thanks for every hint!
Koczian
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:
If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.
If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, MySQL handles the column according to the SQL mode in effect at the time:
*
If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
*
If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For non-transactional tables, an error occurs, but if this happens for the the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It is MySQL 5.0.15, but my column has got an explicit default value (and it can't take NULL). That's why I thought I could use None as parameter and get the default value into the table.
I found something else in the third link you gave me: INSERT INTO <table> (<field list>) VALUES (..., DEFAULT, ...)
That should do it. What do I put into my parameter tuple to get "DEFAULT" in the values list of the query?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Don't include that field, or any value, in your INSERT statement. By not specifying anything for the field, you are telling MySQL to insert the default value.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
"The data I'm trying to get into my table sometimes have a value for this column, but not always, and I don't want to construct a new field list for my insert statement with every new record."
Perhaps I should have added that I've got several fields with default values and different combinations of values / no values for them in the data I try to get into the table.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Then you're hosed. You'll either have to construct a dynamic field list, or run some queries ahead of time to get the default values for the field (via SHOW CREATE, for one), and use those defaults to plug into the INSERT values.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
My table contains default values for some fields. If I don't list these fields in my insert statement the default values are correctly inserted. But if I list them and put "None" into the parameters, I get an error:
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Programme\Python24\Lib\site-packages\MySQLdb\cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "C:\Programme\Python24\Lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1048, "Column 'eform_id' cannot be null")
Quite right, column 'eform_id' cannot be null, but column 'eform_id' has got a default value which should be inserted instead. The data I'm trying to get into my table sometimes have a value for this column, but not always, and I don't want to construct a new field list for my insert statement with every new record.
How can I tell MySQL to use the default value for a column? Or do I have to put this value explicitly into the parameter tuple?
Googling was no help (searching for "MySQL insert default").
Thanks for every hint!
Koczian
What version of MySQL? The behavior of 5.0 has changed a bit to make it more standards-compliant.
http://dev.mysql.com/doc/refman/5.0/en/mysql-5-0-nutshell.html
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:
If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.
If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, MySQL handles the column according to the SQL mode in effect at the time:
It is MySQL 5.0.15, but my column has got an explicit default value (and it can't take NULL). That's why I thought I could use None as parameter and get the default value into the table.
I found something else in the third link you gave me: INSERT INTO <table> (<field list>) VALUES (..., DEFAULT, ...)
That should do it. What do I put into my parameter tuple to get "DEFAULT" in the values list of the query?
Don't include that field, or any value, in your INSERT statement. By not specifying anything for the field, you are telling MySQL to insert the default value.
That works, I know, but I repeat my first post:
"The data I'm trying to get into my table sometimes have a value for this column, but not always, and I don't want to construct a new field list for my insert statement with every new record."
Perhaps I should have added that I've got several fields with default values and different combinations of values / no values for them in the data I try to get into the table.
Then you're hosed. You'll either have to construct a dynamic field list, or run some queries ahead of time to get the default values for the field (via SHOW CREATE, for one), and use those defaults to plug into the INSERT values.