# -*- coding: utf-8 -*-

"""
Datetime: 2020/03/17
Author: Zhang Yafei
Description: 
"""
from DBUtils.PooledDB import PooledDB
from settings import DATABASE
import threading

lock = threading.Lock()


class SqliteHelper(object):
    def __init__(self, db_config):
        """
        # sqlite3
        # 连接数据库文件名，sqlite不支持加密，不使用用户名和密码
        import sqlite3
        config = {"database": "path/to/your/dbname.db"}
        pool = PooledDB(sqlite3, maxcached=50, maxconnections=1000, maxusage=1000, **config)
        # mysql
        import pymysql
        pool = PooledDB(pymysql,5,host='localhost', user='root',passwd='pwd',db='myDB',port=3306) #5为连接池里的最少连接数
        # postgressql
        import psycopg2
        POOL = PooledDB(creator=psycopg2, host="127.0.0.1", port="5342", user, password, database)
        # sqlserver
        import pymssql
        pool = PooledDB(creator=pymssql, host=host, port=port, user=user, password=password, database=database, charset="utf8")
        :param type:
        """
        self.pool = PooledDB(**db_config)
        self.conn = self.pool.connection()
        self.cursor = self.conn.cursor()

    def connect(self):
        conn = self.pool.connection()
        cursor = self.conn.cursor()
        return conn, cursor

    @staticmethod
    def connect_close(conn, cursor):
        """关闭连接"""
        conn.close()
        cursor.close()

    def __del__(self):
        self.cursor.close()
        self.conn.close()

    def execute(self, sql, params=tuple()):
        try:
            lock.acquire(True)
            self.cursor.execute(sql, params)  # 执行这个语句
            self.conn.commit()
            lock.release()
        except Exception as e:
            print(e)

    def execute_many(self, sql, params=tuple()):
        try:
            lock.acquire(True)
            self.cursor.executemany(sql, params)
            self.conn.commit()
            lock.release()
        except Exception as e:
            print(e)

    def fetchone(self, sql, params=tuple()):
        try:
            lock.acquire(True)
            self.cursor.execute(sql, params)
            data = self.cursor.fetchone()
            lock.release()
            return data
        except Exception as e:
            print(e)

    def fetchall(self, sql, params=tuple()):
        try:
            lock.acquire(True)
            self.cursor.execute(sql, params)
            data = self.cursor.fetchall()
            lock.release()
            return data
        except Exception as e:
            print(e)


class DBHelper(object):
    def __init__(self, db_config):
        self.pool = PooledDB(**db_config)

    def __connect(self):
        conn = self.pool.connection()
        cursor = conn.cursor()
        return conn, cursor

    @staticmethod
    def __connect_close(conn, cursor):
        """关闭连接"""
        cursor.close()
        conn.close()

    def execute(self, sql, params=tuple()):
        conn, cursor = self.__connect()
        cursor.execute(sql, params)
        conn.commit()
        self.__connect_close(conn, cursor)

    def execute_many(self, sql, params=tuple()):
        conn, cursor = self.__connect()
        cursor.executemany(sql, params)
        conn.commit()
        self.__connect_close(conn, cursor)

    def fetchone(self, sql, params=tuple()):
        conn, cursor = self.__connect()
        cursor.execute(sql, params)
        data = cursor.fetchone()
        self.__connect_close(conn, cursor)
        return data

    def fetchall(self, sql, params=tuple()):
        conn, cursor = self.__connect()
        cursor.execute(sql, params)
        data = cursor.fetchall()
        self.__connect_close(conn, cursor)
        return data


def init_db(conn_db='sqlite3'):
    """
    初始化DB
    :param conn_db:
    :return:
    """
    if conn_db in {'sqlite3', 'mysql'}:
        db_config = DATABASE.get(conn_db)
        database = DBHelper(db_config)
        return database
    else:
        raise Exception("conn_db 只能是sqlite3 OR mysql")


db = init_db(conn_db=DATABASE.get('use_db'))