스터디 노트🖊️_Day 35(SQL)

정설령·2023년 4월 19일
awssql

SQL

목록 보기
7/9
post-thumbnail

✔️ SQL 심화 1~4

AWS(Amazon Web Service, 아마존 웹 서비스)

  • AWS는 아마존닷컴의 클라우드 컴퓨팅 사업부이며, 다른 웹 사이트나 클라이언트측 응용 프로그램에 대해 온라인 서비스를 제공해줌
  • AWS 접속링크 : https://portal.aws.amazon.com/billing/signup#/start
  • AWS RDB : AWS에서 제공하는 관계형 데이터베이스 서비스
    • Cloud상에 Database 구축
  • AWS 사용 방법
    1) AWS 가입
    2) MySQL RDS 생성
    3) AWS RDS 외부접속 설정
    - DB식별자 선택 → 연결&보안 → VPC 보안그룹 → 보안 그룹 ID 선택 → 인바운드 규칙 편집 → 규칙추가(유형: MYSQL/Aurora, 소스: AnywhereIPv4)
    4) MySQL RDS 접속
    - DB식별자 선택 → 연결&보안 → 엔드포인트 복사 → 터미널 실행 → -h "엔트포인트" -P 포트 -u 마스터사용자이름 -P 입력

    - 외부 접속가능 확인
  • AWS RDS 중지
    - 사용하지 않을 경우에는 사용시간 절약을 위해 중지하는 방법 추천
    - 서비스 / RDS / 데이터베이스 → DB식별자 체크 → 작업/중지
  • AWS RDS 시작
    - 서비스 / RDS / 데이터베이스 → DB식별자 선택 → 작업/시작

문제(복습)

  1. AWS RDS (database-1)에 접속하세요.
  2. Database (zerobase)를 생성하고 확인하세요. (DEFAULT CHARACTER SET utf8mb4)
  3. 사용자(zero)를 추가하고 확인하세요.
  4. 사용자(zero)에게 외부에서 zerobase에 접근해서 사용하기 위한 권한을 부여하세요.
  5. 현재 접속을 종료하고 AWS RDS (database-1)의 zerobase에 zero로 접속하세요.

SQL File

  • SQL File : SQL 쿼리를 모아놓은 파일

  • workspace 생성

    • sql_ws 폴더를 만들고 그 하위에서 VSCode 실행
    • VSCode 터미널에서 데이터베이스 접속

  • SQL File 실행

    • test01.sql

    • test02.sql

  • SQL File로 Database 백업하기

    • zerobase 백업
    • 백업 확인 (zerobase.sql)

  • Database Restore

    • 데이터베이스를 백업한 SQL File을 실행하여 그 시점으로 복구하거나 이전 할 수있다. (SQL File을 실행하는 방법과 동일함)
    • zerobase 데이터베이스 확인
    • zerobase 데이터베이스 이동 및 AWS RDS (database-1)의 zerobase Database를 복원
    • 결과 확인

  • Table Backup & Restore

    • 테이블 단위로도 백업 가능



  • Table Schema Backup

    • 데이터를 제외하고 테이블 생성 쿼리만 백업 가능

문제

  1. AWS RDS (database-1)의 zerobase 데이터베이스를 백업하세요.(※ AWS RDS database 를 백업할 경우 다음의 옵션을 추가 : --set-gtid-purged=OFF)
  2. AWS RDS (database-1)에 admin 계정으로 로그인하세요.
  3. Database (zerodb)를 생성하세요.(DEFAULT CHRACTER SET utf8mb4)
  4. 앞서 생성한 사용자 (zero@%)에게 zerodb의 모든 권한을 부여하세요.
  5. 앞서 백업한 zerobase 백업파일을 zerodb에서 실행하세요.
  6. police_station 테이블에 아래 데이터를 INSERT 하기위한 SQL 파일을 생성하세요.
  7. SQL 파일을실행하여 AWS RDS (database-1) zerobase의 police_station 테이블에 데이터를 INSERT하고 확인하세요.

  8. AWS RDS (database-1) zerobase 의 police_station 테이블을 SQL 파일로 백업하세요.
  9. SQL 파일을 실행하여 AWS RDS (database-1) zerodb의 police_station 테이블을 zerobase와 동일하게 만들고 확인하세요.

Python과 MySQL 연결

  • MySQL Driver 설치
    • pip install mysql-connector-python
  • Local Database 연결
    local = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    password = '    '
)
  • AWS RDS(database-1) 연결
    local = mysql.connector.connect(
    host = '    ',
    port = 3306,
    user = 'admin',
    password = '    '
)
  • Close Database
    local.close()
remote.close()
  • 특정 'zerobase' 데이터베이스 연결 시 각 내부에 쿼리 추가
    database = 'zerobase'

Excecute SQL

  • Query를 실행하기 위한 코드
  • 테이블 생성
remote = mysql.connector.connect(
    host = "    ",
    port = 3306,
    user = 'admin',
    password = '    ',
    database = 'zerobase'
)

