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?

Does the table already exist and, if so, is the TS column of String type? — JoeCondron
– JoeCondron, Commented Oct 8, 2015 at 19:28
@JoeCondron Yes the dataframe 'already exists', but if it helps, it is the result object of running a query through pd.read_sql(). Also, no the entries in df.TS have type pandas.tslib.Timestamp, which I then convert to datetime.datetime. However, running df.dtypes returns datetime64[ns]. — cpsempek
– cpsempek, Commented Oct 8, 2015 at 20:50
You shouldn't need to specify the dtypes yourself (datetime values are supported). Can you try just df.to_sql(name='Table', con=engine, if_exists='append', index=False)? — joris
– joris, Commented Oct 8, 2015 at 21:33
@joris Yes, you are right, I had done that anyway but for some reason thought it would insert them all as TEXT. I should have checked. Thank you. — cpsempek
– cpsempek, Commented Oct 8, 2015 at 22:15

lux7 · Accepted Answer · 2022-01-13 07:54:06Z

4

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)

answered Jan 13, 2022 at 7:54

lux7

2,2604 gold badges24 silver badges41 bronze badges

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

Comments

Gonçalo Peres · Accepted Answer · 2021-05-22 10:43:47Z

1

As @joris said

You shouldn't need to specify the dtypes yourself (datetime values are supported).

So, removing dtype=coltype should solve your problem

df.to_sql(name='Table', con=engine, if_exists='append', index=False)

The dtype is useful for integers with missing values.

To note:

while pandas is forced to store the data as floating point, the database supports nullable integers. When fetching the data with Python, we get back integer scalars. Source

edited May 22, 2021 at 10:43

answered May 22, 2021 at 9:26

Gonçalo Peres

13.8k5 gold badges73 silver badges95 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related