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

It is not needed to make a connection object with engine.connect(), you should just pass the engine to read_sql. Furthermore, the function is available in toplevel pandas, so no need to import psql for that. — joris
– joris, Commented Jun 12, 2014 at 18:28

joris · Accepted Answer · 2014-10-27 20:00:25Z

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.

This worked for me, but I had to specify 'schema' kwarg in meta instantiation. I wrote my answer accordingly.

Mauro Bianchi · Accepted Answer · 2014-06-13 11:44:09Z

Solved, thanks to joris answer. Code was also improved thanks to joris comment, by passing around sqlalchemy engine instead of connection objects.

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

Collectives™ on Stack Overflow

Pandas writing dataframe to other postgresql schema

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related