recreate_db.py

#!/usr/bin/env python
"""
Python script to list all MySQL databases and allow them to be recreated.
This assumes you have server information and credentials stored in a ~/.my.cnf
file (ideally chmoded to 0600)

[client]
host=localhost
user=root
password=secretpasswordgoeshere

With that set up, just invoke this script and you'll be asked to pick a
database from a list, prompted that you're sure you want to erase all data,l
and then recreate it.
"""

import readline
from subprocess import Popen, PIPE
import sys


def list_dbs():
    """Call mysql and list all databases, if err return err"""
    p = Popen(['mysql'], stdout=PIPE, stdin=PIPE, stderr=PIPE)
    out, err = p.communicate(input=b'show schemas')
    if not err:
        all_dbs = list(filter(None, out.decode().split('\n')))[1:]
        valid_dbs = []
        for item in all_dbs:
            if item not in ['information_schema', 'mysql',
                            'performance_schema', 'sys']:
                valid_dbs.append(item)
        return valid_dbs
    return err


def prompt_user(db_list):
    """Ask user which db number they want to delete, and for confirmation."""
    sys.stdout.write('Select a MySQL database to reset by number:\n')

    # if list is empty, tell the user
    if not db_list:
        sys.stdout.write('No databases available to select\n')
        sys.exit(0)

    for item in db_list:
            sys.stdout.write('%s. %s\n' % (i, item))
    

    #  Check for python version (2 = raw_input)
    if sys.version_info >= (3, 0):
        number = input('Choose a number: ')
    else:
        number = raw_input('Choose a number: ')

    confirm_message = ('Are you sure you want to recreate %s? '
                       '(THIS WILL DELETE ALL DATA.) (y/N) '
                       % db_list[int(number)-1])

    if sys.version_info >= (3, 0):
        res = input(confirm_message) or 'N'
    else:

        res = raw_input(confirm_message) or 'N'

    return (number, res, db_list[int(number) - 1])


def recreate_db(db_name):
    """Recreate the database by drop and then create with charset utf"""
    p = Popen(['mysql'], stdout=PIPE, stdin=PIPE)
    command = 'drop schema %s; create schema %s charset utf8' % (db_name,
                                                                 db_name)
    out, err = p.communicate(input=command.encode())
    if not err:
        return out
    return err


if __name__ == '__main__':
    db_list = list_dbs()
    if not isinstance(db_list, list):
        print('Error: %s' % db_list.decode('utf-8'))
    try:
        number, res, db_name = prompt_user(db_list)
    except (IndexError, ValueError) as err:
        print('Choose a valid input. Err: %s\n' % err)
        sys.exit(1)

    if res.lower() != 'y':
        print('Aborting!')
        sys.exit(0)

    print('Recreating... %s' % db_name)
    res = recreate_db(db_name)
    if res:
        print(res.decode())
    sys.exit(0)