How do I change column type on SQLAlchemy declarative model dynamically?

Question

I am running mysql in production but would like to run a simple tests in a sqlite in memory db.

The legacy mysql db has tables with columns that are mysql specific types, Which are declared in declarative models (subclassing declarative_base). I would like to run some simple tests without going to mysql and so would need to swap out the columns of the model.

How do I do this? I've tried writing a patcher/unpatcher to swap out table in my model, but when I run some tests, I get

OperationalError: (OperationalError) near ")": syntax error u'\nCREATE TABLE my_table (\n)\n\n' ()

Which makes my think that I am not patching the columns properly.

Does anyone know how I can do this? What am I doing wrong?

Currently, I create new columns and attach brand new Table object to __table__ and save the old table.

The DB is created, create_all() is and convert_columns is run in setUp. drop_all() and revert_columns is run during tearDown in my tests

mysql_sqlite_mapping = {INTEGER: Integer,
                        MEDIUMINT: Integer,
                        TEXT: text}    

def convert_columns(self, my_class, mapping):
    for column in my_class.__table__.columns:
        if type(column.type) in mapping:
            replacement_col = Column(column.name,
                                     mapping[type(column.type)],
                                     primary_key=column.primary_key,
                                     nullable=column.nullable,
                                     key=column.key,
                                     unique=column.unique)

            converted_columns.append(replacement_col)

    self.registry[my_class] = my_class.__table__

    my_class.__table__.metadata.remove(my_class.__table__)
    my_class.__table__ = Table(my_class.__table__.name,
                               my_class.__table__.metadata)

    for column in converted_columns:
        my_class.__table__.append_column(column)

    return my_class

def revert_columns(self, my_class):
    saved_table = self.registry[my_class]

    metadata = my_class.__table__.metadata
    my_class.__table__.metadata.remove(my_class.__table__)

    model_class.__table__ = Table(saved_table.name,
                                  metadata)

    for column in saved_table.columns:
        column.table = None
        my_class.__table__.append_column(column)

    self.registry.pop(my_class)

Answer 1

I hope the below might help you with a better way of dealing with these kind of modifications. But your error is weird as it has no columns in the CREATE TABLE clause, so I do not think this is a direct answer/solution to your question.

Anyways, instead of completely replacing the tables and columns, why don't you try to use the Custom SQL Constructs and Compilation Extension / Dialect-specific compilation rules. See the sample code that should generate a proper SQL statement for sqlite without changing the table definitions:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.dialects.mysql import MEDIUMINT, INTEGER, TEXT

@compiles(MEDIUMINT, 'sqlite')
def compile_MEDIUMINT(element, compiler, **kw):
    """ Handles mysql MEDIUMINT datatype as Integer in sqlite.  """
    return compiler.visit_integer(element, **kw)

@compiles(INTEGER, 'sqlite')
def compile_INTEGER(element, compiler, **kw):
    """ Handles mysql INTEGER datatype as Integer in sqlite.  """
    return compiler.visit_integer(element, **kw)


@compiles(TEXT, 'sqlite')
def compile_TEXT(element, compiler, **kw):
    """ Handles mysql TEXT datatype as text in sqlite.  """
    return compiler.visit_text(element, **kw)