CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
flybonzai
flybonzai

Reputation: 3931

SQLAlchemy not finding tables, possible connection issues

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?

Upvotes: 0

Views: 1239

Answers (1)

univerio
univerio

Reputation: 20508

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

Upvotes: 1

Related Questions