pythonpostgresqlsqlalchemy

Why am I getting a "relation does not exist" error for existing table in non-default schema?


I have the following code which throws the following error

engine = create_engine('postgresql+psycopg2:....', convert_unicode=True)

metadata = sqlalchemy.MetaData()
table = sqlalchemy.Table('omni.all_order', metadata,
    sqlalchemy.Column('o_id', sqlalchemy.Integer),
    sqlalchemy.Column('order', sqlalchemy.String),
    
)

ins = table.insert().values(all_rows)
engine.execute(ins)

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "omni.all_order" does not exist

But the following two codes work fine

engine = create_engine('postgresql+psycopg2:....', convert_unicode=True)
    
result = engine.execute("SELECT * from omni.all_order ")
rows = result.fetchall()
    print(rows)

--

 engine = create_engine('postgresql+psycopg2:....', convert_unicode=True)
    
    engine.execute("INSERT INTO omni.all_order (o_id) VALUES (1) ")

Creating another table first in the same schema (omni) throws the same error

engine = create_engine('postgresql+psycopg2:....', convert_unicode=True)

    result = engine.execute("CREATE TABLE omni.all_order_s(o_id INT, order VARCHAR(80))")
    
    metadata = sqlalchemy.MetaData()
    table = sqlalchemy.Table('omni.all_order_s', metadata,
        sqlalchemy.Column('o_id', sqlalchemy.Integer),
        sqlalchemy.Column('order', sqlalchemy.String),
    
    )
    
    ins = table.insert().values(all_rows)
    engine.execute(ins)

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "omni.all_order_s" does not exist

but creating it outside of the schema works fine

engine = create_engine('postgresql+psycopg2:....', convert_unicode=True)
    
    result = engine.execute("CREATE TABLE all_order_s(o_id INT, order VARCHAR(80))")
    
        metadata = sqlalchemy.MetaData()
        table = sqlalchemy.Table('all_order_s', metadata,
            sqlalchemy.Column('o_id', sqlalchemy.Integer),
            sqlalchemy.Column('order', sqlalchemy.String),
        
        )
        
        ins = table.insert().values(all_rows)
        engine.execute(ins)

Any ideas why this is?


Solution

  • Pass the table's schema using the schema= keyword argument instead of including it in the table's name:

    table = sqlalchemy.Table('all_order', metadata,
        sqlalchemy.Column('o_id', sqlalchemy.Integer),
        sqlalchemy.Column('order', sqlalchemy.String),
        schema='omni',
    )
    

    Currently it is quoted as a whole.