Print Python Code to Generate Particular Database Tables

打印 Python 代码以生成特定的数据库表

Suppose you have a database already, and want to generate some op.create_table() and other directives that you’d have in a migration file. How can we automate generating that code? Suppose the database schema looks like (assume MySQL):

假设您已经有一个数据库，并且想要生成一些 op.create_table() 和迁移文件中的其他指令。 我们如何自动生成该代码？ 假设数据库模式看起来像（假设为 MySQL）：

CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) NOT NULL,
    KEY `id` (`id`)
);

CREATE TABLE IF NOT EXISTS `user_properties` (
  `users_id` int(11) NOT NULL,
  `property_name` varchar(255) NOT NULL,
  `property_value` mediumtext NOT NULL,
  UNIQUE KEY `property_name_users_id` (`property_name`,`users_id`),
  KEY `users_id` (`users_id`),
  CONSTRAINT `user_properties_ibfk_1` FOREIGN KEY (`users_id`)
  REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Using ops.UpgradeOps, ops.CreateTableOp, and ops.CreateIndexOp, we create a migration file structure, using Table objects that we get from SQLAlchemy reflection. The structure is passed to autogenerate.render_python_code() to produce the Python code for a migration file:

使用 ops.UpgradeOps, ops.CreateTableOp 和 ops.CreateIndexOp，我们使用从 SQLAlchemy 反射获得的 Table 对象创建迁移文件结构。 该结构被传递给 autogenerate.render_python_code() 以生成迁移文件的 Python 代码：

from sqlalchemy import create_engine
from sqlalchemy import MetaData, Table
from alembic import autogenerate
from alembic.operations import ops

e = create_engine("mysql://scott:tiger@localhost/test")

with e.connect() as conn:
    m = MetaData()
    user_table = Table('users', m, autoload_with=conn)
    user_property_table = Table('user_properties', m, autoload_with=conn)

print(autogenerate.render_python_code(
    ops.UpgradeOps(
        ops=[
            ops.CreateTableOp.from_table(table) for table in m.tables.values()
        ] + [
            ops.CreateIndexOp.from_index(idx) for table in m.tables.values()
            for idx in table.indexes
        ]
    ))
)

Output:

# ### commands auto generated by Alembic - please adjust! ###
op.create_table('users',
sa.Column('id', mysql.INTEGER(display_width=11), autoincrement=False, nullable=False),
mysql_default_charset='latin1',
mysql_engine='InnoDB'
)
op.create_table('user_properties',
sa.Column('users_id', mysql.INTEGER(display_width=11), autoincrement=False, nullable=False),
sa.Column('property_name', mysql.VARCHAR(length=255), nullable=False),
sa.Column('property_value', mysql.MEDIUMTEXT(), nullable=False),
sa.ForeignKeyConstraint(['users_id'], ['users.id'], name='user_properties_ibfk_1', ondelete='CASCADE'),
mysql_comment='user properties',
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('id', 'users', ['id'], unique=False)
op.create_index('users_id', 'user_properties', ['users_id'], unique=False)
op.create_index('property_name_users_id', 'user_properties', ['property_name', 'users_id'], unique=True)
# ### end Alembic commands ###