close

How to write data to Redshift that is a result of a dataframe created in Python?

Hello Guys, How are you all? Hope You all Are Fine. Today We Are Going To learn about How to write data to Redshift that is a result of a dataframe created in Python in Python. So Here I am Explain to you all the possible Methods here.

Without wasting your time, Let’s start This Article.

How to write data to Redshift that is a result of a dataframe created in Python?

  1. How to write data to Redshift that is a result of a dataframe created in Python?

    You can use to_sql to push data to a Redshift database. I've been able to do this using a connection to my database through a SQLAlchemy engine.

  2. write data to Redshift that is a result of a dataframe created in Python

    You can use to_sql to push data to a Redshift database. I've been able to do this using a connection to my database through a SQLAlchemy engine.

Method 1

You can use to_sql to push data to a Redshift database. I’ve been able to do this using a connection to my database through a SQLAlchemy engine. Just be sure to set index = False in your to_sql call. The table will be created if it doesn’t exist, and you can specify if you want you call to replace the table, append to the table, or fail if the table already exists.

from sqlalchemy import create_engine
import pandas as pd

conn = create_engine('postgresql://username:[email protected]:5439/yourdatabase')

df = pd.DataFrame([{'A': 'foo', 'B': 'green', 'C': 11},{'A':'bar', 'B':'blue', 'C': 20}])

df.to_sql('your_table', conn, index=False, if_exists='replace')

Note that you may need to pip install psycopg2 in order to connect to Redshift through SQLAlchemy.

Method 2

import pandas_redshift as pr

pr.connect_to_redshift(dbname = <dbname>,
                        host = <host>,
                        port = <port>,
                        user = <user>,
                        password = <password>)

pr.connect_to_s3(aws_access_key_id = <aws_access_key_id>,
                aws_secret_access_key = <aws_secret_access_key>,
                bucket = <bucket>,
                subdirectory = <subdirectory>)

# Write the DataFrame to S3 and then to redshift
pr.pandas_to_redshift(data_frame = data_frame,
                        redshift_table_name = 'gawronski.nba_shots_log')

Conclusion

It’s all About this issue. Hope all Methods helped you a lot. Comment below Your thoughts and your queries. Also, Comment below which Method worked for you? Thank You.

Also, Read