Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dguaraglia/9352513 to your computer and use it in GitHub Desktop.
Save dguaraglia/9352513 to your computer and use it in GitHub Desktop.
# 2010-10-02 14h00
# Update created field in user_prefs table
def migrate(db):
with db.select('user_id, created', from_='users'):
all_users = db.fetchall()
for user in all_users:
with db.update('user_prefs') as obj:
obj.created = user['created']
db.where('user_id = %s', user['user_id'])
db.execute()
db.commit()

This is a minimalist Python migration management script based on Fabric and sqlwitch.

It assumes a migrations/ directory under the project's root path. The fabfile.py attached in this Gist should be added to this directory.

Under migrations/ also, you're expected to add your migration files, which can be .py, .sql or both. Raw SQL files are used for raw create statements. Python scripts are used to move data around (if needed).

The script expects yyyy-mm-dd-hh-mm-migration-name.(py|sql) as the format used in your migration filenames.

The fabfile.py contains two extremely simple tasks: list and migrate.

fab list displays all files that conform the above migration filename format.

fab migrate:$migration runs the migration specified with a filename (extension included). If it's a .sql file, it tries to run it with the mysql binary. If it's a .py file, it loads it as a module and executes its migrate() function.

The script assumes you have a support.py file on your project's root path that is responsible for loading all dependencies, including sqlwitch, and a function called get_db_handler() which returns a sqlwitch object. This is how this function looks in one of my projects (might have to be different in yours):

def get_db_handler(db_auth):
  conn = MySQLdb.connect(**db_auth)
  cursor = conn.cursor(MySQLdb.cursors.DictCursor)
  conn.set_character_set('utf8')
  cursor.execute('set names utf8;')
  cursor.execute('set character_set_connection = utf8;')
  return sqlwitch(cursor, conn)

The script also assumes you have a dictionary called config defined in support.py, containing another dictionary called db with the properties host, user, passwd and db defined (for use in MySQLdb.connect()).

from __future__ import with_statement
from fabric.api import *
import os, sys, imp, re;
APP_ROOT = os.path.join(os.path.dirname(__file__), os.path.pardir)
sys.path += [APP_ROOT, os.path.join(APP_ROOT, 'lib')]
from support import config, get_db_handler
def list():
migrations = [
file for file in os.listdir('.')
if re.match('\d{4}-\d{2}-\d{2}.*(py|sql)$', file)
]
print('Migrations in descending order:')
print('')
for migration in sorted(migrations)[::-1]:
print(' - %s' % migration)
def migrate(migration):
filepath, extension = os.path.splitext(migration)
if extension == '.py':
migration = load_source('migration', os.path.join(APP_ROOT, 'migrations', migration))
migration.migrate(get_db_handler(config['db']))
elif extension == '.sql':
migration_file = os.path.abspath(os.path.join(APP_ROOT, 'migrations', migration))
local('mysql -u root -p %s < %s' % (config['db']['db'], migration_file))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment