Writing datetime.datetime() column using pandas.to_sql()

Question

I have a single column dataframe df which has column TS where

In [1]: type(df.TS.values[0])
Out[1]: pandas.tslib.Timestamp

I convert the column to type datetime.datetime()

In [2]: import datetime
In [3]: df['TS'] = df['TS'].astype(datetime.datetime)

I want to write this dataframe to a table in a mssql 2008 database using pd.to_sql(). So, I run the following.

In [4]: coltype = {'TS': sqlalchemy.TIMESTAMP}

In [5]: df.to_sql(name='Table', con=engine, if_exists='append', index=False, dtype=coltype)
Out[5]: ValueError: df (<class 'sqlalchemy.sql.sqltypes.TIMESTAMP'>) not a string

I have also tried not converting the column to datetime.datetime() and get same error. I have looked through the sqlalchemy documentation on column types but cannot figure out what parameter is supposed to pass. Can someone help me understand what I should be passing to write the column to the db as a datetime object?

Answer 1

In my case (working with a PostgreSQL database) the data type for the timestamp column is sqlalchemy.DateTime

import sqlalchemy    

dtype = {
    "TS": sqlalchemy.DateTime
}
df.to_sql(name="Table", con=engine, if_exists="append", index=False, dtype=dtype)