sqlalchemy NoSuchTableError when table exists in database

Question

I am using PostgreSQL+Psycopg2, SQLAlchemy. I've already created my database "new_db" using pgAdminIII tool and a new schema in it as "new_db_schema". Under this schema I've all the tables I need. My code looks like this.

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, Boolean

engine_text = 'postgresql+psycopg2://root:12345@localhost:5432/db_name'
database_engine = create_engine(engine_text, echo = True)

Base = declarative_base(database_engine)

class Some_table(Base):
    __tablename__ = 'some_table' # This table exist in database.
    __table_args__ = {'autoload':True}

    col1 = Column(String, primary_key=True)
    col2 = Column(Boolean)

    def __init__(self, col1_name, col2_name):
        self.col1 = col1_name
        self.col2 = col2_name

if __name__ == "__main__":
    a = Some_table('blah', 'blah')

Now when i try to run the following code, I get sqlalchemy.exc.NoSuchTableError: some_table.

Since I already have the database setup with all the tables, I would like to autoload while creating classes. Am I missing something here? I need to write such classes for all the tables present in the database. Any help would be greatly appreciated.

Thanks

Answer 1

You can either:

• Use schema-qualified table names: __tablename__ = 'new_db_schema.some_table'. You have to use them everywhere: in string arguments to ForeignKey etc.
• Alter SEARCH_PATH in the database: SET search_path TO new_db_schema;. This SQL command has session scope, so you have to issue it at the start of every connection using SQLAlchemy event system.

Like this:

from sqlalchemy import event

def init_search_path(connection, conn_record):
    cursor = connection.cursor()
    try:
        cursor.execute('SET search_path TO new_db_schema;')
    finally:
        cursor.close()

engine = create_engine('postgresql+psycopg2://...')
event.listen(engine, 'connect', init_search_path)