Python package to automatically manage database schema version control when using SQLAlchemy. Databases can be easily upgraded or downgraded to any version of the schema.
Project description
Quick SchemaBot Tutorial:
Define your initial model and register as schema version 1:
>>> import sqlalchemy as SA >>> from schemabot import SchemaBot, SchemaManager >>> meta = SA.MetaData() >>> user_table = SA.Table("user", meta, ... SA.Column('id', SA.types.Integer), ... SA.Column('username', SA.types.Unicode), ... SA.Column('password', SA.types.Unicode), ... ) >>> default_admin_user = "INSERT INTO user (id, username, password) VALUES (1, 'admin', 'admin')" >>> schema_version_1_upgrade = [user_table, default_admin_user] >>> schema_version_1_downgrade = [user_table] >>> schema_mgr = SchemaManager() >>> schema_mgr.register(1, upgrade=schema_version_1_upgrade, downgrade=schema_version_1_downgrade) >>> engine = SA.create_engine('sqlite:///test1.db') >>> schemabot = SchemaBot(schema_mgr, engine=engine, create_table=True) >>> (model_version, current_db_version) = schemabot.version_check() >>> print (model_version, current_db_version) (1, 0) >>> if model_version != current_db_version: ... schemabot.schema_update() >>> print schemabot.get_current_version() 1
In another terminal (don’t close the above Python session) examine the resulting database:
$ sqlite3 test1.db Loading resources from /Users/chris/.sqliterc SQLite version 3.4.0 Enter ".help" for instructions sqlite> .tables schemabot_version user sqlite> .schema user CREATE TABLE user ( id INTEGER, username VARCHAR, password VARCHAR ); sqlite> SELECT * FROM schemabot_version; current_version --------------- 1 sqlite> SELECT * FROM user; id username password ---------- ---------- ---------- 1 admin admin
Return to the existing Python session, define schema version 2 and upgrade:
>>> address_table = SA.Table('address', meta, ... SA.Column('id', SA.types.Integer), ... SA.Column('user_id', SA.types.Integer, SA.ForeignKey('user.id')), ... SA.Column('street', SA.types.Unicode), ... SA.Column('city', SA.types.Unicode), ... ) >>> schema_version_2 = [address_table] >>> schema_mgr.register(2, upgrade=schema_version_2, downgrade=schema_version_2) >>> (model_version, current_db_version) = schemabot.version_check() >>> print (model_version, current_db_version) (2, 1) >>> if model_version != current_db_version: ... schemabot.schema_update() >>> print schemabot.get_current_version() 2
In another terminal, examine the database:
$ sqlite3 test1.db Loading resources from /Users/chris/.sqliterc SQLite version 3.4.0 Enter ".help" for instructions sqlite> .tables address schemabot_version user sqlite> .schema address CREATE TABLE address ( id INTEGER, user_id INTEGER, street VARCHAR, city VARCHAR, FOREIGN KEY(user_id) REFERENCES user (id) ); sqlite> SELECT * FROM schemabot_version; current_version --------------- 2
Return to the existing Python session. Let’s downgrade the schema back to the initial state (version 0). We will enable SQLAlchemy statement logging (echo) so we can see the action as it happens:
>>> engine.echo = True >>> schemabot.schema_update(version=0) 2009-05-27 13:46:08,690 INFO sqlalchemy.engine.base.Engine.0x...9a10 SELECT schemabot_version.current_version FROM schemabot_version 2009-05-27 13:46:08,691 INFO sqlalchemy.engine.base.Engine.0x...9a10 [] 2009-05-27 13:46:08,691 INFO sqlalchemy.engine.base.Engine.0x...9a10 BEGIN 2009-05-27 13:46:08,693 INFO sqlalchemy.engine.base.Engine.0x...9a10 DROP TABLE address 2009-05-27 13:46:08,693 INFO sqlalchemy.engine.base.Engine.0x...9a10 () 2009-05-27 13:46:08,696 INFO sqlalchemy.engine.base.Engine.0x...9a10 DROP TABLE user 2009-05-27 13:46:08,697 INFO sqlalchemy.engine.base.Engine.0x...9a10 () 2009-05-27 13:46:08,699 INFO sqlalchemy.engine.base.Engine.0x...9a10 UPDATE schemabot_version SET current_version=? 2009-05-27 13:46:08,700 INFO sqlalchemy.engine.base.Engine.0x...9a10 [0] 2009-05-27 13:46:08,701 INFO sqlalchemy.engine.base.Engine.0x...9a10 COMMIT >>> print schemabot.get_current_version() 2009-05-27 13:47:06,115 INFO sqlalchemy.engine.base.Engine.0x...9a10 SELECT schemabot_version.current_version FROM schemabot_version 2009-05-27 13:47:06,115 INFO sqlalchemy.engine.base.Engine.0x...9a10 [] 0
Swapping back to look at the database directly:
$ sqlite3 test1.db Loading resources from /Users/chris/.sqliterc SQLite version 3.4.0 Enter ".help" for instructions sqlite> .tables schemabot_version sqlite> SELECT * FROM schemabot_version; current_version --------------- 0
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.