- changed component to schema
- changed title to have dialect-specific TypeEngines support tranferrence between backends
- assigned issue to
- changed milestone to 0.6.xx
- marked as enhancement
have dialect-specific TypeEngines support tranferrence between backends (reflection / tometadata)
Issue #1414
closed
Hi,
just tried to convert a SQL-Server-DB to SQLite using SQLAlchemy 0.5.3 / 0.5.4 and PyODBC 2.1.5 using the following code:
import sqlalchemy as sa
engine1 = sa.create_engine(connstr, encoding="utf-8", echo=True) # MSSql Server
engine2 = sa.create_engine(connstr2, encoding="utf-8", echo=True) # Sqlite db
meta1 = sa.MetaData(bind=engine1) # gives a deprecation warning (see below)
meta2 = sa.MetaData(bind=engine2)
meta1.reflect() # takes a while but works well
for table in meta1.tables.values():
table.tometadata(meta2)
# no (real) complaints so far
# but this one makes some trouble
meta2.create_all()
Deprecation Warning
C:\Python26\lib\site-packages\sqlalchemy\databases\mssql.py:1068: DeprecationWar
ning: object.__new__() takes no parameters
return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs)
Traceback thrown by create_all()
see attached file
Comments (4)
-
repo owner -
repo owner - changed title to have dialect-specific TypeEngines support tranferrence between backends (reflection / tometadata)
-
repo owner - changed milestone to 1.x.xx
-
repo owner - changed status to closed
there's really nothing to be done here, and the traceback attached does not seem to be against anything still currently present.
if we try to emit CREATE TABLE given a type object that's for the wrong database, it just treats the type as the "generic":
from sqlalchemy import * from sqlalchemy.dialects import mysql, mssql from sqlalchemy import schema m = MetaData() t = Table( 't', m, Column('x', mysql.TIME()) ) print schema.CreateTable(t).compile(dialect=mssql.dialect())
that is, it doesn't try to instantiate mssql.TIME() since we aren't starting with the base sqltypes.TIME.
For custom translations, again there are ways to rewrite the tables, including doing it within the column_reflect() event. It's not something we can support as "built in" to all types since there are so many ambiguous cases.
- Log in to comment
as yet we have never tried to support the use case of transferring types between backends. For the case here, it would be possible, although for most non-trivial cases it would not, for such types as PG's INTERVAL, UUID or MySQL's BIT type. The work involved would require TypeEngine objects that are specific to some database to specify a "fallback" type when being converted to some other engine...or perhaps this can be automated within
TypeEngine.dialect_impl()
(that may make this easier than the "2 day" severity given).there's been a major refactoring of types in 0.6 so any big changes like this are targeted there. of particular interest is the fact that 0.6 will in many cases rely upon the generic types (i.e. String, DateTime) a lot more than the DB-specific ones, which have been greatly reduced (although not for MS and MySQL, whose types have tons of DB-specific options).
as a workaround for now you might want to iterate through the columns of your reflected Table objects and change them to be "generic" types, i.e. MSString becomes String, etc.