create-mysqldb-users.py

#!/usr/bin/python
# https://docs.python.org/3/howto/argparse.html
# https://pythonspot.com/reading-csv-files-in-python/
# https://www.tutorialspoint.com/python/python_database_access.htm
# https://mysqlserverteam.com/how-to-create-multiple-accounts-for-an-app/
import argparse
import csv
import MySQLdb

# Get script arguments
parser = argparse.ArgumentParser()
parser.add_argument("csvfile", 
    help="CSV file with databases and users: dbname, user, pass")
parser.add_argument("-fc", "--firstcolumn", 
    help="CSV first column (dbname). Default: 1",
    nargs='?', type=int, const=1, default=1)
parser.add_argument("-d", "--drop", 
    help="Drop databases and users before creating", 
    action="store_true")
parser.add_argument("-nc", "--nocreation", 
    help="Skip databases and users creation", 
    action="store_true")
parser.add_argument("-e", "--execute", 
    help="Execute SQL directly (TO DO)", 
    action="store_true")
args = parser.parse_args()

# Prepare SQL
sql1  = """
CREATE DATABASE IF NOT EXISTS `{dbname}` CHARACTER SET `utf8mb4` COLLATE `utf8mb4_unicode_ci`;
"""
sql1r = """
DROP DATABASE `{dbname}`;
"""
sql2  = """
CREATE USER IF NOT EXISTS `{user}`@`localhost` IDENTIFIED by "{password}";
GRANT ALL PRIVILEGES ON `{dbname}`.* TO `{user}`@`localhost`;
GRANT ALL PRIVILEGES ON `{dbname}\_%`.* TO `{user}`@`localhost`;
"""
sql2r = """
DROP USER `{user}`@`localhost`";
"""

sql = ""
dbC = {}
dbD = {}

def replace_all(text, dic):
    for i, j in dic.iteritems():
        text = text.replace(i, j)
    return text

with open(args.csvfile) as csvDataFile:
    csvReader = csv.reader(csvDataFile)
    next(csvReader) # skip first row
    for row in csvReader:
        dic = {
            "{dbname}": row[args.firstcolumn],
            "{user}": row[args.firstcolumn+1],
            "{password}": row[args.firstcolumn+2]
        }
        if args.drop:
            # Drop database once
            if not (dic["{dbname}"] in dbD):
                sql += replace_all(sql1r, dic)
                dbD[dic["{dbname}"]] = True
            # Drop users
            sql += replace_all(sql2r, dic)
        if not args.nocreation:
            # Create database once
            if not (dic["{dbname}"] in dbC):
                sql += replace_all(sql1, dic)
                dbC[dic["{dbname}"]] = True
            # Create users
            sql += replace_all(sql2, dic)

if args.execute:
    # Open database connection
    db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB" )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    try:
        # Execute the SQL command
        cursor.execute(sql)
        # Commit your changes in the database
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()
    # disconnect from server
    db.close()
else:
    print(sql)