Db2 with SQLAlchemy, how to specify default schema

Question

I'm trying to map an existing DB2 database to new python ORM objects. I wrote a very simple mapper class:

class Storage(Base):

    __tablename__ = 'T_RES_STORAGE_SUBSYSTEM'        

    id = Column(Integer,primary_key=True,name='SUBSYSTEM_ID')
    name = Column(String(255),name='NAME')
    namealias = Column(String(256),name='NAME_ALIAS')

But when I try to map it, by executing a query it puts the DB2ADMIN.tablename in front of every query, which of course lead to errors. If I execute the query manually by prepending TPC.tablename to it, then everything works without issues.

How can I specify in a table definition which schema to use?

What you call "tablespace" is actually a schema. I edited the question accordingly. — mustaccio, Feb 20 '14 at 13:26
Found the solution after your correction :D Thank you mister — Martino Dino, Feb 20 '14 at 13:32

score 4 · Answer 1 · answered Feb 20 '14 at 13:36

4

Ok so after the help of mustaccio, I found out that in the table_args you have to add schema:

class Storage(Base):

    __tablename__ = 'T_RES_STORAGE_SUBSYSTEM'
    __table_args__ = {'schema' : 'TPC'}

    id = Column(Integer,primary_key=True,name='SUBSYSTEM_ID')
    name = Column(String(255),name='NAME')
    namealias = Column(String(256),name='NAME_ALIAS')

answered Feb 20 '14 at 13:36

Martino Dino

585
6
14

Just what I was looking for. Any good ideas for re-using the object across multiple schemas? (working on a system that has duplicate tables across schemas -> customer1schema.infotable, customer2schema.infotable, ...) – Justin Nov 18 '15 at 21:35
Found this to change schemas - http://stackoverflow.com/questions/29595161/sqlalchemy-dynamic-schema-on-entity-at-runtime – Justin Nov 18 '15 at 21:54

Db2 with SQLAlchemy, how to specify default schema

1 Answers1