pip install jieba -i https://nx.linktimecloud.com/repository/pypi-group/simple/
pip install pymysql -i https://nx.linktimecloud.com/repository/pypi-group/simple/
pip install mysql-connector==2.2.9 -i https://nx.linktimecloud.com/repository/pypi-group/simple/
import warnings
warnings.filterwarnings('ignore')
import numpy as np
import pandas as pd
import jieba, re, random, json

import mysql.connector

from sklearn import feature_extraction
from sklearn.feature_extraction.text import TfidfTransformer 
from sklearn.feature_extraction.text import CountVectorizer
from sklearn.feature_extraction.text import HashingVectorizer


from sqlalchemy import create_engine
import pymysql

#table 
table = 'biz_covid19_articles'
#db 
host = 'xxxxxxxxxx'
user = 'xxxxxxxxxx'
password = 'xxxxxxxxxx'
database = 'xxxxxxxxxx'
def write_data(year):
    mydb = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database)

    mycursor = mydb.cursor()
    mycursor.execute(f"SELECT * FROM {table} where type = '1' and substr(publish_time, 1, 4)={year} limit 50 ")
    myresult = mycursor.fetchall()     # fetchall() 获取所有记录
    mycursor.close()
    mydb.close()


    cut_words = []

    for x in myresult:
        seg_list = jieba.cut(x[3],cut_all=False)
        line = ""
        for seg in seg_list:
            line = line + " " + seg
        cut_words.append(line.strip())

    #print(len(cut_words))
    # 将文本中的词语转换为词频矩阵 矩阵元素a[i][j] 表示j词在i类文本下的词频
    vectorizer = CountVectorizer()
    # 该类会统计每个词语的tf-idf权值
    transformer = TfidfTransformer()
    # 第一个fit_transform是计算tf-idf 第二个fit_transform是将文本转为词频矩阵
    tfidf = transformer.fit_transform(vectorizer.fit_transform(cut_words))
    # 获取词袋模型中的所有词语
    word = vectorizer.get_feature_names()
    # 将tf-idf矩阵抽取出来 元素w[i][j]表示j词在i类文本中的tf-idf权重
    weight = tfidf.toarray()

    #print('Start Kmeans:')
    from sklearn.cluster import KMeans
    clf = KMeans(n_clusters=2)
    pre = clf.fit_predict(weight)
    #print(pre)
    #中心点
    #print(clf.cluster_centers_)
    #print(clf.inertia_)

    from sklearn.decomposition import PCA
    pca = PCA(n_components=2) #输出两维
    newData = pca.fit_transform(weight) #载入N维

    x = [n[0] for n in newData]
    y = [n[1] for n in newData]

    result = []
    i = 0
    for new in newData:
        x_value = round(new[0], 3)
        y_value = round(new[1], 3)
        temp = [x_value,y_value, int(pre[i])]
        result.append(temp)
        i += 1




    
    lst = np.array(result).reshape(len(result), 3).tolist()
    point = [[x[0], x[1], int(x[2])] for x in lst]
  

    
        
    return point
    
sqlEngine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:3306/{database}')
dbConnection = sqlEngine.connect()

rslt = []
for yr in [2020, 2021]:#
    print(yr)
    df = pd.DataFrame(columns=['point', 'year'])
    df.loc[0, 'point'] = [i for i in write_data(yr)[:200]]
    df['year'] = yr
    rslt.append(df)
df = pd.concat(rslt, ignore_index=True)
df['point'] = df['point'].apply(lambda x: json.dumps(x))
frame = df[['point', 'year']].to_sql('ads_cov19_kms', dbConnection, index=False, if_exists='replace')
dbConnection.close()

pip install jieba -i https://nx.linktimecloud.com/repository/pypi-group/simple/
pip install pymysql -i https://nx.linktimecloud.com/repository/pypi-group/simple/
pip install mysql-connector==2.2.9 -i https://nx.linktimecloud.com/repository/pypi-group/simple/
import warnings
warnings.filterwarnings('ignore')
import numpy as np
import pandas as pd
import jieba, re, random, json
import jieba.analyse
from re import match


import mysql.connector

from sklearn import feature_extraction
from sklearn.feature_extraction.text import TfidfTransformer 
from sklearn.feature_extraction.text import CountVectorizer
from sklearn.feature_extraction.text import HashingVectorizer


