Closed jorisvandenbossche closed 10 years ago
Note that this is similar (the same?) as the NaN/NaT conversions (#2754, #4199, PR #4200) issues referred to in #4163
I suppose we can solve this by creating a custom Timestamp
class, subclassing from the sqlalchemy.types.DateTime
(also mentioned here https://github.com/pydata/pandas/issues/2754#issuecomment-42944154 by @jreback), with something like:
class Timestamp(sqlalchemy.types.TypeDecorator):
"""convert to/from pandas.tslib.Timestamp type """
impl = sqlalchemy.types.DateTime
def process_bind_param(self, value, dialect):
f = getattr(value,'to_datetime', None)
if f is not None:
return f()
return value
And the use this in _sqlalchemy_type
instead of DateTime
(https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L787).
I tested it with pymysql, and it works. Question remain:
@jreback There are both Timestamp.to_datetime()
and Timestamp.to_pydatetime()
methods (I used to_datetime above). Any reason to not use to_datetime
(which is slightly faster than to_pydatetime
)?
@jorisvandenbossche hmm, they are virtually the same (one is on the shadow class _Timestamp
) but it doesn't matter you can call them similary). Not really sure why they both exist.
I think Timestamp.to_datetime
(and Timedelta.to_timedelta
) are pretty clear ways of doing this.
however, if we would pick one, I think the name to_pydatetime
is more clear, and also in line with the DatetimeIndex method
ok, sure (and maybe thats why their are 2)!
See the tests I added for datetime, NaT and NaN values in PRs #7082 and #7100.
Postrgresql (psycopg2)
SQLite
read_sql_table
, and you get strings withread_sql_query
(sqlite has no native datetime format -> nothing to do about that)MySQL (pymysql / MySQLdb / mysql.connector)
Writing datetime64 columns depends on driver:
pymysql
: fails with "KeyError: < class 'pandas.tslib.Timestamp' >"MySQLdb
: worksmysql.connector
: fails with "ProgrammingError: Failed processing pyformat-parameters; Python 'timestamp' cannot be converted to a MySQL type" or with " 'MySQLConverter' object has no attribute '_timestamp_to_mysql' " (see #7936 for more details)The data are feeded to the database using
df.itertuples()
and thennp.asscalar()
, and this gives data of theTimestamp
class. And for some reason most drivers can handle this (because it is a subclass of datetime.datetime?), but pymysql and mysql.connector not.For now, all the tests are skipped for MySQL / pymysql.
Oracle / SQL Server ?
There are also some issues with NaT handling (see the tests I added for datetime, NaT and NaN values in PRs #7082 and #7100), but to track this there is also an issue specific on NaN handling (#2754)