[Dev][Enhancement] Mysql 연동

[SSANGYOON]

What : mysql에 xls파일 저장 및 쿼리 기능

Why : 조건에 맞는 알약 검색 및 점수 탐색

How

• [x] csv 파일 저장
• [x] 저장된 텐서 기반 상위 알약 추출 기능

[SSANGYOON]

import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import base64
# sqlalchemy는 python에서 사용하는 orm(sql없이 db를 쉽게 쓰게 해주는) 모듈입니다
# base64는 np array(float32)->byte string으로 변환하면
# 데이터베이스에 text 타입으로 저장할수 없는 문자가 포함됩니다(ascii의 범위를 벗어남)
# 따라서 base64로 인코딩을 해서 저장할 수 있는 형태로 바꿔줍니다. 

# db에 커넥션 생성
conn = pymysql.connect(
    user='sangyoon', 
    passwd='1234', 
    host='localhost', 
    db='pills', 
    charset='utf8'
)

#엑셀 파일 읽기
df = pd.read_excel('OpenData_PotOpenTabletIdntfc20220518.xls')
# 실제로는 model로 feature를 추출한 뒤에 저장해야 하지만 임시로 random array를 사용해봅시다.
# tobytes로 바이트 스트링으로 바꾸고 str.decode로 스트링으로 변경 base64로 저장가능한 텍스트로 변경

add_srs = pd.Series([base64.b64encode(np.random.rand(1024).tobytes()).decode('utf-8') for i in range(24345)], index=[i for i in range(24345)])
add_srs = pd.Series(random_features, index=[i for i in range(24345)])
#feature 값을 데이터프레임에 column으로 추가해줍니다.
df["feature"] = add_srs

#sql_alchemy와 pandas를 이용한 데이터베이스에 추가
db_connection_str = 'mysql+pymysql://sangyoon:1234@127.0.0.1/pills'
db_connection = create_engine(db_connection_str)
df.to_sql(name='pills_table', con=db_connection, if_exists='append',index=False)

#sql로 색상이 연두와 원형인 것들을 가져온 결과
cursor = conn.cursor()

sql = '''
SELECT  feature
from pills_table
where 색상앞 = '연두' 
and 의약품제형 = '원형'
'''

cursor.execute(sql) 
res = cursor.fetchall() 

for data in res[:5]:
    print(np.frombuffer(base64.b64decode(data[0].encode('utf-8')), dtype=np.float64))

conn.commit() 
conn.close()

[SSANGYOON]

[SSANGYOON]

위와 같이 조건에 맞는 이미지 url을 데이터베이스에서 가져올 수 있습니다. feature는 일단 가져오지 않았습니다.

[ghost]

감사합니다. 노션에 잘 정리해뒀습니다! https://seoulsky-field.notion.site/MySQL-CSV-MySQL-5899c8f3f38b401fae817a932ce64167

[SSANGYOON]

import streamlit as st import io from PIL import Image import time import random import requests import pandas as pd import csv import pymysql import numpy as np import base64 st.set_page_config(page_title = "빨간 알약 줄까? 파란 알약 줄까?", layout="centered", page_icon = "pill")

def main():

st.image(Image.open('bald.jpg'))

st.sidebar.title("사용법")
st.sidebar.subheader("1. 알아보고 싶은 알약의 앞면과 뒷면이 잘 보이도록 사진을 각각 한 장씩 찍습니다.")
st.sidebar.subheader("2. 찍은 알약의 앞면 사진을 상단에, 뒷면 사진을 하단에 업로드합니다.")
st.sidebar.subheader("3. 앞면 사진과 뒷면 사진을 업로드하였다면 아래의 버튼을 눌러줍니다.")
st.sidebar.subheader("4. 업로드한 사진과 유사한 알약을 k개까지 웹페이지 상으로 보여줍니다.")

col1, col2 = st.columns(2)
col3, col4 = st.columns(2)
conn = pymysql.connect(
    user='sangyoon', 
    passwd='1234', 
    host='localhost', 
    db='pills', 
    charset='utf8'
)
cursor = conn.cursor()
uploaded_file_front = col1.file_uploader("알약 앞면 사진을 올려주세요.", type=["jpg", "jpeg", "png"])

if uploaded_file_front:
    image_bytes = uploaded_file_front.getvalue()
    image = Image.open(io.BytesIO(image_bytes))
    col3.image(image, caption='Pill front')

uploaded_file_back = col2.file_uploader("알약 뒷면 사진을 올려주세요.", type=["jpg", "jpeg", "png"])

if uploaded_file_back:
    image_bytes = uploaded_file_back.getvalue()
    image = Image.open(io.BytesIO(image_bytes))
    col4.image(image, caption='Pill back')
sql = '''
    SELECT  품목명,큰제품이미지,성상, feature
    from pills_table
    where 색상앞 = '연두' 
    and 의약품제형 = '원형'
'''
if uploaded_file_front and uploaded_file_back :

    random_feature = np.random.rand(1024)
    if st.button("예측 시작") :
        cursor.execute(sql) 
        res = cursor.fetchall() 
        st.write("예측 완료!")

        # 임의로 결과 생성 및 출력
        cols = st.columns(5)
        for i in range(5):

            response = requests.get(res[i][1])
            img = Image.open(io.BytesIO(response.content))

            cols[i].write(f"{i+1}. {res[i][0]}")
            cols[i].image(img)
            cols[i].write(f"성상: {res[i][2]}")
            feat = np.frombuffer(base64.b64decode(res[i][3].encode('utf-8')), dtype=np.float64)
            # 벡터의 내적/벡터의 크기 식입니다. 이렇게 코사인 유사도를 구합니다.
                            cols[i].write(f"점수: {np.dot(feat,random_feature)/(np.dot(feat,feat)*np.dot(random_feature,random_feature) ** 0.5)}")

main()

[SSANGYOON]

임시로 random feature을 사용한 버전입니다.

[SSANGYOON]

수정한 내용 csv -> xlsx (띄어쓰기 문제)