cur = remote.cursor()
cur.execute('create table sql_file (id int, filename varchar(16))')

remote.close()
  • 테이블 삭제
remote = mysql.connector.connect(
    host = '    ',
    port = 3306,
    user = 'admin',
    password = '    ',
    database = 'zerobase'
)

cur = remote.cursor()
cur.execute('drop table sql_file')

remote.close()

Execute SQL File 예제

  • test03.sql 파일 생성 및 실행
remote = mysql.connector.connect(
    host='    ',
    port = 3306,
    user = 'admin',
    password = '    ',
    database = 'zerobase'
)

cur = remote.cursor()
sql = open('test03.sql').read()
cur.execute(sql)

remote.close()
  • test04.sql 파일 생성 및 실행
remote = mysql.connector.connect(
    host = '    ',
    port = 3306,
    user = 'admin',
    password = '    ',
    database = 'zerobase'
)

cur = remote.cursor()
sql = open('test04.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)

remote.commit()
remote.close()

Fetch All 예제

  • celeb 테이블을 조회( 읽어올 데이터 양이 많은 경우 buffered=True)
remote = mysql.connector.connect(
    host = '    ',
    port = 3306,
    user = 'admin',
    password = '    ',
    database = 'zerobase'
)

cur = remote.cursor(buffered=True)
cur.execute('select * from sql_file')
result = cur.fetchall()
remote.close()

result
# [(1, 'test01.sql'), (2, 'test02.sql'), (3, 'test03.sql'), (4, 'test04.sql')]
for result_iterator in result:
    print(result_iterator)
    
# (1, 'test01.sql')
# (2, 'test02.sql')
# (3, 'test03.sql')
# (4, 'test04.sql')
import pandas as pd

df = pd.DataFrame(result)
df

Python과 CSV 연결

  • csv에 있는 데이터를 Python으로 INSERT
  • police_station.csv를 pandas 로 읽어오기
import pandas as pd

df = pd.read_csv('police_station.csv')
df.tail()

  • zerobase에 연결
import mysql.connector
conn = mysql.connector.connect(
    host = '    ',
    port = 3306,
    user = 'zero',
    password = 'zerobase',
    database = 'zerobase'
)

# cursor(커서) 만들기
cursor = conn.cursor(buffered=True)

# insert 문 만들기
sql = 'insert into police_station values (%s, %s)'

# 데이터 입력
# cursor 관련 공식문서 : https://dev.mysql.com/doc/connectors/en/connector-python-api-mysqlcursor-execute.html
for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()		# commit() : 데이터베이스에 적용하기 위한 명령
			
# 결과 확인
cursor.execute('select * from police_station')
result = cursor.fetchall()

# 검색 결과를 pandas로 읽기
df = pd.DataFrame(result)
df.head()

Python과 CSV 연결2

  • crime_status 테이블에 2020_crime.csv를 입력하는 코드를 작성
import pandas as pd

conn = mysql.connector.connect(
    host = '    ',
    port = 3306,
    user = 'zero',
    password = 'zerobase',
    database = 'zerobase'
)

# 2020_crime.csv 데이터 (encoding='euc-kr') 읽어오기
df = pd.read_csv('2020_crime.csv', encoding='euc-kr')
df.head()

# insert 쿼리 작성
sql = "insert into crime_status values('2020', %s, %s, %s, %s)"
cursor = conn.cursor(buffered=True)

# 데이터를 crime_status 테이블에 insert
for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()
    
# crime_status 테이블의 데이터 조회
cursor.execute('select * from crime_status')
result = cursor.fetchall()
    
# 조회한 결과를 pandas로 변환해서 확인
df = pd.DataFrame(result)
df.head()

문제

  1. AWS RDS(database-1) zerobase에 접속
import mysql.connector

conn = mysql.connector.connect(
    host = '    ',
    port = 3306,
    user = 'zero',
    password = 'zerobase',
    database = 'zerobase'
)
  1. cctv Table 을 생성
import pandas as pd

df = pd.read_csv("Seoul_CCTV.csv")
df.head()


3. cctv SQL 파일에서 데이터를 Pandas로 읽어오기

sql = 'create table cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)'
cursor = conn.cursor(buffered=True)
cursor.execute(sql)
  1. 데이터를 cctv 테이블에 INSERT
sql = "insert into cctv values(%s, %s, %s, %s, %s, %s)"
cursor = conn.cursor(buffered=True)

for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()
  1. cctv 테이블의 데이터를 조회하여 확인
cursor.execute('select * from cctv')

result = cursor.fetchall()
for row in result:
    print(row)


6. 조회된 데이터를 Pandas로 변환하여 출력

df = pd.DataFrame(result)
df.head()

"이 글은 제로베이스 데이터 취업 스쿨의 강의 자료 일부를 발췌하여 작성되었습니다."

profile
정설령
이전 포스트

스터디 노트🖊️_Day 34(SQL)

다음 포스트

스터디 노트🖊️_Day 36(SQL)

0개의 댓글