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()

pandas 在模式public"中写入名为a_schema.test"的表中，而不是写入a_schema"模式中的test"表.

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.

如何指示 Pandas 使用不同于 public 的模式?

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

谢谢

推荐答案

更新:从 pandas 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 函数写入不同的模式(但已经提交了增强请求:https://github.com/pydata/pandas/issues/7441).

---

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 版本中进行更改，但您可以在 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.

更新:PandasSQLAlchemy 在 pandas 0.15 中更名为 SQLDatabase.

Update: PandasSQLAlchemy is renamed to SQLDatabase in pandas 0.15.

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