除了 PostgreSQL 上的“public”之外，Pandas to_sql 无法写入模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25173887/
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 to_sql can't write to schema besides 'public' on PostgreSQL
提问by 2daaa
I'm trying to write the contents of a data frame to a table in a schema besides the 'public' schema. I followed the pattern described in Pandas writing dataframe to other postgresql schema:
我正在尝试将数据框的内容写入“公共”模式之外的模式中的表。我遵循Pandas 将数据帧写入其他 postgresql 模式中描述的模式：
meta = sqlalchemy.MetaData()
engine = create_engine('postgresql://some:user@host/db')
meta = sqlalchemy.MetaData(engine, schema='schema')
meta.reflect(engine, schema='schema')
pdsql = pandas.io.sql.PandasSQLAlchemy(engine, meta=meta)
But when I try to write to the table:
但是当我尝试写入表格时：
pdsql.to_sql(df, 'table', if_exists='append')
I get the following error:
我收到以下错误：
InvalidRequestError: Table 'schema.table' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object.
I also tried adding extend_existing=Trueto the reflectcall, but that doesn't seem to make a difference.
我也尝试添加extend_existing=True到reflect电话中，但这似乎没有什么区别。
How can I get pandas to write to this table?
我怎样才能让Pandas写入这张表？
回答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')
As I said in the linked question, 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, I described a workaround using the object interface. But what I described there only works for adding the table once, not for replacing and/or appending the table. So if you just want to add, first delete the existing table and then write again.
但是，我描述了使用对象接口的解决方法。但是我在那里描述的内容仅适用于添加表格一次，不适用于替换和/或附加表格。所以如果只是想添加的话，先把已有的表删掉，再写一遍。
If you want to append to the table, below is a little bit more hacky workaround. First redefine has_tableand get_table:
如果你想附加到表格中，下面是一个有点棘手的解决方法。首先重新定义has_table和get_table：
def has_table(self, name):
return self.engine.has_table(name, schema=self.meta.schema)
def get_table(self, table_name):
if self.meta.schema:
table_name = self.meta.schema + '.' + table_name
return self.meta.tables.get(table_name)
pd.io.sql.PandasSQLAlchemy.has_table = has_table
pd.io.sql.PandasSQLAlchemy.get_table = get_table
Then create the PandasSQLAlchemyobject as you did, and write the data:
然后PandasSQLAlchemy像你一样创建对象，并写入数据：
meta = sqlalchemy.MetaData(engine, schema='schema')
meta.reflect()
pdsql = pd.io.sql.PandasSQLAlchemy(engine, meta=meta)
pdsql.to_sql(df, 'table', if_exists='append')
This is obviously not the good way to do, but we are working to provide a better API for 0.15. If you want to help, pitch in at https://github.com/pydata/pandas/issues/7441.
这显然不是一个好方法，但我们正在努力为 0.15 提供更好的 API。如果您想提供帮助，请访问https://github.com/pydata/pandas/issues/7441。
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(.1).
谨防！这个接口 ( PandasSQLAlchemy) 还不是真正公开的，在下一版的 pandas 中仍然会发生变化，但这是你可以为 pandas 0.14(.1) 做的事情。
Update: PandasSQLAlchemyis renamed to SQLDatabasein pandas 0.15.
更新：在Pandas 0.15 中PandasSQLAlchemy重命名为SQLDatabase。
