import pandas as pd
df = pd.read_csv('C:\\Users\\xiejava\\Desktop\\staff.csv')
df

import pandas as pd
df = pd.read_excel('C:\\Users\\xiejava\\Desktop\\staff.xlsx')
df

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:yourpassword@localhost:3306/pandastest')
sql_query_staff = 'select * from staff;'
df_staff = pd.read_sql_query(sql_query_staff, engine)
df_staff

import requests
import json
import pandas as pd
url='http://127.0.0.1:5000/getstaff'
resp=requests.get(url)
data=json.loads(resp.text)
staff=pd.DataFrame(data)
staff

pip install docx
pip install pandas

pip install openpyxl

files=os.listdir(filepah)
for file in files:
    if file.find('.docx')>0:
        docfilepah=filepah+file

data=[]
#读word的docx评议表文件，并读取word中的表格数据
def procdoc(docfilepath):
    document=Document(docfilepath)
    tables=document.tables
    table=tables[0]
    for i in range(1,len(table.rows)):
        id=int(table.cell(i,0).text)
        name=table.cell(i,1).text
        excellent=0
        if table.cell(i,2).text!='' and table.cell(i,2).text is not None:
            excellent=1
        competent = 0
        if table.cell(i, 3).text!='' and table.cell(i, 3).text is not None:
            competent=1
        basicacompetent=0
        if table.cell(i, 4).text!='' and table.cell(i, 4).text is not None:
            basicacompetent=1
        notcompetent = 0
        if table.cell(i, 5).text!='' and table.cell(i, 5).text is not None:
            notcompetent=1
        dontunderstand =0
        if table.cell(i, 6).text!='' and table.cell(i, 6).text is not None:
            dontunderstand=1
        appraisedata=[id,name,excellent,competent,basicacompetent,notcompetent,dontunderstand]
        data.append(appraisedata)

df = pd.DataFrame(data,columns=['序号','姓名','优秀','称职','基本称职','不称职','不了解'])
df=df.groupby(['序号','姓名']).sum() #汇总每个人每一项的评分
df['票数'] = df.apply(lambda x: x.sum(), axis=1) #统计票数
df['计分'] = (df['优秀']*95+df['称职']*85+df['基本称职']*75+df['不称职']*65+df['不了解']*0)/len(df)#根据规则计分
df['评价']=df['计分'].map(getscore) #根据规则评价评级

#根据评分规则计算评级
def getscore(x):
    if x>=95:
        score='优秀'
    elif x>=80 and x<95:
        score='称职'
    elif x>=75 and x<80:
        score='基本称职'
    elif x<75:
        score='不称职'
    return score

#将汇总计算好的数据写入Excel
def write2excle(exclefile,dataframe):
    writer = pd.ExcelWriter(exclefile)
    dataframe.to_excel(writer)
    writer.save()
    print('输出成功')

import os
import pandas as pd
from docx import Document

data=[]
#读word的docx评议表文件，并读取word中的表格数据
def procdoc(docfilepath):
    document=Document(docfilepath)
    tables=document.tables
    table=tables[0]
    for i in range(1,len(table.rows)):
        id=int(table.cell(i,0).text)
        name=table.cell(i,1).text
        excellent=0
        if table.cell(i,2).text!='' and table.cell(i,2).text is not None:
            excellent=1
        competent = 0
        if table.cell(i, 3).text!='' and table.cell(i, 3).text is not None:
            competent=1
        basicacompetent=0
        if table.cell(i, 4).text!='' and table.cell(i, 4).text is not None:
            basicacompetent=1
        notcompetent = 0
        if table.cell(i, 5).text!='' and table.cell(i, 5).text is not None:
            notcompetent=1
        dontunderstand =0
        if table.cell(i, 6).text!='' and table.cell(i, 6).text is not None:
            dontunderstand=1
        appraisedata=[id,name,excellent,competent,basicacompetent,notcompetent,dontunderstand]
        data.append(appraisedata)

#读取评议表的目录，并处理目录中的docx文件，根据评议表计算评分，写入汇总表。
def readfile(filepah):
    files=os.listdir(filepah)
    for file in files:
        if file.find('.docx')>0:
            docfilepah=filepah+file
            procdoc(docfilepah)
    df = pd.DataFrame(data,columns=['序号','姓名','优秀','称职','基本称职','不称职','不了解'])
    print(df)
    df=df.groupby(['序号','姓名']).sum()
    df['票数'] = df.apply(lambda x: x.sum(), axis=1)
    df['计分'] = (df['优秀']*95+df['称职']*85+df['基本称职']*75+df['不称职']*65+df['不了解']*0)/len(df)
    df['评价']=df['计分'].map(getscore)
    print(df)
    write2excle('民主评议\\民主评议表汇总.xlsx',df)

#根据评分规则计算评级
def getscore(x):
    if x>=95:
        score='优秀'
    elif x>=80 and x<95:
        score='称职'
    elif x>=75 and x<80:
        score='基本称职'
    elif x<75:
        score='不称职'
    return score

#将汇总计算好的数据写入Excel
def write2excle(exclefile,dataframe):
    writer = pd.ExcelWriter(exclefile)
    dataframe.to_excel(writer)
    writer.save()
    print('输出成功')

if __name__ == '__main__':
    readfile('民主评议\\')

#从文件中获取数据集
def getDataFromFile(filename='data/data.csv'):
    input_url = filename
    data_csv = pd.read_csv(input_url, ',', error_bad_lines=False)
    data_df = pd.DataFrame(data_csv)
    url_df = np.array(data_df)
    random.shuffle(url_df)
    y = [d[1] for d in url_df]
    inputurls = [d[0] for d in url_df]
    return inputurls,y

#分词
def getTokens(input):
    web_url = input.lower()
    urltoken = []
    dot_slash = []
    slash = str(web_url).split('/')
    for i in slash:
        r1 = str(i).split('-')
        token_slash = []
        for j in range(0, len(r1)):
            r2 = str(r1[j]).split('.')
            token_slash = token_slash + r2
        dot_slash = dot_slash + r1 + token_slash
    urltoken = list(set(dot_slash))
    if 'com' in urltoken:
        urltoken.remove('com')
    if 'cn' in urltoken:
        urltoken.remove('cn')
    return urltoken

all_urls,y=getDataFromFile(datapath)
url_vectorizer = TfidfVectorizer(tokenizer=getTokens)
x = url_vectorizer.fit_transform(all_urls)

#训练,通过逻辑回归模型训练
def trainLR(datapath):
    all_urls,y=getDataFromFile(datapath)
    url_vectorizer = TfidfVectorizer(tokenizer=getTokens)
    x = url_vectorizer.fit_transform(all_urls)
    x_train, x_test, y_train, y_test = train_test_split(x, y, test_size=0.2, random_state=42)
    l_regress = LogisticRegression()                  # Logistic regression
    l_regress.fit(x_train, y_train)
    l_score = l_regress.score(x_test, y_test)
    print("score: {0:.2f} %".format(100 * l_score))
    return l_regress,url_vectorizer

#训练，通过SVM支持向量机模型训练
def trainSVM(datapath):
    all_urls, y = getDataFromFile(datapath)
    url_vectorizer = TfidfVectorizer(tokenizer=getTokens)
    x = url_vectorizer.fit_transform(all_urls)
    x_train, x_test, y_train, y_test = train_test_split(x, y, test_size=0.2, random_state=42)
    svmModel=svm.LinearSVC()
    svmModel.fit(x_train, y_train)
    svm_score=svmModel.score(x_test, y_test)
    print("score: {0:.2f} %".format(100 * svm_score))
    return svmModel,url_vectorizer

#保存模型及特征
def saveModel(model,vector):
    #保存模型
    file1 = modelfile_path
    with open(file1, 'wb') as f:
        pickle.dump(model, f)
    f.close()
    #保存特征
    file2 = vectorfile_path
    with open(file2, 'wb') as f2:
        pickle.dump(vector, f2)
    f2.close()

if __name__ == '__main__':
    #model,vector=trainLR('data/data.csv')
    model, vector = trainSVM('data/data.csv')
    saveModel(model,vector)

#载入已经训练好的模型
def loadModel():
    file1 = modelfile_path
    with open(file1, 'rb') as f1:
        model = pickle.load(f1)
    f1.close()

    file2 = vectorfile_path
    with open(file2, 'rb') as f2:
        vector = pickle.load(f2)
    f2.close()
    return model,vector

#通过接口进行调用
@app.route('/<path:path>')
def show_predict(path):
    X_predict = []
    X_predict.append(path)
    model, vector = loadModel()
    x = vector.transform(X_predict)
    y_predict = model.predict(x)
    print(y_predict[0])
    return "url predict: "+str(y_predict[0])

