mpls · May 16, 2018 05:12 · Mar 24, 2015 · Mar 24, 2015
diff --git a/mysql.py b/mysql.py
@@ -1,6 +1,10 @@
+"""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,
@@ -20,6 +24,17 @@ def get_innodb_log_file_size():
 
 
 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")
 
@@ -39,10 +54,48 @@ def get_mysql_var(name, scope='global'):
 
     return stdout.strip()
 
-ib_logfile_size = get_innodb_log_file_size()
 
+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
 
-print("datadir = %s" % datadir)
+# 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'])
diff --git a/mysql.py b/mysql.py
@@ -0,0 +1,48 @@
+import subprocess
+
+def get_innodb_log_file_size():
+    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'):
+    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()
+
+ib_logfile_size = get_innodb_log_file_size()
+
+print(ib_logfile_size)
+
+datadir = get_mysql_var('datadir')
+
+print("datadir = %s" % datadir)
No results found