Pandas to_sql using wrong data type, can it be changed?

user_15124022 0
Viewed 486

I've loaded some SQL Server data into a Pandas Dataframe. Here some transformations take place. Once completed I am trying to dump the Dataframe back into SQL using the SQLAlchemy to_sql function. The destination table is automatically created by SQLAlchemy.

However, I am getting this error message...

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

This is because the Dataframe contains a number of date fields. Some of the data in these date fields are set to '0001-01-01' which obviously won't fit into a DATETIME data type, but would fit into a DATETIME2 data type.

Is there a way to force DATETIME2 to be used instead of DATETIME?

pandas dataframe sqlalchemy python
1 Answers

You can specify DATETIME2 as a dtype, as shown below.

df.to_sql(
  'Temp',
  target_engine,
  schema = 'dbo',
  if_exists = 'replace',
  chunksize = 250000,
  index = False,
  dtype={"CreatedDate": DATETIME2}
)
user_151240220
Related