Ultimately you have to modify the reflected tables or rewrite them using data types that are appropriate to the target database. Reflection is in fact going to produce the most specific type possible, so in this case you're actually getting sqlalchemy.dialects.mysql.VARCHAR objects and such back which contain additional arguments. For most types, when the PG dialect receives them it will just interpret a mysql.VARCHAR as a postgresql.VARCHAR, ignoring the MySQL-specific arguments. For a DATETIME though there is no actual DATETIME on PG.
So this would be a matter of changing the "type" on all the Column() objects to be types.DateTime(), the "generic" datetime type, on those columns that are types.DATETIME. You should be able to reassign it, i.e. somecolumn.type = DateTime(), and it will work (just instantiate the type object). There's a vague plan at some point to allow this behavior to be configurable with reflection, i.e. either reflect the most specific types possible, or reflect "generic" types when possible. Currently though it just works the one way (and in 0.5 and earlier, worked the other way,which was changed since it loses information). On Jan 18, 2011, at 4:07 PM, Catherine Devlin wrote: > I'm trying to build a MySQL->PostgreSQL migration tool that reflects tables > from MySQL, then creates them in pg. > > So far so good, except that when SQLAlchemy reflects a MySQL table with a > DATETIME column, it reflects it as a sqlalchemy.types.DATETIME, then tries to > create a DATETIME in PostgreSQL. I get > > LINE 7: "SFDC_LAST_UPDATED" DATETIME DEFAULT '0000-00-00 00:00:00' ... > > sqlalchemy.exc.ProgrammingError: (ProgrammingError) type "datetime" does not > exist > > I haven't been able to come up with a way around this... either change the > column's type after reflection to DateTime (how?), or command sqlalchemy to > reflect them as sqlalchemy.DateTime in the first place (how?), or... I don't > know. I can't hard-code the column definitions b/c I want the tool to adapt > when the original (MySQL) database is changed. > > Can anybody help? > > Thanks very much! > -- > - Catherine > http://catherinedevlin.blogspot.com > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
