from bottle import request, route, run, template,view,static_file,response,Response,json_dumps,jinja2_template
import os
import urllib
import requests
import json
import requests
# import requests.packages.urllib3.util.ssl_
requests.packages.urllib3.util.ssl_.DEFAULT_CIPHERS = 'ALL'
import sys
import os
import sqlite3

Path=os.path.dirname(os.path.realpath(__file__)) #获取当前路径
# print(Path)
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d
class Sqldata: #False  True
    def __init__(self, dbname = 'data.db'):
        self.conn = sqlite3.connect(Path+"/"+dbname, check_same_thread=False)
        self.conn.row_factory = dict_factory
    def sent(self,sql):
        ret = True
        result = None
        try:
            cursor = self.conn.cursor()
            if sql.split()[0].lower() == "select":  #判断是否是查询语句
                cursor.execute(sql)                 #执行语句
                result = cursor.fetchall()          #使用 fetchall() 方法获取所有数据
                # print(result)                       
            else:
                cursor.execute(sql)         #执行语句 
                result = cursor.fetchall()    
                self.conn.commit()       #提交数据，不然数据库中会没有增加的数据
                print("运行成功")
            cursor.close()
        except Exception as e:
            self.conn.rollback()      # 执行回滚操作
            print(e)
            ret = False
        finally:
            pass
            # self.conn.commit()    # 关闭游标
            # self.conn.close()     # 关闭数据库连接
        return {"code":ret,"msg":"ok","data":result}
    def add_table(self,name):  #  创建数据表
        sql = '''CREATE TABLE IF NOT EXISTS {}
             (uid INTEGER PRIMARY KEY AUTOINCREMENT,
             username VARCHAR(255),
             password VARCHAR(255),
             menu_url VARCHAR(20),
             email VARCHAR(20));'''.format(name)
        return sql            
    def add_table(self,name):  #  创建数据表
        sql = "CREATE TABLE IF NOT EXISTS {} (uid INTEGER PRIMARY KEY {},username VARCHAR(255),password VARCHAR(255),menu_url VARCHAR(255),email VARCHAR(255))".format(name,self.AUTO_INCREMENT) # 创建数据表
        return self.sent(sql)    
    def __del__(self):  #关闭方法
        self.conn.commit()    # 关闭游标
        self.conn.close()     # 关闭数据库连接

    def insert(self, dic, tblName):
        sql_key = ', '.join(dic.keys())
        sql_val = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_val = sql_val + '\'' + dic[key] + '\','
            else:
                sql_val = sql_val + str(dic[key]) + ' ,'

        # 移除 sql_val最后一个 ','
        sql = "insert into {} ({}) values ({}) ".format(tblName, sql_key, sql_val[:-1])
        return self.sent(sql)  
    # 调用者需要自己补充 where之后的条件语句
    def update(self, dic, tblName):
        sql_key = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_key = sql_key + key + ' = \'' + dic[key] + '\','
            else:
                sql_key = sql_key + key + ' = ' + str(dic[key]) + ' ,'

        # 移除 sql_key 最后一个 ','
        sql = "update {} set {} where uid !='' ".format(tblName, sql_key[:-1])
        return self.sent(sql)        
    def select(self, dic, tblName):
        sql_key = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_key = sql_key + key + ' = \'' + dic[key] + '\' and '
            else:
                sql_key = sql_key + key + ' = ' + str(dic[key]) + ' and '

        # 移除 sql_key 最后一个 ','
        sql = "SELECT * FROM {} where {}".format(tblName,sql_key[:-5])
        return self.sent(sql)  
    def delete(self, dic, tblName):
        sql_key = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_key = sql_key + key + ' = \'' + dic[key] + '\' and '
            else:
                sql_key = sql_key + key + ' = ' + str(dic[key]) + ' and '

        # 移除 sql_key 最后一个 ','
        sql = "delete FROM {} where {}".format(tblName,sql_key[:-5])
        return self.sent(sql)

    def user_get(self,username,password):
        return self.select({"username":username,"password":password},"user")["data"]

    def user_add(self,username,password):
        return self.insert({"username":username,"password":password},"user")["code"]        
t = Sqldata()
# while True:
    # name = input("请输入：")

    # # sql = "insert into {} ({}) values ({}) ".format(tblName, sql_key, sql_val[:-1])
    # # sql = "update {} set {} where uid !='' ".format(tblName, sql_key[:-1])
    # # sql = "delete from {} where {}".format(tblName,sql_key[:-5])
    # sql = "select * from {}".format(name)

    # df = t.sent(sql)
    # print(df)
    
    
@route('/api')
def api():
    # reqbasicInfo = urllib.parse.unquote(request.url)
    # print(reqbasicInfo)
    # print(dict(request.query))
    # name = request.query_string   # http://127.0.0.1:8888/api?&w=lishi
    name = dict(request.query)["w"]  # http://127.0.0.1:8888/api?&w=lishi
    sql = "select * from {}".format(name)

    df = t.sent(sql)

    return df 

@route('/')
def index():
    return template('1')    
    
run(host='127.0.0.1', port=8888, debug=True)