CODEWITHSUNDEEP
pythonmysqldateselect
hd.
hd.

Reputation: 18336

Zero date in mysql select using python

I have a python script which selects some rows from a table and insert them into another table. One field has type of date and there is a problem with its data when its value is '0000-00-00'. python converts this value to None and so gives an error while inserting it into the second table.

How can I solve this problem? Why python converts that value to None?

Thank you in advance.

Upvotes: 1

Views: 1066

Answers (1)

zvone
zvone

Reputation: 19372

This is actually a None value in the data base, in a way. MySQL treats '0000-00-00' specially. From MySQL documentation:

MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE mode.

It seems that Python's MySQL library is trying to be nice to you and converts this to None.

When writing, it cannot guess that you wanted '0000-00-00' and uses NULL instead. You should convert it yourself. For example, this might work:

if value_read_from_one_table is not None:
    value_written_to_the_other_table = value_read_from_one_table
else:
    value_written_to_the_other_table = '0000-00-00'

Upvotes: 1

Related Questions