Schema qualified tables with SQLAlchemy, SQLite and Postgresql?

Question

I have a Pylons project and a SQLAlchemy model that implements schema qualified tables:

class Hockey(Base):
    __tablename__ = "hockey"
    __table_args__ = {'schema':'winter'}
    hockey_id = sa.Column(sa.types.Integer, sa.Sequence('score_id_seq', optional=True), primary_key=True)
    baseball_id = sa.Column(sa.types.Integer, sa.ForeignKey('summer.baseball.baseball_id'))

This code works great with Postgresql but fails when using SQLite on table and foreign key names (due to SQLite's lack of schema support)

sqlalchemy.exc.OperationalError: (OperationalError) unknown database "winter" 'PRAGMA "winter".table_info("hockey")' ()

I'd like to continue using SQLite for dev and testing.

Is there a way of have this fail gracefully on SQLite?

Answer 1

I know this is a 10+ year old question, but I ran into the same problem recently: Postgres in production and sqlite in development.

The solution was to register an event listener for when the engine calls the "connect" method.

@sqlalchemy.event.listens_for(engine, "connect")
def connect(dbapi_connection, connection_record):
    dbapi_connection.execute('ATTACH "your_data_base_name.db" AS "schema_name"')

Using ATTACH statement only once will not work, because it affects only a single connection. This is why we need the event listener, to make the ATTACH statement over all connections.

Answer 2

I'd like to continue using SQLite for dev and testing.

Is there a way of have this fail gracefully on SQLite?

It's hard to know where to start with that kind of question. So . . .

Stop it. Just stop it.

There are some developers who don't have the luxury of developing on their target platform. Their life is a hard one--moving code (and sometimes compilers) from one environment to the other, debugging twice (sometimes having to debug remotely on the target platform), gradually coming to an awareness that the gnawing in their gut is actually the start of an ulcer.

Install PostgreSQL.

When you can use the same database environment for development, testing, and deployment, you should.

Not to mention the QA team. Why on earth are they testing stuff they're not going to ship? If you're deploying on PostgreSQL, assure the quality of your work on PostgreSQL.

Seriously.