如何使用Python创建的数据帧将数据写入Redshift?

本文介绍了如何使用Python创建的数据帧将数据写入Redshift?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在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: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.

to_sql文档