from enum import Enum from typing import Optional from pydantic import BaseModel from fastapi import FastAPI, Query, UploadFile, File import dataset,json from fastapi import FastAPI from fastapi.middleware.cors import CORSMiddleware from fastapi.staticfiles import StaticFiles import time from datetime import datetime from PIL import Image import io from typing import List import threading app = FastAPI() origins = [ "http://172.105.205.52", "http://172.105.205.52:8000", ] app.add_middleware( CORSMiddleware, allow_origins=['*'], allow_credentials=True, allow_methods=["*"], allow_headers=["*"], ) serverADDR = 'welife.asia:8002/' cover_img_dir = 'static/cover_img/' app.mount("/static/cover_img", StaticFiles(directory="static/cover_img"), name="static/cover_img") ##########TAG CRUD################TAG CRUD##################TAG CRUD###########TAG CRUD################## class Tag(BaseModel): id: int name: str @app.get("/tags") async def get_tags(): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') statement = 'SELECT id,name FROM tag' tags = [] for row in db.query(statement): tags.append({'id':row['id'],'name':row['name']}) return tags @app.post("/create_tag") async def create_tags(tag:Tag): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') tag_table = db['tag'] pk = tag_table.insert({'name':tag.name}) tag.id = pk return tag @app.get("/del_tag/{tag_id}") async def delete_tags(tag_id:int): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') sqls = 'DELETE FROM tag WHERE id = '+str(tag_id) db.query(sqls) sqls = 'DELETE FROM course_tag WHERE tag_id = '+str(tag_id) db.query(sqls) return 'success' @app.post("/update_tag/") async def update_tags(tag_id,tag:Tag): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') sqls = 'UPDATE tag\ SET name="'+tag.name+'"\ WHERE id='+tag_id+';' db.query(sqls) tag.id=tag_id return tag @app.get("/tags/{tag_id}") async def get_tags(tag_id): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') statement = 'SELECT id,name FROM tag where id = '+tag_id for row in db.query(statement): result = {'id':row['id'],'name':row['name']} return result ###########COURSE CRUD###########COURSE CRUD###########COURSE CRUD###########COURSE CRUD########### class Course(BaseModel): id: int title: str profile: str url: str #course website location url cover_img: str #img url form teacher_name: str teacher_img: str #teacher user image url form price: int price_discount: int hours: float units: int @app.post("/get_courses_by_cid") async def get_courses(cids:List[int]): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') cid_str = '(' for t in cids: cid_str = cid_str + str(t) +',' cid_str = cid_str[:-1]+')' courses = [] statement = 'SELECT * FROM course WHERE id IN'+cid_str for row in db.query(statement): courses.append({'id':row['id'],'title':row['title'],'url':row['url'] ,'teacher_name':['teacher_name'],'price':row['price'],'price_discount':row['price_discount'],'profile':row['profile'] ,'cover_img':'https://'+serverADDR + row['cover_img'],'hours':row['hours'],'units':row['units']}) return courses @app.post("/get_courses") async def get_courses(tags:List[int]): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') tag_str = '(' for t in tags: tag_str = tag_str + str(t) +',' tag_str = tag_str[:-1]+')' courses = [] statement = 'SELECT * FROM course_tag '\ 'INNER JOIN course on course_tag.course_id=course.id '\ 'INNER JOIN tag on course_tag.tag_id = tag.id '\ 'WHERE tag.id IN'+tag_str+' ORDER BY clicks DESC' for row in db.query(statement): courses.append({'id':row['course_id'],'title':row['title'],'url':row['url'] ,'teacher_name':['teacher_name'],'price':row['price'],'price_discount':row['price_discount'],'profile':row['profile'] ,'cover_img':'https://'+serverADDR + row['cover_img'],'hours':row['hours'],'units':row['units']}) return courses @app.post("/create_courses") async def create_courses(tags: list,course:Course): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') #add to course table course_table = db['course'] pk = course_table.insert({'title':course.title,'profile':course.profile,'url':course.url,'cover_img':course.cover_img,'teacher_name':course.teacher_name, 'price':course.price,'price_discount':course.price_discount,'hours':course.hours,'units':course.units}) course.id = pk course_tag_table = db['course_tag'] for tag in tags: course_tag_table.insert({'course_id':course.id,'tag_id':tag}) x = threading.Thread(target=to_sheet) x.start() return {'msg':'新增成功'} @app.get("/del_course/{course_id}") async def delete_course(course_id:int): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') sqls = 'DELETE FROM course WHERE id = '+str(course_id) db.query(sqls) sqls = 'DELETE FROM course_tag WHERE course_id = '+str(course_id) db.query(sqls) return {'msg':'新增成功'} @app.post("/upload_cover_img/") async def create_upload_file(file: UploadFile = File(...)): img_name = str(time.time()).replace('.','') contents = await file.read() image = Image.open(io.BytesIO(contents)) image= image.convert("RGB") image.save(cover_img_dir+img_name+'.jpg') return {"msg": serverADDR+cover_img_dir+img_name+'.jpg'} async def to_sheet(): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4') statement = 'SELECT * FROM course ORDER BY clicks DESC' df = pd.DataFrame() for row in db.query(statement): date_format = "%Y-%M-%d %H:%M:%S" df = df.append({'serial id':row['id'],'標題':['Title'],'介紹':row['profile'],'url':row['url'] ,'圖片':row['cover_img'],'價錢':row['price'],"折扣後價錢":row['price_discount'],'時數':row['hours'] ,'單元數':row['units']}, ignore_index=True) save_sheet(df,'deco_request','sheet1') def save_sheet(df,filename,tabname,startpos='A1'): scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] credentials = ServiceAccountCredentials.from_json_keyfile_name('spread2.json', scope) gc = gspread.authorize(credentials) spread = Spread(filename,creds=credentials) spread.df_to_sheet(df, index=False, sheet=tabname, start=startpos, replace=False)