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

Question

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?

Henry Ecker · Accepted Answer · 2021-07-20 13:47:20Z

2

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}
)

edited Jul 20, 2021 at 13:47

Henry Ecker♦

35.8k19 gold badges48 silver badges67 bronze badges

answered Jul 20, 2021 at 13:07

BrokenBad

958 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related