python - Pandas writing dataframe to other postgresql schema

Question

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

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.

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

Thanks

Answer 1

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

---

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

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

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.

Update: PandasSQLAlchemy is renamed to SQLDatabase in pandas 0.15.