Create table in MySQL based on reflected metadata from MSSQL using SQLAlchemy -
i'm trying use sqlalchemy copy table schemas between different rdbms - in example mssql mysql.
is there way take table object , copy , convert metadata different dialect?
i tried tometadata() function type info columns remains in original mssql dialect.
it works ok long column types compatible, breaks when column type doesn't exist in mysql eg. uniqueidentifier, varchar(max), etc
import sqlalchemy 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 error 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 , 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
one way or other need define equivalent type in target rdbms each of cannot mapped automatically.
one way achieve define own compiles extension:
from sqlalchemy.ext.compiler import compiles sqlalchemy.dialects.mssql import uniqueidentifier, varchar @compiles(uniqueidentifier, 'mysql') def compile_uniqueidentifier_mssql_mysql(element, compiler, **kw): """ handles mssql uniqueidentifier datatype varchar in mysql """ try: length = element.length except: length = none element.length = 64 # @note: 36 should enough, see link below # @note: since sa-0.9 string types have collation, not # compatible between databases, use default 1 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 boolean in mysql """ return compiler.visit_boolean(element, **kw) and after run code, work fine.
note: not know mysql well, , not sure best mapping of data types, looked 10.5.4 microsoft sql server type mapping information.
Comments
Post a Comment