[Python] Why am I getting a "relation does not exist" error for existing table in non-default...

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?

Continue reading...