pandas 熊猫将数据帧写入其他 postgresql 模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24189150/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pandas writing dataframe to other postgresql schema
提问by Mauro Bianchi
I am trying to write a pandas DataFrame to a PostgreSQL database, using a schema-qualified table.
我正在尝试使用模式限定表将 Pandas DataFrame 写入 PostgreSQL 数据库。
I use the following code:
我使用以下代码：
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()
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 在名为“a_schema.test”的表中写入模式“public”，而不是写入“a_schema”模式中的“test”表中。
How can I instruct pandas to use a schema different than public?
如何指示 Pandas 使用不同于 public 的模式？
Thanks
谢谢
回答by joris
Update: starting from pandas 0.15, writing to different schema's is supported. Then you will be able to use the schemakeyword argument:
更新：从 pandas 0.15 开始，支持写入不同的模式。然后您将能够使用schema关键字参数：
df.to_sql('test', engine, schema='a_schema')
Writing to different schema's is not yet supported at the moment with the read_sqland to_sqlfunctions (but an enhancement request has already been filed: https://github.com/pydata/pandas/issues/7441).
目前尚不支持使用read_sql和to_sql函数写入不同的模式（但已提交增强请求：https: //github.com/pydata/pandas/issues/7441）。
However, you can get around for now using the object interface with PandasSQLAlchemyand providing a custom MetaDataobject:
但是，您现在可以使用对象接口PandasSQLAlchemy并提供自定义MetaData对象：
meta = sqlalchemy.MetaData(engine, schema='a_schema')
meta.reflect()
pdsql = pd.io.sql.PandasSQLAlchemy(engine, meta=meta)
pdsql.to_sql(df, 'test')
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 中仍然会发生变化，但这是你可以为 pandas 0.14 做的方式。
Update: PandasSQLAlchemyis renamed to SQLDatabasein pandas 0.15.
更新：在Pandas 0.15 中PandasSQLAlchemy重命名为SQLDatabase。
回答by Mauro Bianchi
Solved, thanks to joris answer. Code was also improved thanks to joris comment, by passing around sqlalchemy engine instead of connection objects.
已解决，感谢 joris 的回答。由于 joris 注释，通过传递 sqlalchemy 引擎而不是连接对象，代码也得到了改进。
import pandas as pd
from sqlalchemy import create_engine, MetaData
engine = create_engine(r'postgresql://some:user@host/db')
meta = sqlalchemy.MetaData(engine, schema='a_schema')
meta.reflect(engine, schema='a_schema')
pdsql = pd.io.sql.PandasSQLAlchemy(engine, meta=meta)
df = pd.read_sql("SELECT * FROM xxx", con=engine)
pdsql.to_sql(df, 'test')
