Zero date in mysql select using python

Question

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.

I found the solution at here: stackoverflow.com/questions/15420470/… — hd.
– hd., Commented Sep 7, 2016 at 9:05

zvone · Accepted Answer · 2016-09-07 08:01:44Z

1

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'

answered Sep 7, 2016 at 8:01

zvone

19.5k5 gold badges53 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

Zero date in mysql select using python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related