mysql datetime column containing the value "0000-00-00 00:00:00" is retrieved incorrectly as None

[vitaly numenta]

platform = mac os x yosemtite

sqlalchemy version = '0.9.4'

mysql version = 5.6.23

table definition:

CREATE TABLE `twitter_tweets` (

  `uid` varchar(40) COLLATE utf8_unicode_ci NOT NULL,

  `created_at` datetime NOT NULL,

  `retweet` tinyint(1) NOT NULL,

  `lang` varchar(10) COLLATE utf8_unicode_ci NOT NULL,

  `text` mediumtext COLLATE utf8_unicode_ci,

  `retweeted_userid` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,

  `username` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,

  `userid` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,

  `real_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,

  `retweeted_status_id` varchar(40) COLLATE utf8_unicode_ci DEFAULT '',

  `retweet_count` int(11) DEFAULT '-2',

  `retweeted_username` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',

  `retweeted_real_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',

  `in_reply_to_status_id` varchar(40) COLLATE utf8_unicode_ci DEFAULT '',

  `in_reply_to_userid` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',

  `in_reply_to_username` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',

  `contributors` text COLLATE utf8_unicode_ci,

  `stored_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`uid`),

  KEY `created_at_idx` (`created_at`),

  KEY `stored_at_idx` (`stored_at`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Insert statement:

insert  into twitter_tweets (`uid`, `created_at`) values ("3", '0000-00-00 00:00:00') on duplicate key update uid=uid;

Query in sql alchemy returns None for the value:

e.execute('select created_at from taurus_collectors.twitter_tweets where created_at="0000-00-00 00:00:00"').fetchall()

[(None,)]

For comparison, the same query in mysql shell (and Sequel Pro app) returns a non-Null value:

mysql> select created_at from taurus_collectors.twitter_tweets where created_at="0000-00-00 00:00:00";

+---------------------+

| created_at          |

+---------------------+

| 0000-00-00 00:00:00 |

+---------------------+

1 row in set (0.00 sec)

[Mike Bayer]

that's a behavior of the MySQL driver in use, not SQLAlchemy, and is also necessary - datetimes are returned as Python datetime objects which do not support non-existent timestamps.

You might try casting the column to a string where you'd probably get the plain string back.  Use "select CAST(created_at AS CHAR) .."

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[vitaly numenta]

I understand that 0000-00-00 00:00:00 would be an invalid datetime, but there is presently no way in sqlalchemy to distinguish between actual Null and the "0000-00-00 00:00:00" value that mysql substitutes when it gets an invalid datetime

[vitaly numenta]

Thank you Michael, the CAST works as you suggested.

Best,

Vitaly