import pyodbc
import logging
from configparser import ConfigParser



class DataBase(object):
    def __init__(self, queries_schema, dataset_schema):
        self.__queries_schema = queries_schema
        self.__dataset_schema = dataset_schema
        self.__cursor = self.connect_db()


    def load_config(self):
        cf = ConfigParser()
        cf.read('config.ini')
        server = cf.get('db', 'server')
        username = cf.get('db', 'username')
        password = cf.get('db', 'password')
        database = cf.get('db', 'database')
        driver = cf.get('db', 'driver')
        return server, username, password, database, driver

    def connect_db(self):
        server, username, password, database, driver = self.load_config()
        cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server +
                              ';PORT=1433;DATABASE='+database+';UID='+username+';PWD=' + password)
        cursor = cnxn.cursor()
        if not cursor:
            print('can\'t connect db!')
        else:
            logger = logging.getLogger("python-logger")
            stream_handler = logging.StreamHandler(sys.stderr)
            logger.addHandler(stream_handler)
            logger.setLevel(logging.INFO)
            logger.info('connect success! from logging')
            # print('connect success!')
        return cursor

    # 根据Query返回Id
    def get_id_by_query(self, utterance):
        tsql = "SELECT id FROM "+self.__queries_schema + " WHERE Query = ?"
        with self.__cursor.execute(tsql, utterance):
            row = self.__cursor.fetchone()
            if row:
                return row[0]
            else:
                return None
            # while row:
            #     print(row[0])
            #     row = self.__cursor.fetchone()