Defining data type when using pandas.to_sql()

Question

I am writing a pandas Dataframe to a redshift database using pandas.to_sql() as so:

from sqlalchemy import create_engine
import pandas as pd

conn = create_engine('postgresql://user:[email protected]:5439/db')

dataframe.to_sql('table_name', conn, index=False, if_exists='replace', schema='schema_name')

I this dataframe I have timestamp column which looks like this 2020-03-02, but when I write it to db it gets stored as a varchar and I cant use this column to plot graphs which require timestamp format.

Is there a way to define the column type when writing data to a db with pandas.to_sql()? I do not want to create a view or another table out of the one I am writing because then I would need to drop the current table as the other one would be related to the original one and I would get an error cant drop table because other objects depend on it.

You could use sqlalchemy.types and specify a schema dictionary as dtype to the pd.to_sql function, check the accepted answer in this link - pandas to_sql all columns as nvarchar Check here for supported sqlalchemy types. — Cavin Dsouza
– Cavin Dsouza, Commented May 6, 2020 at 9:05

Umar.H · Accepted Answer · 2021-05-24 10:21:29Z

7

use sqlalchemy.types

for your time stamp column:

from sqlalchemy import types 

sql_types =  {'date' : types.TIMESTAMP(0)}

then change your dataframe.to_sql call to include the following arguments.

dataframe.to_sql('table_name', conn, index=False, 
                if_exists='replace', schema='schema_name',
                dtype=sql_types)

edited May 24, 2021 at 10:21

answered May 6, 2020 at 9:13

Umar.H

23.1k7 gold badges50 silver badges94 bronze badges

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

2 Comments

Jonas Palačionis Over a year ago

Hm, for some reason the datatype is still a varchar.

Umar.H Over a year ago

@JonasPalačionis can you open the table to see the created datatypes?

Collectives™ on Stack Overflow

Defining data type when using pandas.to_sql()

1 Answer 1

use sqlalchemy.types

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

use sqlalchemy.types

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related