Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas writing dataframe to other postgresql schema

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

like image 518
Mauro Bianchi Avatar asked Jun 12 '14 16:06

Mauro Bianchi


1 Answers

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.

like image 75
joris Avatar answered Sep 18 '22 16:09

joris