sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown database "mydb"

Question

I have a problem to use flask_alchemy for my unit test function

In the production environment I use a postgresql database

"SQLALCHEMY_DATABASE_URI": "postgresql://login:passwd@dburl:1234/mydatabase",

To work with the postgresql schema, in my entity definition i declare a _table_args to specify a schema

    class MyTable(Base):
        __tablename__ = 'my_tablename'
        __table_args__ = {'schema': 'mydbschema'}
    
        my_id = Column('my_id', Date, primary_key=True)

....

But in my unittest i would like to use a memory database

"SQLALCHEMY_DATABASE_URI": "sqlite://",

When i run my function i have this error :

E   sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown database "mydbschema"

Somebody knows a workarround in this case ?

Answer 1

I found a workaround ...

class MyTable(Base):
        __tablename__ = 'my_tablename'
        __table_args__ = {'schema': 'mydbschema'}
    
        my_id = Column('my_id', Date, primary_key=True) if os.environ.get('TESTVAR') is None else {}

....

Answer 2

I know that OP found a workaround (though, I'm not sure how it works), but for those who still searching for proper solution - you need to use ATTACH DATABASE statement in order to select specific schema, for example if you are using pytest you can use following fixture to create test's setup:

from sqlalchemy import create_engine

@pytest.fixture
def setup_db():
    engine = create_engine('sqlite:///:memory:')
    with engine.connect() as conn:
        conn.execute('ATTACH DATABASE \':memory:\' AS mydbschema;')
        yield conn

Of course, you can define fixture scope by your needs.

Similarly, you can use DETACH DATABASE statement to detach and dissociate a named database from a database connection (if necessary), which will destroy DB in the case of in-memory databases.