diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2018-03-28 19:12:41 -0400 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2018-03-28 19:12:41 -0400 |
commit | a7164ef76729aa9306f9c7e2636448a588f35ddb (patch) | |
tree | 9c3c5babe070986cb229da61d2ad87e9869269be /docs/build/cookbook.rst | |
parent | bd1b3ac3a5b79deb4c908501f3e2c2e2a7e18c49 (diff) | |
download | alembic-a7164ef76729aa9306f9c7e2636448a588f35ddb.tar.gz |
Add recipe for generating Python code for existing tables
Change-Id: Ia54f1a383d3b9ee32963f33276025bf8ae11d003
Diffstat (limited to 'docs/build/cookbook.rst')
-rw-r--r-- | docs/build/cookbook.rst | 74 |
1 files changed, 74 insertions, 0 deletions
diff --git a/docs/build/cookbook.rst b/docs/build/cookbook.rst index c4a4eef..ffa05a3 100644 --- a/docs/build/cookbook.rst +++ b/docs/build/cookbook.rst @@ -973,3 +973,77 @@ populated with defaults from the ``[DEFAULT]`` section. The above approach can be automated by creating a custom front-end to the Alembic commandline as well. +Print Python Code to Generate Particular Database Tables +======================================================== + +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):: + + 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 :class:`.ops.UpgradeOps`, :class:`.ops.CreateTableOp`, and +:class:`.ops.CreateIndexOp`, we create a migration file structure, +using :class:`.Table` objects that we get from SQLAlchemy reflection. +The structure is passed to :func:`.autogenerate.render_python_code` to +produce the Python code for a migration file:: + + 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 ### + |