def basic_auto_migrate_relational_db(app, bind): """Inspired with http://stackoverflow.com/questions/2103274/""" from sqlalchemy import Table from sqlalchemy import MetaData print('Performing auto-migration in', bind, 'database...') db.session.commit() db.reflect() db.session.commit() db.create_all(bind=bind) with app.app_context(): engine = db.get_engine(app, bind) tables = db.get_tables_for_bind(bind=bind) metadata = MetaData() metadata.engine = engine ddl = engine.dialect.ddl_compiler(engine.dialect, None) for table in tables: db_table = Table(table.name, metadata, autoload=True, autoload_with=engine) db_columns = get_column_names(db_table) columns = get_column_names(table) new_columns = columns - db_columns unused_columns = db_columns - columns existing_columns = columns.intersection(db_columns) for column_name in new_columns: column = getattr(table.c, column_name) if column.constraints: print( f'Column {column_name} skipped due to existing constraints.' ) continue print(f'Creating column: {column_name}') definition = ddl.get_column_specification(column) add_column(engine, table.name, definition) if engine.dialect.name == 'mysql': sql = f'SHOW CREATE TABLE `{table.name}`' table_definition = engine.execute(sql) columns_definitions = {} to_replace = { 'TINYINT(1)': 'BOOL', # synonymous for MySQL and SQLAlchemy 'INT(11)': 'INTEGER', 'DOUBLE': 'FLOAT(53)', ' DEFAULT NULL': '' } for definition in table_definition.first()[1].split('\n'): match = re.match( '\s*`(?P<name>.*?)` (?P<definition>[^,]*),?', definition) if match: name = match.group('name') definition_string = match.group('definition').upper() for mysql_explicit_definition, implicit_sqlalchemy in to_replace.items( ): definition_string = definition_string.replace( mysql_explicit_definition, implicit_sqlalchemy) columns_definitions[ name] = name + ' ' + definition_string columns_to_update = [] for column_name in existing_columns: column = getattr(table.c, column_name) old_definition = columns_definitions[column_name] new_definition = ddl.get_column_specification(column) if old_definition != new_definition: columns_to_update.append( [column_name, old_definition, new_definition]) if columns_to_update: print( '\nFollowing columns in `%s` table differ in definitions ' 'from those in specified in models:' % table.name) for column, old_definition, new_definition in columns_to_update: agreed = got_permission( 'Column: `%s`\n' 'Old definition: %s\n' 'New definition: %s\n' 'Update column definition?' % (column, old_definition, new_definition)) if agreed: update_column(engine, table.name, new_definition) print(f'Updated {column} column definition') else: print(f'Skipped {column} column') if unused_columns: print('\nFollowing columns in `%s` table are no longer used ' 'and can be safely removed:' % table.name) for column in unused_columns: if got_permission(f'Column: `{column}` - remove?'): drop_column(engine, table.name, column) print(f'Removed column {column}.') else: print(f'Keeping column {column}.') print('Auto-migration of', bind, 'database completed.')
def metadata(engine): metadata = MetaData() metadata.engine = engine return metadata