Postgres, reflection, and search_path

[percious]

Hey guys,

I have a postgres database which requires me to "set search_path to
'my_db'" before I can get a proper table listing.

I have written a schema for this database, but what I would like to do
is compare my schema against the existing database, and make sure that
all my tables and columns jive. I have tried something like this:


from sqlalchemy import MetaData, create_engine
metadata = MetaData()
engine = create_engine('postgres://user@host/Target')
engine.execute("set search_path to 'my_db'")
metadata.bind = engine
metadata.reflect()

print metadata.tables.keys()

which never returns the tables I desire.

Does anyone have any pointers?

Thanks,
-chris

[Michael Bayer]

its likely a connection specific thing. do it on a Connection, then
send that as "bind" to metadata.reflect().

[percious]

like this?

from sqlalchemy import MetaData, create_engine
metadata = MetaData()
engine = create_engine('postgres://user@host/Target')

connect = engine.connect()
connect.execute("set search_path to 'my_db'")
metadata.reflect(bind=connect)

(does not work)

On Jun 20, 2:47 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> its likely a connection specific thing.  do it on a Connection, then  
> send that as "bind" to metadata.reflect().
>
> On Jun 20, 2008, at 4:45 PM, percious wrote:
>
>
>
> > Hey guys,
>
> > I have a postgres database which requires me to "setsearch_pathto
> > 'my_db'" before I can get a proper table listing.
>
> > I have written a schema for this database, but what I would like to do
> > is compare my schema against the existing database, and make sure that
> > all my tables and columns jive.  I have tried something like this:
>
> >    from sqlalchemy import MetaData, create_engine
> >    metadata = MetaData()
> >    engine = create_engine('postgres://user@host/Target')

> >    engine.execute("setsearch_pathto 'my_db'")

[percious]

Nevermind, that got it. Thanks mike for your ordinarily punctual
responses.

BTW, anyone have a need for a db schema comparison tool? I thought
there was one out there, but I was dubious about the source.

cheers.
-chris

On Jun 20, 3:06 pm, percious <ch...@percious.com> wrote:
> like this?
>
>     from sqlalchemy import MetaData, create_engine
>     metadata = MetaData()
>     engine = create_engine('postgres://user@host/Target')
>     connect = engine.connect()

>     connect.execute("setsearch_pathto 'my_db'")

[Michael Bayer]

On Jun 20, 2008, at 5:06 PM, percious wrote:

>
> like this?
>
> from sqlalchemy import MetaData, create_engine
> metadata = MetaData()
> engine = create_engine('postgres://user@host/Target')
> connect = engine.connect()
> connect.execute("set search_path to 'my_db'")
> metadata.reflect(bind=connect)
>
> (does not work)

that would be it. but search_path shouldnt affect anything SQLA does
regarding reflection. don't you just want to send "schema='my_db'" to
metadata.reflect() ?

[percious]

that also works. Thanks mike.

On Jun 20, 3:13 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Jun 20, 2008, at 5:06 PM, percious wrote:
>
>
>
> > like this?
>
> >    from sqlalchemy import MetaData, create_engine
> >    metadata = MetaData()
> >    engine = create_engine('postgres://user@host/Target')
> >    connect = engine.connect()

> >    connect.execute("setsearch_pathto 'my_db'")
> >    metadata.reflect(bind=connect)
>
> > (does not work)
>
> that would be it.   butsearch_pathshouldnt affect anything SQLA does  

[a...@svilendobrev.com]

On Saturday 21 June 2008 00:11:53 percious wrote:
> Nevermind, that got it. Thanks mike for your ordinarily punctual
> responses.
>
> BTW, anyone have a need for a db schema comparison tool? I thought
> there was one out there, but I was dubious about the source.

u have some?
check a thread named "Schema and database migration: how to diff?"
from around being of this month.