from pathlib import Path
import sqlite3
import numpy as np
import pandas as pd


class SQLDataset:
    def __init__(self, sql_name):
        '''
        sqlite3存在系统表sqlite_master,结构如下：
        sqlite_master(
            type TEXT,      #类型:table-表,index-索引,view-视图
            name TEXT,      #名称:表名,索引名,视图名
            tbl_name TEXT,
            rootpage INTEGER,
            sql TEXT
            )
        '''
        self.sql_name = Path(sql_name)

    @property
    def tab_names(self):
        '''
        获取 sqlite3 数据的全部表名
        '''
        with sqlite3.connect(self.sql_name) as conn:
            cu = conn.cursor()  # 获取表名，保存在tab_name列表
            cu.execute("select name from sqlite_master where type='table'")
            tab_name = cu.fetchall()
        return [line[0] for line in tab_name]

    def is_null(self):
        if self.tab_names == []:
            return True
        else:
            return False

    def _sql2pd(self):
        # root sqlite3 数据的 path
        with sqlite3.connect(self.sql_name) as con:
            # read_sql_query和read_sql都能通过SQL语句从数据库文件中获取数据信息
            pd_list = [pd.read_sql(
                f"SELECT * FROM {tab_name}", con=con) for tab_name in self.tab_names]
        return pd_list