Run queries directly against MySQL via subprocess, rather than using MySQLdb

mysql.py

"""Examples fetching data from MySQL via /usr/bin/mysql"""

import subprocess

def get_innodb_log_file_size():
    """Run SELECT @@innodb_log_file_size and return the value as an integer"""

    process = subprocess.Popen(['mysql', '-ss'],
                               stdout=subprocess.PIPE,
                               stderr=subprocess.STDOUT,
                               stdin=subprocess.PIPE)
    stdout, _ = process.communicate("SELECT @@global.innodb_log_file_size")

    if process.returncode != 0:
        print("Something failed")
        raise Exception("Failed to find innodb log file size")

    # otherwise, success!
    # stdout will be the actual output with a trailing newline
    # let's decode it into an integer:

    # strip whitespace (i.e. the trailing newline) and convert to an integer
    return int(stdout.strip())


def get_mysql_var(name, scope='global'):
    """Fetch arbitrary variables by 'name' from MySQL

    This is a more generic version of get_innodb_log_file_size()
    that works for any variable name, but does not convert any
    values.

    get_innodb_log_file_size() could be rewritten as:

    int(get_mysql_var('innodb_log_file_size'))

    """
    if scope not in ('global', 'session'):
        raise Exception("scope must be one of: global, session")

    sql = 'SELECT @@{scope}.{name}'.format(scope=scope, name=name)

    process = subprocess.Popen(['mysql', '-ss'],
                               stdout=subprocess.PIPE,
                               stderr=subprocess.STDOUT,
                               stdin=subprocess.PIPE)

    stdout, _ = process.communicate(input=sql)

    if process.returncode != 0:
        print("Query failed: %s" % sql)
        print("Output was: %s" % stdout)
        raise Exception("Failed to lookup variable '%s'" % name)

    return stdout.strip()


def global_variables():
    """Dump out all global variables and return a python dictionary

    This runs SHOW GLOBAL VARIABLES and creates a mapping of names
    to values, just like you would see by running SHOW GLOBAL VARIABLES
    in the MySQL client.  Nothing is converted so all the values will
    be python strings.  If integers are expected those values would
    need to be converted first.
    """
    process = subprocess.Popen(['mysql', '-ss'],
                               stdout=subprocess.PIPE,
                               stderr=subprocess.STDOUT,
                               stdin=subprocess.PIPE)

    stdout, _ = process.communicate(input='SHOW GLOBAL VARIABLES')

    result = {}

    for line in stdout.splitlines():
        key, value = line.split('\t', 1)  # split up to first tab character
        result[key] = value

    return result

ib_logfile_size = get_innodb_log_file_size()
print(ib_logfile_size)

datadir = get_mysql_var('datadir')
print("datadir = %s" % datadir)


all_vars = global_variables()

import pprint  # <- pretty print helper

pprint.pprint(all_vars)  # <- just to show what we collected

print(all_vars['datadir'])  # - alternate way to look up a single variable

# NOTE: these are string -> string mappings.
# That means all_vars['innodb_log_file_size'] is a string so
# you would have to convert it to an integer before doing
# math on it (i.e. to pretty print MB / GB / etc.)
# int(all_vars['innodb_log_file_size'])