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

29

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.

edited Oct 27, 2014 at 20:00

answered Jun 12, 2014 at 18:25

joris

140k37 gold badges258 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mauro Bianchi Over a year ago

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

3

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

answered Jun 13, 2014 at 11:44

Mauro Bianchi

6931 gold badge9 silver badges17 bronze badges

1 Comment

Cristián Vargas Acevedo Over a year ago

this solution work form me on panda 1.3.5 !

Collectives™ on Stack Overflow

Pandas writing dataframe to other postgresql schema

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related