Generic TEXT/CLOB data type for cross-database SQLAlchemy application

Question

I am working on Python web application that uses MySQL as a database backend on the development environment and Oracle 11g on the production environment. Is there a specific way to unify the SQLAlchemy text fields, so that they work both on MySQL and Oracle database? For the Oracle backend in the models I use sqlalchemy.CLOB and for MySQL sqlalchemy.TEXT.

Answer 1

According to @agronholm on #sqlalchemy one is supposed to use generic SQLAlchemy type Text (which TEXT is specialization of):

A variably sized string type.

In SQL, usually corresponds to CLOB or TEXT. Can also take Python unicode objects and encode to the database’s encoding in bind params (and the reverse for result sets.) In general, TEXT objects do not have a length; while some databases will accept a length argument here, it will be rejected by others.

Answer 2

I think that using this official documentation trick should do the trick: Overiding Type Compilation. In this case i would keep CLOB as original because it is used in production, and would apply a compilation for mysql:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import CLOB

@compiles(CLOB, "mysql")
def compile_binary_mysql(type_, compiler, **kw):
    return "TEXT"
    # @note: this will handle other parameter such as `length`
    # return compiler.visit_TEXT(type_, **kw)

Note that it is strongly advised to use the same RDBMS for production/test/development to avoid these and other issues.