#!/usr/bin/python
# -*- coding: utf8 -*-

# sudo pip3 install mysqlclient xmltodict

import os
import MySQLdb
import xmltodict
import argparse

parser = argparse.ArgumentParser(description='Python Magento DB', add_help=False)

parser.add_argument("-h", "--host", help="DB host")
parser.add_argument("-u", "--user", help="DB user")
parser.add_argument("-p", "--password", help="DB password")
parser.add_argument("-n", "--name", help="DB name")

args = parser.parse_args()

SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
CONFIG_FILE = "{}/../app/etc/local.xml".format(SCRIPT_DIR)

local_xml = content = open(CONFIG_FILE, 'r').read()

xml = xmltodict.parse(local_xml)

prefix = xml['config']['global']['resources']['db']['table_prefix']
db_host = xml['config']['global']['resources']['default_setup']['connection']['host']
db_user = xml['config']['global']['resources']['default_setup']['connection']['username']
db_password = xml['config']['global']['resources']['default_setup']['connection']['password']
db_name = xml['config']['global']['resources']['default_setup']['connection']['dbname']

db = MySQLdb.connect(
    host=args.host if args.host else db_host,
    user=db_user,
    passwd=db_password,
    db=db_name,
    charset="utf8",
    use_unicode=True
)

cur = db.cursor()

help = """
# Example of request
cur.execute("SELECT * FROM core_config_data")
for row in cur.fetchall():
    print(row[0])
prettify(cur.fetchall())
db.close()
"""

print(help)

# Function to prettify fetchall output
def prettify(results, vertical=False):
    if not vertical:
        widths = []
        columns = []
        tavnit = '|'
        separator = '+'

        for cd in cur.description:
            widths.append(max(cd[2], len(cd[0])))
            columns.append(cd[0])

        for w in widths:
            tavnit += " %-"+"%ss |" % (w,)
            separator += '-'*w + '--+'

        print(separator)
        print(tavnit % tuple(columns))
        print(separator)
        for row in results:
            print(tavnit % row)
        print(separator)

    else:
        print(results)

        column_length = 0
        for cd in cur.description:
            if len(cd[0]) >= column_length:
                column_length = len(cd[0])

        for index, row in enumerate(results):
            print("{} {}. row {}".format("*"*27, index+1, "*"*27))
            for cd, value in zip(cur.description, row):
                tavnit = "{:>%s}: " % (column_length,)
                print(tavnit.format(cd[0]) + value)

        print("{} rows in set".format(len(results)))