I am trying to create a database API and I am using Python 2.7 and SQLAlchemy.
The API has to support multiple database platforms including MSSQL, MySQL, PostgreSQL, and SQLite.
The problem with making it multi-platform, is that each database has a slightly different understanding of the word 'schema'. Basically, if I am connected to a MSSQL database I want to include or set the schema, but if I am connecting to a SQLite database I do not.
Another problem is that I am using the declarative_base within SQLAlchemy, and the classes have been created, with the schema specification, before I know what type of database I am connecting to. (The user will connect to the database after the script has started running.) So I don't know what type of schema to assign the table.
Here is a simplified example of my code:
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Methods(Base):
__tablename__ = 'methods'
__table_args__ = {u'schema': 'sname'}
MethodID = Column('methodid', Integer, primary_key=True, nullable=False)
MethodTypeCV = Column('methodtypecv', nullable=False, index=True)
MethodCode = Column('methodcode', String(50), nullable=False)
MethodName = Column('methodname', String(255), nullable=False)
MethodDescription = Column('methoddescription', String(500))
MethodLink = Column('methodlink', String(255))
OrganizationID = Column('organizationid', Integer)
I have tried a lot of things, including, setting the schema after the class is created but that doesn't work.I have also tried to use reflection but I can't figure out how to get it to work with the declarative base.
Is there a way to use reflection or a global variable or something to be able to change the schema at runtime, once I have a connection to the database and know what the schema will be?
