How do you create temporary MySQL table in SQLAlchemy?

Create table in MySQL based on reflected metadata from MSSQL using SQLAlchemy

• I'm trying to use SQLAlchemy to copy table schemas between different rdbms - in this example from MSSQL to MySQL. Is there a way to take a table object and copy and convert the metadata to a different dialect? I tried the tometadata() function but the type info for the columns remains in the original mssql dialect. It works ok as long as the column types are compatible, but breaks when the column type doesn't exist in MySQL eg. uniqueidentifier, varchar(max), etc import sqlalchemy as sa # Source table details source_table_name = 'Customer' source_schema_name = 'AdventureWorksLT2008.SalesLT' db_uri_mssql = "mssql+pyodbc://{user}:{password}@{dsn}" db_uri_mysql = "mysql+mysqlconnector://{user}:{password}@{host}:{port}/{db}" source_db = db_uri_mssql.format(user=source_user, password=source_password, dsn=source_dsn) target_db = db_uri_mysql.format(user=target_user, password=target_password, \ host=target_host, db=target_db, port=target_port) source_engine = sa.create_engine(source_db, echo=False, convert_unicode=True, legacy_schema_aliasing=False) target_engine = sa.create_engine(target_db, echo=False, convert_unicode=True) source_meta = sa.MetaData(bind=source_engine) target_meta = sa.MetaData(bind=target_engine) source_table = sa.Table(source_table_name, source_meta, autoload=True, schema=source_schema_name) target_table = source_table.tometadata(target_meta, schema=None) target_table.create(target_engine, checkfirst=True) Here is the error I get: Traceback (most recent call last): File "/Users/josh/PycharmProjects/Dmigrate3/Dmigrate3.py", line 38, in target_table.create(target_engine, checkfirst=True) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 725, in create checkfirst=checkfirst) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1854, in _run_visitor conn._run_visitor(visitorcallable, element, **kwargs) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1481, in _run_visitor **kwargs).traverse_single(element) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single return meth(obj, **kw) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 764, in visit_table include_foreign_key_constraints=include_foreign_key_constraints File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute return meth(self, multiparams, params) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 962, in _execute_ddl compiled = ddl.compile(dialect=dialect) File "", line 1, in File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 494, in compile return self._compiler(dialect, bind=bind, **kw) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 26, in _compiler return dialect.ddl_compiler(dialect, self, **kw) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 190, in __init__ self.string = self.process(self.statement, **compile_kwargs) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 213, in process return obj._compiler_dispatch(self, **kwargs) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch return meth(self, **kw) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 2167, in visit_create_table (table.description, column.name, ce.args[0]) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause reraise(type(exception), exception, tb=exc_tb) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 2156, in visit_create_table and not first_pk) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 213, in process return obj._compiler_dispatch(self, **kwargs) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch return meth(self, **kw) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 2187, in visit_create_column first_pk=first_pk File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1954, in get_column_specification column.type, type_expression=column) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 261, in process return type_._compiler_dispatch(self, **kw) File "/Users/josh/Dmig3/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 79, in _compiler_dispatch raise exc.UnsupportedCompilationError(visitor, cls) sqlalchemy.exc.CompileError: (in table 'Customer', column 'rowguid'): Compiler can't render element of type

• Answer:

  One way or the other you actually need to define equivalent type in the target RDBMS for each of those that cannot be mapped automatically. One way to achieve this would be to define your own compiles extension: from sqlalchemy.ext.compiler import compiles from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER, VARCHAR @compiles(UNIQUEIDENTIFIER, 'mysql') def compile_UNIQUEIDENTIFIER_mssql_mysql(element, compiler, **kw): """ Handles mssql UNIQUEIDENTIFIER datatype as VARCHAR in MySQL """ try: length = element.length except: length = None element.length = 64 # @note: 36 should be enough, but see the link below # @note: since SA-0.9 all string types have collation, which are not # really compatible between databases, so use default one element.collation = None res = compiler.visit_VARCHAR(element, **kw) if length: element.length = length return res @compiles(BIT, 'mysql') def compile_BIT_mssql_mysql(element, compiler, **kw): """ Handles mssql BIT datatype as BOOLEAN in mysql """ return compiler.visit_BOOLEAN(element, **kw) And after that run your code, which hopefully will work just fine. Note: I do not know MySQL very well, and not sure about the best mapping of the data types, but i looked https://dev.mysql.com/doc/workbench/en/wb-migration-database-mssql-typemapping.html for the information.