#!/usr/bin/env python3
# -*- coding: utf-8 -*-

' a pymysql module '
__author__ = 'Roc Wong'

import pymysql

class RocDb:
    __db = None

    '''
    init  初始化
    * cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    '''

    def __init__(self, hostname, username, password, database):
        self.__db = pymysql.connect(hostname, username, password, database, charset='utf8', cursorclass=pymysql.cursors.DictCursor)

    '''
    DB - select 查询
    :param sql string
    :param param list
    '''

    def select(self, table_name, where, order=''):
        try:
            with self.__db.cursor() as cursor:
                sql = self.settleSql('SELECT', table_name, where, {}, order)
                cursor.execute(sql)
                data = cursor.fetchall()
                return data
        except Exception as e:
            print('Exception: ', e)
            return []

    '''
    DB - find  查找
    :param sql string
    :param param list
    '''

    def find(self, table_name, where, order=''):
        try:
            with self.__db.cursor() as cursor:
                sql = self.settleSql('SELECT', table_name, where, {}, order)
                cursor.execute(sql)
                data = cursor.fetchone()
                return data
        except Exception as e:
            print('Exception: ', e)
            return []

    '''
    DB - insert  插入
    :param sql string
    :param param list
    '''

    def insert(self, table_name, data):
        try:
            with self.__db.cursor() as cursor:
                sql = self.settleSql('INSERT',table_name,{},data)
                if type(data) == type([]):
                    back = cursor.executemany(sql,data)
                else:
                    cursor.execute(sql,data)
                    back = cursor.lastrowid
                self.__db.commit()
            return back
        except Exception as e:
            print('Exception: ', e)
            self.__db.rollback()

    '''
    DB - update  更新
    :param table_name string
    :param data list
    :param where list
    '''

    def update(self, table_name, data, where={}):
        try:
            with self.__db.cursor() as cursor:
                sql = self.settleSql('UPDATE', table_name, where, data)
                effect_rows = cursor.execute(sql)
                self.__db.commit()
            return effect_rows
        except Exception as e:
            print('Exception: ', e)
            self.__db.rollback()

    '''
    DB - delete  删除
    :param table_name string
    :param where list
    '''

    def delete(self, table_name, where):
        try:
            with self.__db.cursor() as cursor:
                sql = self.settleSql('DELETE', table_name, where)
                exit()
                effect_rows = cursor.execute(sql)
                self.__db.commit()
            return effect_rows
        except Exception as e:
            print('Exception: ', e)
            self.__db.rollback()

    '''
    settle sql  sql语句组装
    :param operate string
    :param table_name string
    :param where list
    '''

    def settleSql(self, operate, table_name, where={}, data={}, order=''):
        if operate == 'UPDATE':
            sql = F"""{operate} `{table_name}`"""
            sql += self.settleSets(table_name, data)
            sql += self.settleWhere(table_name, where)
        elif operate == 'DELETE':
            sql = F"""{operate} FROM `{table_name}`"""
            sql += self.settleWhere(table_name, where)
        elif operate == 'SELECT':
            sql = F"""{operate} * FROM `{table_name}`"""
            sql += self.settleWhere(table_name, where)
            if len(order) > 0:
                sql += F""" ORDER BY {order}"""
        elif operate == 'INSERT':
            table_dict = self.getTableDict(table_name)
            liter = "%s," * len(table_dict)
            sql = F"""{operate} INTO `{table_name}`({self.settleTableDict(table_dict)}) VALUES({liter[:-1]})"""

        return sql

    '''
    settle sets 设置set字段
    '''

    def settleSets(self, table_name, data):
        sets = ''
        for key in data:
            if type(data[key]) == type(1):
                sets += F""" `{table_name}`.`{key}` = {data[key]},"""
            else:
                sets += F""" `{table_name}`.`{key}` = '{data[key]}',"""

        return ' SET' + sets[:-1]

    '''
    settle where  设置where字段
    '''

    def settleWhere(self, table_name, where):
        if len(where) > 0:
            condition = ''
            for key in where:
                if type(where[key]) == type(1):
                    condition += F""" `{table_name}`.`{key}` = {where[key]} AND"""
                else:
                    condition += F""" `{table_name}`.`{key}` = '{where[key]}' AND"""

            return ' WHERE' + condition[:-4]
        else:
            return ''


    '''
    settleTableDict  设置数据表字典
    '''

    def settleTableDict(self, table_dict):
        dict = ''
        for item in table_dict:
            dict += '`' + item + '`,'

        return dict[:-1]

    '''
    getTableDict  获取数据库字典
    '''
    def getTableDict(self, table_name):
        try:
            with self.__db.cursor() as cursor:
                cursor.execute(F"SELECT COLUMN_NAME FROM `information_schema`.COLUMNS WHERE `COLUMN_KEY` != 'PRI' AND `EXTRA` != 'auto_increment' AND `table_name` = '{table_name}'")
                dict = cursor.fetchall()
                data_dict = []
                for field in dict:
                    data_dict.append(field['COLUMN_NAME'])

            return data_dict
        except Exception as e:
            print('Exception: ', e)
            self.__db.rollback()

    '''
    destruct  析构函数
    '''

    def __del__(self):
        self.__db.close()