from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy.orm import Session

DBURL = 'mysql+pymysql://root:ormaorse@localhost:6606/webors'


class DBNotFound(Exception):
   pass


class DB(object):
    def __init__(self, method, echo:bool = False):
       self.engine = create_engine(method, echo=echo)
       self.connexion = self.engine.connect()

    def __enter__(self):
        """
        In order to work the with sentence
        """
        return self
    def __exit__(self, exc_type, exc_val, exc_tb):
        """
        In order to work the with sentence
        """
        self.close()

    def execute_dict(self, sql, **kargs):
        """
        Execute the sql statement in the database

        Parameters
        ----------
        sql: any object that connexion.exectue recognizes
        kargs: a dictionary with the parameters of the execution.
        """
        return  self.connexion.execute(sql, kargs).mappings()

    def execute(self, sql, **kargs):
        return  self.connexion.execute(sql, kargs)

    def table_names(self):
        return self.engine.table_names()

    def get_session(self) -> Session:
        return Session(self.engine)


    def close(self):
        """
        close the connection
        """
        self.connexion.close()

def get_default_conn(echo:bool = False) -> DB:
    return DB(DBURL, echo)

def test_DB():
    with get_default_conn() as conn:
        sql = text(
            """
            select count(*) from clasvig where nom like :nom
            """
        )
        res = conn.execute(sql, nom='A%')
        for row in res:
            print(row)