DS School Week 8-2 AWS RDS와 SQL (SQL File, SQL with Python)

오늘 학습 계획

• SQL 강의 수강 (AWS RDS, SQL File, Python with MYSQL)

학습 내용

1. AWS RDS

• 초기 세팅
  • 개정 개설
  • MySQL RDS 생성
    -> 스토리지 자동 조정 활성화 해제
    -> 퍼블릭 엑세스 체크
    -> 데이터 베이스 포트 확인
    -> 자동 백업 해제
    -> 삭제 방지 활성화
  • 보안 그룹 설정 변경 (외부 접속 환경 설정)
    -> 인바운드 규칙 편집
    -> 규칙 추가(유형 : MYSQL/Aurora, 사용자지정 : Anywhere-IPv4)
• 접속
  • 엔드포인트 확인 -> 호스트 이름
  • 포트 확인
  • % mysql -h <엔드포인트> -P <포트> -u <마스터사용자 이름> -p

2. SQL 파일

• SQL 쿼리를 모아놓은 파일
• 실행
  • 로그인 이후 실행
    source </path/filename.sql>
\. </path/filename.sql>
\. <filename.sql> 현재 폴더에 파일이 있는 경우
• 실행
  • 외부에서 실행
    % mysql -u username -p dbname < </path/filename.sql>
• 백업
  • 데이터베이스
    % mysqldump -u username -p dbname > filename.sql
    % mysqldump -u username -p --all-databases > filename.sql
    [AWS RDS]
    % mysqldump --set-gtid-purged=OFF -h hostname -P port -u username -p dbname > filename.sql
  • 테이블
    % mysqldump -u username -p dbname tbname > filename.sql
  • 테이블 스키마
    % mysqldump -d -u usernaame -p dbname > backup.sql
    % mysqldump -d -u usernaame -p dbname tbname > backup.sql

3. python으로 SQL 실행

• 설치
  % pip install mysql-connector-python

• 실행
  import mysql.connector

• MySQL 접속 코드 실행
  db = mysql.connector.connect(host = '', user = '', password = '', ...)
db.close()
  * 로컬일 경우 : host = 'localhost'

• 쿼리 실행
  cursor = db.cursor()

  • db.cursor(buffered=True) : 데이터를 읽을 때(용량이 필요할 때)
  • db.cursor(dictionary=True) : 컬럼 명을 함께 불러 올 때

  cursor.execute('query')

• SQL 파일 실행
  sql = open('filename.sql').read()
cursor.execute(sql)
  [파일 내 SQL query가 여러개인 경우]
  sql = open('filename.sql').read()
cursor.execute(sql, multi=True)

  sql = open('filename.sql').read()
  
  for result_iterator in cur.execute(sql, multi=True):
  	if result_iterator.with_rows:
    	  print(result_iterator.fetchall())
      else:
        print(result_iterator.statement)
        
   db.commit()

• SQL 파일 실행(데이터 불러오기)

  • fetchall

  • query 실행에 따라 데이터를 불러올 경우 데이터를 변수에 담아주는 함수
    cur = remote.cursor(buffered=True) ( buffered : 데이터 양이 많은 경우 설정하는 옵션
    cur.execute('query')
result = cur.fetchall()

    [데이터 프레임으로 읽기]
    pd.DataFrame(result)

  • commit

    • 데이터베이스에 적용하기 위한 명령어

  • 실행문

  sql = 'insert into tb values (%s, %s)'
  for i, row in df.iterrows():
     cur.execute(sql, tuple(row))
     print(tuple(row))
     conn.commit() 

다음 학습 계획

• SQL 강의 수강(KEY, Function, SUbqyery)