def __init__(self, user_id=12):
try:
with ssh.Tunnel() as tunnel:
with connect.Connect(port=tunnel.local_bind_port) as conn:
sql1 = "select * from analysis_tour where uid = {}".format(
user_id)
self.analy_df = pd.read_sql_query(sql1, conn)
self.analy_df.drop_duplicates(['TID'], inplace=True)
sql2 = "select * from crawling_tour"
self.tour_df = pd.read_sql_query(sql2, conn)
sql3 = "select * from point"
self.base_df = pd.read_sql_query(sql3, conn)
except Exception as e:
print(e)
def save(df, base_address):
try:
with ssh.Tunnel() as tunnel:
with connect.Connect() as conn:
cur = conn.cursor()
now = time.strftime('%y%m%d%H%M%S',
time.localtime(time.time()))
group_no = f"{now}"
x = 0
print("UID | TID | SCHEDULE_NAME | DATE | GROUP_NO | TIME")
while x < len(df.index):
sql = f"insert into schedule (UID, TID, SCHEDULE_NAME, DATE, GROUP_NO, TIME) value ({df.loc[x]['UID']},{df.loc[x]['TID']},'{base_address}','{df.loc[x]['DATE']}','{group_no}',{df.loc[x]['TIME']})"
cur.execute(sql)
conn.commit()
x += 1
except Exception as e:
print(e)
return -1
finally:
return 1
from Connection import connect, ssh
import pandas as pd
from sklearn.metrics.pairwise import cosine_similarity
with ssh.Tunnel() as tunnel:
with connect.Connect(port=tunnel.local_bind_port) as conn:
sql = "select * from analysis_tour"
a_df = pd.read_sql_query(sql, conn)
sql = "select * from crawling_tour"
t_df = pd.read_sql_query(sql, conn)
print("==Data Frame Ready==")
#ratings_matrix = a_df.pivot_table('GRADE',index='UID',columns='TID')
#print(ratings_matrix.head(3))
rating_place = pd.merge(a_df, t_df, on='TID')
ratings_matrix = rating_place.pivot_table('GRADE', index='UID', columns='TID')
ratings_matrix = ratings_matrix.fillna(0)
ratings_matrix_T = ratings_matrix.transpose()
print("==Matrix Ready==")
#print(ratings_matrix_T.head(3))
item_sim = cosine_similarity(ratings_matrix_T, ratings_matrix_T)
# cosine_similarity() 로 반환된 넘파이 행렬을 영화명을 매핑해 Dataframe으로 변환
item_sim_df = pd.DataFrame(data=item_sim, index=ratings_matrix.columns, columns=ratings_matrix.columns)
print("==Similarity sort ok==")
item_sim_df.to_csv('item_sim.csv', mode='w', encoding='utf-8-sig')