SQLAlchemy not finding tables, possible connection issues [duplicate]

Question

I'm trying to connect to one our our internal databases using the following code:

engine = create_engine('postgresql+psycopg2://{user}:{passw}@{host}:{port}/{db}'.format(
        user=config3.CANVAS_USERNAME,
        passw=config3.CANVAS_PWD,
        host=config3.CANVAS_BOUNCER,
        port=config3.CANVAS_PORT,
        db='cluster17dr'
    ))
metadata = MetaData()
    metadata.reflect(bind=engine)
    print(metadata.tables)

And my only result is a table called 'spatial_ref_sys', which I assume is some kind of metadata. I know that my login stuff is correct, because this works perfectly:

with ppg2.connect(
        database='cluster17dr',
        user=config3.CANVAS_USERNAME,
        password=config3.CANVAS_PWD,
        host=config3.CANVAS_BOUNCER,
        port=config3.CANVAS_PORT) as conn:
        cur = conn.cursor()
        sql = 'SELECT * FROM canvas.switchman_shards LIMIT 10'

        cur.execute(sql)

        res = cur.fetchall()
        print(res)

Any ideas as to what I'm missing in my connection using SQLAlchemy?

Try specifying metadata.reflect(..., schema="canvas")?

univerio
– univerio

2016-04-26 02:12:36 +00:00
Commented Apr 26, 2016 at 2:12 — univerio
– univerio, Commented Apr 26, 2016 at 2:12
That did it. Create an answer and I'll accept:)

flybonzai
– flybonzai

2016-04-26 03:09:00 +00:00
Commented Apr 26, 2016 at 3:09 — flybonzai
– flybonzai, Commented Apr 26, 2016 at 3:09

univerio · Accepted Answer · 2016-04-26 03:24:12Z

1

By default, if no schema name is specified, SQLAlchemy will only give you tables under the default schema. If you want to reflect tables in a schema other than the default schema (which defaults to public in PostgreSQL), you need to specify the schema keyword to .reflect():

metadata.reflect(..., schema="canvas")

answered Apr 26, 2016 at 3:24

univerio

20.6k3 gold badges73 silver badges73 bronze badges

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

Collectives™ on Stack Overflow

SQLAlchemy not finding tables, possible connection issues [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related