如何使用Python创建的数据帧将数据写入Redshift? [英] How to write data to Redshift that is a result of a dataframe created in Python?
问题描述
我在Python中有一个数据框.我可以将此数据作为新表写入Redshift吗? 我已经成功创建了到Redshift的数据库连接，并且能够执行简单的SQL查询. 现在，我需要为其编写一个数据框.
I have a dataframe in Python. Can I write this data to Redshift as a new table? I have successfully created a db connection to Redshift and am able to execute simple sql queries. Now I need to write a dataframe to it.
推荐答案
您可以使用to_sql将数据推送到Redshift数据库.通过使用SQLAlchemy引擎与数据库的连接，我已经能够做到这一点.只要确保在to_sql调用中设置了index = False.如果该表不存在，则将创建该表，您可以指定是否要调用以替换该表，追加到该表或在该表已存在的情况下失败.
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:password@yoururl.com: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')
请注意，您可能需要 pip install psycopg2 才能通过SQLAlchemy连接到Redshift.
Note that you may need to pip install psycopg2 in order to connect to Redshift through SQLAlchemy.
这篇关于如何使用Python创建的数据帧将数据写入Redshift?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
