DB와 python 연결 및 데이터 추가하기.(feat.visual studio code, MySQL Workbench)

순서

- 단일 db에 접속할 수 있는 계정 만들기(보안상의 문제를 일으키지 않기 위해)

- host 주소와 위에서 만든 계정을 이용해 python으로 접속하기.

- 데이터 추가하기.

- 데이터 추가가 잘 되었는지 확인하기.

 

보안상의 문제가 있을 수 있으니 모든 데이터베이스에 접속하는게 아닌 특정 데이터베이스에 접속하기 위한 계정을 만들어줍니다.

 

  - 접속하려는 데이터베이스는 'yhdb'이고,

    계정의 id는 'yhdb' ('%' 는 현재 컴퓨터가 아닌 외부에서 접속한다는 설정입니다.)

    비밀번호는 'insta1234로 설정합니다.

  - 그리고 만든 계정으로 할 수 있는 권한을 설정합니다.( grant all은 select, update, delete등 모든 권한입니다)

 

잘 만들어졌는지 확인하기위해 DB에 연결해 확인합니다.(hostname 에는 서버의 ip주소 또는 DNS를 입력합니다)

aws서버의 호스트 주소에 username을 위에서 만든 'yhdb'로 로그인합니다.

 

yhdb라는 하나의 데이터베이스만 보이며, 접속이 잘 된 것을 확인할 수 있습니다.

 

 

이제 python에서도 연결을 해봅니다. 

import streamlit as st
import mysql.connector
from mysql.connector import Error

def main():
     title = st.text_input('책의 제목을 입력하세요')
     author_fname = st.text_input('작가의 이름을 입력하세요')
     author_lname = st.text_input('작가의 성씨를 입력하세요')
     released_year = st.number_input('출판년도를 입력하세요',0)
     stock_quantity = st.number_input('판매 부수를 입력하세요',0)
     pages = st.number_input('페이지 수를 입력하세요',0)

     if st.button('저장'):
          try:         
              connection = mysql.connector.connect(
              host = ' ip주소 또는 DNS (aws RDS에서 엔드포인트) ',
              database = 'yhdb',
              user = 'yhdb',
              password = 'yh1234'
              )

              if connection.is_connected():
                  cursor = connection.cursor()
                  query = '''insert into books (title, author_fname, author_lname, released_year, stock_quantity, pages)
                              values (%s, %s, %s, %s, %s, %s);'''

                  record = (title, author_fname, author_lname, released_year, stock_quantity, pages)
                  cursor.execute(query, record)
                  connection.commit()

                  print( '{}개 저장됨'.format(cursor.rowcount) )

          except Error as e:
        	print('db관련 에러 발생', e)
    
          finally :
        	cursor.close()
        	connection.close()
        	print('MySQL connection END')

if __name__ == '__main__':
    main()

pip install mysql-connector-python를 통해 mysql 모듈을 다운받습니다. 이 모듈이 없으면 db와 연결할 수 없습니다.

 

mysql.connector.connect() 함수를 이용해서 db에 연결합니다. 관리자계정이 아닌 데이터베이스 하나에만 접속할 수 있는 아이디로 접속합니다.

 

제대로 된 정보를 입력하였다면 연결이 되어 if connection.is_connected(): 밑의 문장이 실행됩니다.

 

데이터 추가를 위해 커서를 만들어줍니다.( connection.cursor() )

 

SQL query와 입력 데이터를 query, record 변수에 따로 저장합니다.

  - query는 ''' '''으로 전체 문장을 끌어오고, values( 추가하는 데이터셋 )엔 %s를 입력합니다.(record의 값이 들어갑니다)

  - 데이터의 추가는 cusor.excute(query, record)로 하고, connection.commit()으로 DB에 저장합니다.

 

 

cursor.rowcount데이터를 이용해 변경된 데이터의 수를 불러옵니다.

 

try문에서 접속하는데 오류가 난다면 except Error as e: 안으로 들어가 'db관련 에러 발생'과 에러코드(e)를 출력합니다.

 

try문이 끝나거나 오류가 발생해 except내의 코드가 실행된 후엔 finally가 실행됩니다.

 

 

 

데이터를 입력하고 추가된 데이터를 확인해보겠습니다.

 

실제로 데이터를 추가합니다.(Streamlit 으로 실행했습니다)

들어갈 데이터를 입력하고 저장버튼을 누르면 저장이 잘 되는것을 볼 수 있습니다.(MySQL Workbench에서 확인했습니다)

book_id는 auto_increment를 한 데이터이기때문에 내림차순으로 불러 가장 최근에 저장한 데이터순으로 정렬합니다. 

 

여러개의 데이터를 입력할 땐 리스트를 사용합니다. 

  + cursor.execute()  --> cursor.executemany() 를 사용합니다.