pandas 将数据框写入其他PostgreSQL模式 [英] Pandas writing dataframe to other postgresql schema

问题描述

我正在尝试将pandas DataFrame写入PostgreSQL数据库， 使用模式限定的表.

I am trying to write a pandas DataFrame to a PostgreSQL database, using a schema-qualified table.

我使用以下代码:

import pandas.io.sql as psql
from sqlalchemy import create_engine

engine = create_engine(r'postgresql://some:user@host/db')

c = engine.connect()
conn = c.connection

df = psql.read_sql("SELECT * FROM xxx", con=conn)    
df.to_sql('a_schema.test', engine)

conn.close()

发生的情况是，大熊猫在模式"public"中写入名为"a_schema.test"的表， 而不是在"a_schema"架构中的测试"表中编写.

What happens is that pandas writes in schema "public", in a table named 'a_schema.test', instead of writing in the "test" table in the "a_schema" schema.

如何指示熊猫使用与公共模式不同的模式?

How can I instruct pandas to use a schema different than public?

谢谢

推荐答案

更新:支持从熊猫0.15开始，写入不同的模式.然后，您将可以使用schema关键字参数:

Update: starting from pandas 0.15, writing to different schema's is supported. Then you will be able to use the schema keyword argument:

df.to_sql('test', engine, schema='a_schema')

---

read_sql和to_sql函数目前尚不支持写入不同的架构(但是已经提交了增强请求:

---

Writing to different schema's is not yet supported at the moment with the read_sql and to_sql functions (but an enhancement request has already been filed: https://github.com/pydata/pandas/issues/7441).

但是，您现在可以将对象接口与PandasSQLAlchemy一起使用，并提供自定义的MetaData对象:

However, you can get around for now using the object interface with PandasSQLAlchemy and providing a custom MetaData object:

meta = sqlalchemy.MetaData(engine, schema='a_schema')
meta.reflect()
pdsql = pd.io.sql.PandasSQLAlchemy(engine, meta=meta)
pdsql.to_sql(df, 'test')

当心！该接口(PandasSQLAlchemy)尚未真正公开，并且在下一版的Pandas中仍将进行更改，但这是您可以在熊猫0.14中实现的方法.

Beware! This interface (PandasSQLAlchemy) is not yet really public and will still undergo changes in the next version of pandas, but this is how you can do it for pandas 0.14.

更新:在熊猫0.15中，PandasSQLAlchemy重命名为SQLDatabase.

Update: PandasSQLAlchemy is renamed to SQLDatabase in pandas 0.15.

这篇关于 pandas 将数据框写入其他PostgreSQL模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！