from sqlalchemy import create_engine
import pymysql

#table 
table = 'biz_covid19_articles'
#db 
host = 'xxxxxxxxxx'
user = 'xxxxxxxxxx'
password = 'xxxxxxxxxx'
database = 'xxxxxxxxxx'
def write_data(year):
    c = {}

    mydb = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database)

    mycursor = mydb.cursor()
    mycursor.execute(f"SELECT * FROM {table} where type = '1' and substr(publish_time, 1, 4)={year} limit 100")
    myresult = mycursor.fetchall()     # fetchall() 获取所有记录
    mycursor.close()
    mydb.close()

                
    result = []
    cut_words = ""

    r='[\W]'
    list = ['，','。','、',]    
    for x in myresult:
        seg_list = jieba.cut(x[3],cut_all=False)
        for seg in seg_list:
            if len(seg) > 1 and match(r,seg) is None:
                cut_words = cut_words + " " + seg
                if seg in c:
                    c[seg] +=1
                else:
                    c[seg] = 1
                            
    for key in c.keys():
        temp = (key,c[key])
        result.append(temp)  

    

    df = pd.DataFrame(result, columns=['word', 'count']) 
    df['year'] = year

        

        
    return df

sqlEngine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:3306/{database}')
dbConnection = sqlEngine.connect()

rslt = []
for yr in [2020, 2021]:
    print(yr)
    data = write_data(yr)
    rslt.append(data)
df = pd.concat(rslt, ignore_index=True)
frame = df.to_sql('ads_cov19_wordcount', dbConnection, index=False, if_exists='replace')
dbConnection.close()

pip install jieba -i https://nx.linktimecloud.com/repository/pypi-group/simple/
pip install pymysql -i https://nx.linktimecloud.com/repository/pypi-group/simple/
pip install mysql-connector==2.2.9 -i https://nx.linktimecloud.com/repository/pypi-group/simple/
import warnings
warnings.filterwarnings('ignore')
import numpy as np
import pandas as pd
import jieba, re, random, json
import jieba.analyse
from re import match


import mysql.connector

from sklearn import feature_extraction
from sklearn.feature_extraction.text import TfidfTransformer 
from sklearn.feature_extraction.text import CountVectorizer
from sklearn.feature_extraction.text import HashingVectorizer


from sqlalchemy import create_engine
import pymysql

#table 
table = 'biz_covid19_articles'
#db 
host = 'xxxxxxxxxx'
user = 'xxxxxxxxxx'
password = 'xxxxxxxxxx'
database = 'xxxxxxxxxx'
def write_data(year):
    c = {}

    mydb = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database)

    mycursor = mydb.cursor()
    mycursor.execute(f"SELECT * FROM {table} where type = '1' and substr(publish_time, 1, 4)={year} limit 100")
    myresult = mycursor.fetchall()     # fetchall() 获取所有记录
    mycursor.close()
    mydb.close()

                
    result = []
    cut_words = ""

    r='[\W]'
    list = ['，','。','、',]    
    for x in myresult:
        seg_list = jieba.cut(x[3],cut_all=False)
        for seg in seg_list:
                if len(seg) > 1 and match(r,seg) is None:
                    cut_words = cut_words + " " + seg
                    if seg in c:
                        c[seg] +=1
                    else:
                        c[seg] = 1

    keywords = jieba.analyse.extract_tags(cut_words,
                                            topK=50,
                                            withWeight=True,
                                            allowPOS=('a','e','n','nr','ns', 'v')) #词性 形容词 叹词 名词 动词

    result_keyword = []
    for keyword in keywords:
        temp_keyword = (keyword[0],keyword[1])
        result_keyword.append(temp_keyword)

 

    df = pd.DataFrame(result_keyword, columns=['word', 'score']) 
    df['year'] = year

        

        
    return df

sqlEngine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:3306/{database}')
dbConnection = sqlEngine.connect()

rslt = []
for yr in [2020, 2021]:
    print(yr)
    data = write_data(yr)
    rslt.append(data)
df = pd.concat(rslt, ignore_index=True)
frame = df.to_sql('ads_cov19_cluster', dbConnection, index=False, if_exists='replace')
dbConnection.close()