import time from datetime import datetime from fastapi import FastAPI, Form, Request from fastapi.templating import Jinja2Templates from fastapi.middleware.cors import CORSMiddleware from pydantic import BaseModel from oauth2client.service_account import ServiceAccountCredentials import os, dataset, json, gspread from gspread_pandas import Spread, Client import pandas as pd app = FastAPI() directory=os.path.dirname(os.getcwd()) templates = Jinja2Templates(directory) origins = [ "*" ] app.add_middleware( CORSMiddleware, allow_origins=origins, allow_credentials=True, allow_methods=["*"], allow_headers=["*"], ) @app.get("/") async def read_root(request: Request): return templates.TemplateResponse("index.html",{"request": request}) @app.post("/submitform") async def formentry(name: str = Form(...), phone: str = Form(...), loc: str = Form(...), course_name: str = Form(...), bookdate: str = Form(...), booktime: str = Form(...)): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/violetbeauty?charset=utf8mb4') request_table = db['booking_info'] time_stamp = datetime.fromtimestamp(time.time()) time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S") print(time_stamp) request_table.insert({"name": name, "phone": phone, "location": loc, "course_name":course_name, "book_date": bookdate, "book_time": booktime, "time_stamp": time_stamp}) return 0 # pls make booking success page # return templates.TemplateResponse("(bookingsuccess.html)",{"request": request}) @app.get("/getbooking_data") async def getdata(): db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/violetbeauty?charset=utf8mb4') statement = 'SELECT * FROM booking_info ORDER BY time_stamp DESC' df = pd.DataFrame() for row in db.query(statement): date_format = "%Y-%M-%d %H:%M:%S" fdate = row['time_stamp'].strftime('%Y-%m-%d') df = df.append({'填單時間':row['time_stamp'],'姓名':row['name'],'電話':str('\'' + row['phone']),'預約體驗店家':row['location'],'選擇課程':row['course_name'],'預約日期':row['book_date'],'預約時間':row['book_time']}, ignore_index=True) save_sheet(df,'紫晴預約資料','預約資料') return '資料下載完成' 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('violet-beauty-booking-data-8ef5f0d9a747.json', scope) gc = gspread.authorize(credentials) spread = Spread(filename,creds=credentials) spread.df_to_sheet(df, index=False, sheet=tabname, start=startpos, replace=False) time_stamp = datetime.fromtimestamp(time.time()) time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S") spread.sheet.update('I1', '最後更新:' + time_stamp)