from fastapi.responses import JSONResponse # <--- [추가] from fastapi import FastAPI from pydantic import BaseModel from mistral_inference.transformer import Transformer from mistral_inference.generate import generate from mistral_common.tokens.tokenizers.mistral import MistralTokenizer from pathlib import Path import uvicorn import pymysql import re # --------------------------------------------------------- # [설정 1] 데이터베이스 연결 정보 # --------------------------------------------------------- DB_HOST = "qst-s.iptime.org" DB_PORT = 33063 DB_USER = "ai_read_only" DB_PASSWORD = "qsentech!1233" DB_NAME = "paradise" # --------------------------------------------------------- # [설정 2] 모델 로드 및 클래스 찾기 # --------------------------------------------------------- try: from mistral_common.protocol.instruct.messages import ChatCompletionRequest, UserMessage, SystemMessage except ImportError: try: from mistral_common.protocol.instruct.request import ChatCompletionRequest from mistral_common.protocol.instruct.messages import UserMessage, SystemMessage except ImportError: import mistral_common.protocol.instruct.messages as msg_module UserMessage = msg_module.UserMessage SystemMessage = msg_module.SystemMessage pass mistral_models_path = Path.home().joinpath('mistral_models', '7B-Instruct-v0.3') tokenizer_path = mistral_models_path / "tokenizer.model.v3" print("=== 모델 로딩 중 ===") tokenizer = MistralTokenizer.from_file(str(tokenizer_path)) model = Transformer.from_folder(mistral_models_path) print("=== 준비 완료 ===") app = FastAPI() class ChatRequest(BaseModel): prompt: str max_tokens: int = 1024 # --------------------------------------------------------- # [Helper] 모델 호출 함수 # --------------------------------------------------------- def ask_mistral(messages, max_tokens=1024, temperature=0.1): chat_request = ChatCompletionRequest(messages=messages) tokens = tokenizer.encode_chat_completion(chat_request).tokens out_tokens, _ = generate( [tokens], model, max_tokens=max_tokens, temperature=temperature, eos_id=tokenizer.instruct_tokenizer.tokenizer.eos_id ) return tokenizer.instruct_tokenizer.tokenizer.decode(out_tokens[0]) # --------------------------------------------------------- # [프롬프트 정의] # --------------------------------------------------------- # ▼▼▼ [수정된 부분] AI에게 정확한 카테고리 족보를 줍니다 ▼▼▼ def get_sql_prompt(): return """ You are a SQL expert. Convert the user's question into a MariaDB SQL query. [Schema] Table: tbl_alarm_info Table COMMENT: DID 알람 정보 - alarmId (varchar(10)): Digital information display alarm id - alarmDesc (text): Digital information display alarm description Table: tbl_alarm_log Table COMMENT: DID 알람 이력 - alarmId (varchar(10)): Digital information display alarm id - alarmRepairDesc (text): Digital information display repair description - alarmDate (datetime): Digital information display alarm date - didId (varchar(10)): Digital information display id Table: tbl_casino_info Table COMMENT: Casino 정보 - casinoId (varchar(10)): Casino id - casinoName (carchar(50)): Casino name Table: tbl_did_info Table COMMENT: DID 정보 - didId (varchar(10)): Digital information display id - didType (char(1)): Digital information display type - didName (varchar(50)): Digital information display name - didDesc (text): Digital information display description - didIpAddr (varchar(20)): Digital information display ip address - eMapId (varchar(10)): eMap id - eMapAxis (varchar(20)): eMap Axis - clientId (varchar(10)): Screen client software id - didIconId (varchar(10)): Digital information display icon id - imgPath (varchar(100)): Screen client's capture image path - didStatusIconId (varchar(10)): Digital information display status icon id - didStatus (char(10)): Digital information display status Table: tbl_emap_info Table COMMENT: DID eMAP 대시보드 이미지 정보 - eMapId (varchar(10)): eMap id - eMapName (varchar(50)): eMap name - eMapFileName (varchar(100)): eMap file name - eMapVer (varchar(20)): eMap Version - useYn (char(1)): use check (default Y) Table: tbl_file_info Table COMMENT: 파일 정보 - fileId (bigint(20)): File id - fileName (varchar(100)): File name - fileType (varchar(1)): File type - fileCapacity (varchar(10)): File capacity - fileSize (varchar(20)): File size - fileDesc (text): File size Table: tbl_game_info Table COMMENT: 카지노 게임 정보 - gameId (varchar(10)): Casino game slot id - gameName (varchar(50)): Casino game slot name - iconId (varchar(10)): Casino game icon id Table: tbl_group_info Table COMMENT: Jackpot group information - groupId (varchar(10)): Jackpot group id - groupName (varchar(50)): Jackpot group name - groupType (char(1)): Jackpot group type Table: tbl_icon_info Table COMMENT: DID eMAP 대시보드 아이콘 정보 - iconId (varchar(10)): Digital information display dashboard icon id - iconName (varchar(50)): Digital information display dashboard icon name - iconFileName (varchar(100)): Digital information display dashboard icon file name - didType (char(1)): Digital information display type - didStatus (char(10)): Digital information display status Table: tbl_jackpot_hit Table COMMENT: 실시간 잭팟 당첨 정보 - jackpotId (varchar(10)): Hit jackpot id - groupId (varchar(10)): Jackpot group id - casinoId (varchar(10)): Casino id - machineId (varchar(10)): Hit slot machine or table id - spotNo (varchar(10)): Table game's spot number - gameId (varchar(10)): Table or game slot id - typeId (char(1)): Jackpot typeId - sizeId (char(1)): Jackpot sizeId - mwId (varchar(10)): Middleware agent id - hitPrize (double): Hit jackpot money - upTime (datetime): Hit jackpot time Table: tbl_jackpot_hitlog Table COMMENT: 실시간 잭팟 당첨 이력 - jackpotId (varchar(10)): Hit jackpot id - groupId (varchar(10)): Jackpot group id - casinoId (varchar(10)): Casino id - machineId (varchar(10)): Hit slot machine or table id - spotNo (varchar(10)): Table game's spot number - gameId (varchar(10)): Table or game slot id - typeId (char(1)): Jackpot typeId - sizeId (char(1)): Jackpot sizeId - mwId (varchar(10)): Middleware agent id - hitPrize (double): Hit jackpot money - upTime (datetime): Hit jackpot time Table: tbl_jackpot_info Table COMMENT: 실시간 잭팟 적립 정보 - jackpotId (varchar(10)): Accumulated jackpot id - groupId (varchar(10)): Jackpot group id - casinoId (varchar(10)): Casino id - mwId (varchar(10)): Middleware agent id - prizeCurrency (double): Accumulated jackpot money - minPrize (double): Minimum jackpot money - maxPrize (double): Maximum jackpot money - typeId (varchar(10)): Jackpot typeId - sizeId (varchar(10)): Jackpot sizeId - delaySec (int(11)): The time stored in the tbl_jackpot_hit table - upTime (datetime): Lastest accumulated jackpot money update iime Table: tbl_jpc_info Table COMMENT: Jackpot controller information - jpcId (varchar(10)): Jackpot controller id - jpcName (varchar(50)): Jackpot controller name - product (varchar(100)): Jackpot controller product company Table: tbl_jpsize_info Table COMMENT: Jackpot size information - sizeId (varchar(10)): Jackpot size id - sizeName (varchar(50)): Jackpot size name Table: tbl_jptype_info Table COMMENT: Jackpot type information - typeId (varchar(10)): Jackpot type id - typeName (varchar(50)): Jackpot type name Table: tbl_machine_info Table COMMENT: Casino slot machine information - machineId (varchar(10)): Casino slot machine id - gameId (varchar(10)): Casino game id - jpcId (varchar(10)): Jacppot controller id - eMapId (varchar(10)): eMap id - eMapAxis (varchar(50)): eMap Axis - useYn (char(1)): Machine use yn Table: tbl_middleware_info Table COMMENT: Middleware agent information - mwId (varchar(10)): Middleware agent id - mwName (varchar(50)): Middleware agent name - mwConfigInfo (longtext): Middleware config information - mwVer (varchar(20)): Middleware agent file version - useYn (char(1)): use check (default Y) Table: tbl_playlist_info Table COMMENT: 스크린 플레이어 플레이 리스트 정보 - playlistId (varchar(10)): Play list id - plName (varchar(50)): Play list name - hitScrOn (char(1)): Hit screen view on/off - hitGroupId (varchar(10)): Hit jackpot group id - hitJackpotId (varchar(10)): Hit jackpot id - hitScrId (varchar(10)): Hit screen id - useYn (char(1)): use check (default Y) Table: tbl_property_info Table COMMENT: 스크린 속성 정보 - tagNo (bigint(20)): Tag number - fontName (varchar(50)): Tag font name - fontSize (double): Tag font size - fontColor (varchar(10)): Tag font color - baseColor (varchar(10)): Tag background color - db_GroupId (varchar(10)): Jackpot group id - db_JackpotId (varchar(10)): Jackpot id - db_FieldName (varchar(50)): Db field name - db_FieldType (varchar(50)): Db field type - propertyValue (varchar(100)): Property value - valuePercent (int(3)): Property value percent - animateOn (char(1)): Jackpot money animate on/off - gridMaxRow (int(11)): Jackpot information grid max row - useFormat (char(1)): propertyValue field value's replace flag Table: tbl_resource_info Table COMMENT: 웹 서버 리소스 정보 - resName (varchar(50)): Resource name - resValue (varchar(50)): Resource value Table: tbl_scr_client Table COMMENT: 스크린 클라이언트 프로그램 정보 - clientId (varchar(10)): Software id - playlistId (varchar(10)): Screen player's play list id - runSw (varchar(100)): Running exe file name - exitKey (varchar(10)): Running exe file's exit keyboard value - runKey (varchar(10)): Exiting exe file's run keyboard value - captureFileName (varchar(100)): SMB saved capture file name - connStatus (char(1)): Connection status - useYn (char(1)): use check (default Y) Table: tbl_scr_info Table COMMENT: 스크린 정보 - scrId (varchar(10)): Screen id - playlistId (varchar(10)): Screen player's playlist id - scrPos_X (double): Screen X coordinates position - scrPos_Y (double): Screen Y coordinates position - scrPos_Width (double): Screen width - scrPos_Height (double): Screen height - runTime (int(11)): Screen loding time - runFileName (varchar(100)): Screen backgorund media file name - sound (char(1)): Screen sound on/off - transparency (int(3)): Screen font transparency - runNo (int(11)): Play list running order Table: tbl_table_info Table COMMENT: 카지노 테이블 정보 - tableId (varchar(10)): Casino table id - tableName (varchar(50)): Casino table name - clientId (varchar(10)): Screen client's software id - tableStatus (char(1)): Casino table status - gameId (varchar(10)): Casino game id - eMapAxis (varchar(20)): eMapAxis - useYn (char(1)): use check (default Y) Table: tbl_tag_info Table COMMENT: 스크린 태그 정보 - tagNo (bigint(20)): Tag number auto increment value - scrId (varchar(10)): Screen id - tagType (int(3)): Screen tag type - tagName (varchar(50)): Screen tag name - pos_X (double): Screen tag X coordinates position - pos_Y (double): Screen tag Y coordinates position - pos_Width (double): Screen tag width - pos_Height (double): Screen tag height - transparency (int(3)): Screen tag background transparency - alignment (char(1)): Screen tag alignment - refTagNo (bigint(20)): Field tagType no.5 reference tag id - useLogDb (char(1)): Table tbl_jackpot_hitlog use flag Table: tbl_user_info Table COMMENT: 사용자 정보 - userId (varchar(20)): User login id - userPw (varchar(100)): User login password - userLevel (char(1)): User authority level - userName (varchar(50)): User name - casinoId (varchar(10)): User casino id - employeeId (varchar(30)): User employee id - officeCode_01 (varchar(10)): User office affiiation 01 - officeCode_02 (varchar(10)): User office affiiation 02 - officeCode_03 (varchar(10)): User office affiiation 03 - officeCode_04 (varchar(10)): User office affiiation 04 - tel_01 (varchar(20)): User telephone 01 - tel_02 (varchar(20)): User telephone 02 - connStatus (char(1)): User connection status - useYn (char(1)): use check (default Y) [Rules] 1. Output ONLY the SQL query inside a code block (```sql ... ```). 2. Use SELECT statement only. 3. [IMPORTANT] You MUST use Korean aliases for all columns in the SELECT clause. - Use the format: column_name AS 'Korean_Name' - Example: SELECT userName AS '이름', userLevel AS '권한' FROM tbl_user_info; 4. [CRITICAL] When searching for a user information, ALWAYS check both 'userId' and 'userName' columns using 'OR'. - The user input could be an ID or a Name. You must check both to be sure. - Syntax: WHERE (userId = 'INPUT_VALUE' OR userName = 'INPUT_VALUE') [Examples] User: "홍길동 전화번호 알려줘" SQL: ```sql SELECT userName AS '이름', tel_01 AS '전화번호' FROM tbl_user_info WHERE userId = '홍길동' OR userName = '홍길동'; ``` """ # ▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲ def get_general_prompt(): return "You are a helpful AI assistant. Answer kindly in Korean." # --------------------------------------------------------- # [기능] SQL 실행 함수 # --------------------------------------------------------- def execute_sql_query(sql: str): try: conn = pymysql.connect( host=DB_HOST, port=DB_PORT, user=DB_USER, password=DB_PASSWORD, database=DB_NAME, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) with conn: with conn.cursor() as cursor: cursor.execute(sql) result = cursor.fetchall() return list(result) except Exception as e: return f"SQL_ERROR: {str(e)}" # --------------------------------------------------------- # [필수 추가] 에러 발생 시 자연어 변환 프롬프트 # --------------------------------------------------------- def get_error_response_prompt(user_input, error_type, detail_msg=""): return f""" The user asked: "{user_input}" Situation: - We tried to search the database but found nothing or an error occurred. - Status: {error_type} - Detail: {detail_msg} Task: - Write a SHORT, kind, and helpful response in Korean explaining the situation. - If Status is 'EMPTY', say "searched for it but couldn't find any matching data." - If Status is 'ERROR', say "an internal error occurred while searching." - Do NOT mention technical details (SQL, column names) to the user. """ # --------------------------------------------------------- # [API 엔드포인트] - 무조건 SQL 모드로 동작하게 수정됨 # --------------------------------------------------------- @app.post("/chat") async def chat_endpoint(request: ChatRequest): try: user_input = request.prompt print(f">> 질문: {user_input}") print(">> 모드: 강제 SQL 실행") # ------------------------------------------------------- # 1. SQL 생성 # ------------------------------------------------------- sql_messages = [ SystemMessage(content=get_sql_prompt()), UserMessage(content=f"Question: {user_input}\nSQL Query:") ] # SQL 생성은 길게 허용 (2048) generated_text = ask_mistral(sql_messages, max_tokens=2048, temperature=0.1) # 정규식으로 SQL 추출 match = re.search(r"```(sql)?(.*?)```", generated_text, re.DOTALL | re.IGNORECASE) if match: clean_sql = match.group(2).strip() else: clean_sql = generated_text.strip() if "select" in clean_sql.lower(): clean_sql = clean_sql[clean_sql.lower().find("select"):] if ";" in clean_sql: clean_sql = clean_sql.split(";")[0] + ";" print(f">> 추출된 SQL: {clean_sql}") # ------------------------------------------------------- # 2. SQL 유효성 검사 실패 시 # ------------------------------------------------------- if not clean_sql.lower().startswith("select"): print(">> 에러: SQL이 아님 -> AI 에러 설명 생성 중...") err_msg = ask_mistral( [UserMessage(content=get_error_response_prompt(user_input, "ERROR", "Invalid SQL Generated"))], max_tokens=512, # <--- [중요] 답변 길이 제한 temperature=0.7 ) print(f">> 에러 답변 완료: {err_msg}") return {"response": err_msg} # ------------------------------------------------------- # 3. DB 실행 # ------------------------------------------------------- db_result = execute_sql_query(clean_sql) # (A) DB 에러 발생 시 (문자열로 리턴된 경우) if isinstance(db_result, str) and "SQL_ERROR" in db_result: print(f">> DB 실행 에러 감지: {db_result}") print(">> AI에게 에러 설명 요청 중...") # 여기서 멈추지 않도록 max_tokens 설정 error_explanation = ask_mistral( [UserMessage(content=get_error_response_prompt(user_input, "ERROR", db_result))], max_tokens=512, # <--- [중요] 짧게 설정하여 멈춤 방지 temperature=0.7 ) print(">> AI 에러 설명 생성 완료") return {"response": error_explanation} # (B) 검색 결과가 0건일 때 if isinstance(db_result, list) and not db_result: print(">> 결과 없음 (Empty List) -> AI 설명 요청 중...") empty_explanation = ask_mistral( [UserMessage(content=get_error_response_prompt(user_input, "EMPTY", "No records found"))], max_tokens=512, # <--- [중요] temperature=0.7 ) print(">> 결과 없음 설명 완료") return {"response": empty_explanation} # (C) 정상 결과 반환 final_response = f"[검색 결과]\n" for row in db_result: row_text = ", ".join([f"{k}: {v}" for k, v in row.items()]) final_response += f"- {row_text}\n" # 헤더에 "Connection": "close"를 넣는 것이 핵심입니다. return JSONResponse( content={"response": final_response}, headers={"Connection": "close"} ) except Exception as e: print(f"Server Critical Error: {e}") # 함수 이름 에러(NameError) 등을 잡기 위해 traceback 출력 권장 import traceback traceback.print_exc() return {"response": "죄송합니다. 서버 시스템 오류가 발생했습니다."} if __name__ == "__main__": uvicorn.run(app, host="0.0.0.0", port=8000)