import requests
from bs4 import BeautifulSoup
headers = {
    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36'
}

def getwhoisinfobychinafu(domain):
    ret_result = {}
    result=getWhoisInfoFromDB(domain)
    if len(result)==0:
        whois_service_url = 'http://whois.chinafu.com/whois.php'
        post_data={"domain":domain}

        try:
            post_result=requests.post(whois_service_url,post_data)
            if post_result.status_code == 200:
                ret_str = post_result.content.decode('utf-8')
                soup = BeautifulSoup(ret_str, 'lxml')
                items_tr =soup.find(name='table',attrs={'class':'listtable'}).find_all(name='tr')
                for item_tr in items_tr:
                    td_item=item_tr.find(name='td')
                    if 'colspan' in td_item.attrs:
                        key_name='详情'
                        key_value=td_item.find(name='div',id='tab1').text
                    else:
                        key_name=item_tr.find(name='th').text
                        key_value=item_tr.find(name='td').text
                    ret_result[key_name]=key_value
                addchinafuWhoisInfo2DB(ret_result)
        except Exception as r:
            print('未知错误 %s' % (r))
    #ret_result = json.dumps(ret_result, ensure_ascii=False)
    else:
        ret_result=result[0]
    return ret_result

def getWhoisInfoFromDB(domainname):
    whoisInfos=db.session.execute('select * from whoisinfo where domain_name="%s" and updated_time > DATE_SUB(CURDATE(), INTERVAL 1 WEEK)' % domainname).fetchall()
    whoisInfo_dics=[]
    for whoisInfo in whoisInfos:
        chinafuwhoisinfo_dic=chinafuwhoisinfo2dic(whoisInfo)
        whoisInfo_dics.append(chinafuwhoisinfo_dic)
    return whoisInfo_dics

def addchinafuWhoisInfo2DB(chinafuWhoisInfo_dic):
    chinafuWhois=WhoisInfo()
    chinafuWhois.domain_name=chinafuWhoisInfo_dic.get('域名DomainName')
    chinafuWhois.domain_status=chinafuWhoisInfo_dic.get('域名状态Domain Status','')
    chinafuWhois.registrar=chinafuWhoisInfo_dic.get('注册商Sponsoring Registrar','')
    chinafuWhois.name_server=chinafuWhoisInfo_dic.get('DNS 服务器Name Server','')
    chinafuWhois.registrar_creation_date=chinafuWhoisInfo_dic.get('注册日期Registration Date','')
    chinafuWhois.registrar_updated_date = chinafuWhoisInfo_dic.get('更新日期Update Date', '')
    chinafuWhois.registrar_expiry_date = chinafuWhoisInfo_dic.get('到期日期Expiration Date', '')
    chinafuWhois.detail=chinafuWhoisInfo_dic.get('详情', '')[0:10000]
    chinafuWhois.source = '中国福网'
    db.session.execute('delete from whoisinfo where domain_name="%s" and source="%s"' % (chinafuWhoisInfo_dic.get('域名DomainName'), chinafuWhois.source))
    db.session.add(chinafuWhois)
    db.session.commit()

def getIPbyDomain(domain):
    addr=''
    try:
        myaddr = socket.getaddrinfo(domain, 'http')
        addr=myaddr[0][4][0]
    except Exception as e:
        print(e)
    return addr

def getipinfobytaobao(ip):
    taobaoIp_url = 'https://ip.taobao.com/outGetIpInfo'
    post_data={"ip":ip,"accessKey":"alibaba-inc"}
    ret_ipinfo= {}
    try:
        return_data=requests.post(taobaoIp_url,post_data)
        #其中返回数据中code的值的含义为，0：成功，1：服务器异常，2：请求参数异常，3：服务器繁忙，4：个人qps超出
        return_json=json.loads(return_data.text)
        if return_json['code']==0:
            ret_ipinfo['ip']=return_json['data']['ip']
            ret_ipinfo['country']=return_json['data']['country']
            ret_ipinfo['region']=return_json['data']['region']
            ret_ipinfo['org']=''
            ret_ipinfo['city'] = return_json['data']['city']
            ret_ipinfo['isp']=return_json['data']['isp']
            ret_ipinfo['loc'] = ''
            ret_ipinfo['timezone'] = ''
            ret_ipinfo['source']='淘宝IP'
            addIPInfo2DB(ret_ipinfo)
    except Exception as e:
        print('未知错误 %s' % (e))
    return ret_ipinfo

