Source code for fabtools.mysql

"""
MySQL users and databases
=========================

This module provides tools for creating MySQL users and databases.

"""
from __future__ import with_statement

from fabric.api import env, hide, prompt, puts, run, settings

from fabtools.utils import run_as_root


def prompt_password(user='root'):
    """
    Ask MySQL password interactively.
    """
    return prompt('Please enter password for MySQL user "%s":' % user)


def _query(query, use_sudo=True, **kwargs):
    """
    Run a MySQL query.
    """
    func = use_sudo and run_as_root or run

    user = kwargs.get('mysql_user') or env.get('mysql_user')
    password = kwargs.get('mysql_password') or env.get('mysql_password')
    if user and not password:
        password = prompt_password(user)

    return func('mysql --batch --raw --skip-column-names --user=%(user)s --password=%(password)s --execute="%(query)s"' % {
        'user': user,
        'password': password,
        'query': query
    })


[docs]def user_exists(name, host='localhost', **kwargs): """ Check if a MySQL user exists. """ with settings(hide('running', 'stdout', 'stderr', 'warnings'), warn_only=True): res = _query(""" use mysql; SELECT COUNT(*) FROM user WHERE User = '%(name)s' AND Host = '%(host)s'; """ % { 'name': name, 'host': host, }, **kwargs) return res.succeeded and (int(res) == 1)
[docs]def create_user(name, password, host='localhost', **kwargs): """ Create a MySQL user. Example:: import fabtools # Create DB user if it does not exist if not fabtools.mysql.user_exists('dbuser'): fabtools.mysql.create_user('dbuser', password='somerandomstring') """ with settings(hide('running')): _query("CREATE USER '%(name)s'@'%(host)s' IDENTIFIED BY '%(password)s';" % { 'name': name, 'password': password, 'host': host }, **kwargs) puts("Created MySQL user '%s'." % name)
[docs]def database_exists(name, **kwargs): """ Check if a MySQL database exists. """ with settings(hide('running', 'stdout', 'stderr', 'warnings'), warn_only=True): res = _query("SHOW DATABASES LIKE '%(name)s';" % { 'name': name }, **kwargs) return res.succeeded and (res == name)
[docs]def create_database(name, owner=None, owner_host='localhost', charset='utf8', collate='utf8_general_ci', **kwargs): """ Create a MySQL database. Example:: import fabtools # Create DB if it does not exist if not fabtools.mysql.database_exists('myapp'): fabtools.mysql.create_database('myapp', owner='dbuser') """ with settings(hide('running')): _query("CREATE DATABASE %(name)s CHARACTER SET %(charset)s COLLATE %(collate)s;" % { 'name': name, 'charset': charset, 'collate': collate }, **kwargs) if owner: _query("GRANT ALL PRIVILEGES ON %(name)s.* TO '%(owner)s'@'%(owner_host)s' WITH GRANT OPTION;" % { 'name': name, 'owner': owner, 'owner_host': owner_host }, **kwargs) puts("Created MySQL database '%s'." % name)

Project Versions

This Page