The MySQL documentation says the 'supported' range for dates is:
'1000-01-01' to '9999-12-31'
I am running into problems where a user accidentally typed 220-09-21 instead of 2002-09-21. The result is that MySQLdb stores the date as '2022-00-09'. Normally if a user enters a wrong date, I would not have a problem, as long as the wrong date is saved (it is a simple matter of a user error). However, in this case, the date is being mangled (the munging happends in str_to_TIME() in the MySQL sql/time.cc , which is called by Field_date::store in sql/field.cc).
The MySQL stance is that the application should validate dates instead of the database... Ideally, instead of writing this mysql specific logic into all of my apps, the MySQLdb python library/module catch the invalid date and raise some exception (TypeError or something).
Unfortunately, from my brief investigation, it appears that the MySQLdb conversion stuff is really only used on results from mysql, not with data being put into mysql. Even worse, my SQL INSERT statement, appears to be sent straight to MySQL (cursors.py __do_query() ) without any processing by MySQLdb.
Have I missed something? Is there someway to add a check for valid dates being INSERTed or UPDATEd into MySQL by MySQLdb?
thanks,
donfede
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The MySQL documentation says the 'supported' range for dates is:
'1000-01-01' to '9999-12-31'
I am running into problems where a user accidentally typed 220-09-21 instead of 2002-09-21. The result is that MySQLdb stores the date as '2022-00-09'. Normally if a user enters a wrong date, I would not have a problem, as long as the wrong date is saved (it is a simple matter of a user error). However, in this case, the date is being mangled (the munging happends in str_to_TIME() in the MySQL sql/time.cc , which is called by Field_date::store in sql/field.cc).
The MySQL stance is that the application should validate dates instead of the database... Ideally, instead of writing this mysql specific logic into all of my apps, the MySQLdb python library/module catch the invalid date and raise some exception (TypeError or something).
Unfortunately, from my brief investigation, it appears that the MySQLdb conversion stuff is really only used on results from mysql, not with data being put into mysql. Even worse, my SQL INSERT statement, appears to be sent straight to MySQL (cursors.py __do_query() ) without any processing by MySQLdb.
Have I missed something? Is there someway to add a check for valid dates being INSERTed or UPDATEd into MySQL by MySQLdb?
thanks,
donfede
Use MySQLdb.Date() on your input data, pass that to execute(). MySQLdb has no way of knowing your column types in an INSERT/UPDATE.