def getipinfobyipinfo(ip):
    api_url='http://ipinfo.io/'+ip
    ipinfo = {}
    try:
        req_return = requests.get(api_url)
        if req_return.status_code == 200:
            ipinfo = json.loads(req_return.text)
            ipinfo['source']='ipinfo.io'
            addIPInfo2DB(ipinfo)
    except Exception as e:
        print('未知错误 %s' % (e))
    return ipinfo

def getipinfobyipapi(ip):
    api_url='http://ip-api.com/json/'+ip
    ipinfo={}
    try:
        req_return=requests.get(api_url)
        if req_return.status_code==200:
            ipinfo=json.loads(req_return.text)
            ipinfo['ip'] = ip
            ipinfo['source'] = 'ip-api.com'
            ipinfo['loc'] = str(ipinfo['lat'])+','+str(ipinfo['lon'])
            addIPInfo2DB(ipinfo)
    except Exception as e:
        print('未知错误 %s' % (e))
    return ipinfo

def getipinfobygeoip2(ip):
    ipinfo={}
    dbdir=Config.geoLiteDBdir
    with geoip2.database.Reader(dbdir) as reader:
        response = reader.city(ip)
        ipinfo['ip'] =ip
        ipinfo['country'] = response.country.names['zh-CN']
        ipinfo['region'] =''
        ipinfo['city']=response.city.name
        ipinfo['org'] =''
        ipinfo['loc'] = str(response.location.latitude)+','+str(response.location.longitude)
        ipinfo['timezone'] = response.location.time_zone
        ipinfo['source'] = 'GeoIP'
        addIPInfo2DB(ipinfo)
    return ipinfo

DEV_DATABASE_URI = 'mysql+pymysql://dbuser:yourpassword@127.0.0.1:3306/infocol_db_dev?charset=utf8'
TEST_DATABASE_URI = 'mysql+pymysql://dbuser:yourpassword@127.0.0.1:3306/infocol_db_test?charset=utf8'
PROD_DATABASE_URI = 'mysql+pymysql://dbuser:yourpassword@127.0.0.1:3306/infocol_db?charset=utf8'

SQLALCHEMY_TRACK_MODIFICATIONS = True
SECRET_KEY=your secret key

FLASK_ENV=development

import os
from dotenv import load_dotenv
basedir=os.path.abspath(os.path.dirname(__file__))
flaskenv_path=os.path.join(basedir,'.flaskenv')
env_path=os.path.join(basedir,'.env')
if os.path.exists(flaskenv_path):
    load_dotenv(flaskenv_path)
if os.path.exists(env_path):
    load_dotenv(env_path)

class Config:
    geoLiteDBdir=os.path.join(basedir,'GeoLite2\GeoLite2-City.mmdb')
    flaskenv = os.getenv('FLASK_ENV','development')
    SECRET_KEY=os.getenv('SECRET_KEY','123!@#')
    SQLALCHEMY_TRACK_MODIFICATIONS=os.getenv('SQLALCHEMY_TRACK_MODIFICATIONS')
    SQLALCHEMY_DATABASE_URI = os.getenv('DEV_DATABASE_URI')
    @staticmethod
    def init_app(app):
        pass

class DevelopmentConfig(Config):
    DEBUG=True
    SQLALCHEMY_DATABASE_URI = os.getenv('DEV_DATABASE_URI')

class TestingConfig(Config):
    TESTING=True
    SQLALCHEMY_DATABASE_URI = os.getenv('TEST_DATABASE_URI')

class ProductionConfig(Config):
    SQLALCHEMY_DATABASE_URI = os.getenv('PROD_DATABASE_URI')

config={
    'development':DevelopmentConfig,
    'testing':TestingConfig,
    'production':ProductionConfig,
    'default':DevelopmentConfig
}

@index_bp.route('/',methods=['GET','POST'])
def index():
    name = ''
    ipinfos = []
    whois_info = ''
    form = InputForm()
    if form.validate_on_submit():
        name = form.name.data
        if checkip(name):
            ipinfos = getipinfo(name)
        else:
            whois_info = getwhoisinfo(name)
            whois_ip = getIPbyDomain(name)
            if checkip(whois_ip):
                ipinfos = getipinfo(whois_ip)
        form.name.data = ''
    return render_template('index.html',form=form, name=name, ipinfos=ipinfos, whois_info=whois_info)