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')