jhw/PCITY_DID
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
PCITY_DID/SOURCE/LLM_Mistral/ubuntu API 서버 파일 백업/serve_mistral_20260220_전체디비포함.py
jhw b2916b74b1 first upload
2026-09-04 15:12:16 +09:00

476 lines
19 KiB
Python
Raw Permalink Blame History

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)
Reference in New Issue View Git Blame Copy Permalink