[SQL] Chapter13. Python with MySQL & Chapter14. 기본키/외래키 & Chapter15. 집계함수

황성미·2023년 8월 28일
sql제로베이스제로베이스데이터스쿨제로베이스스쿨
0

제로베이스 데이터스쿨

목록 보기
33/58
post-thumbnail
post-custom-banner

✍🏻 28일 공부 이야기.




파이썬으로 MySQL 코드 만들어보기

실습환경 만들기

  • jupyter notebook 또는 vscode프로그램을 실행시킨 후 sql_ws 폴더에서 시작!

  • AWS RDS로 생성한 database-1 사용

  • 이전에 작성해둔 police_station 데이터 삭제

  • python.ipynb 파일 하나 생성



Python으로 MySQL에 접속하는 방법

먼저, MySQL Driver를 설치해야한다.

pip install mysql-connector-python

위 코드를 실행한 후, import mysql.connector 를 실행시켰을 때 에러가 안 나면 된다.


📌 MySQL에 접속하기 위한 코드

변수명 = mysql.connector.connect(
	host = "호스트이름",
    user = "사용자이름",
    password = "비밀번호"
)

기본적인 위 코드를 바탕으로 Local Database와 AWS RDS(database-1) 연결에 대해 알아보자.

  • Local Database에 연결
    local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "비밀번호"
)
  • AWS RDS(database-1)에 연결
    remote = mysql.connector.connect(
    host = "database-1.cohcgh9bucnv.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "비밀번호"
)

연결을 다 했으면
변수명.close()를 통해 연결을 끊어줘야한다.


📌 특정 database에 접속하기 위한 코드

변수명 = mysql.connector.connect(
	host = "호스트이름",
    user = "사용자이름",
    password = "비밀번호",
    database = "데이터베이스이름"
)



Python으로 MySQL 쿼리를 실행하는 방법

📌 쿼리를 실행하기 위한 코드

커서명 = 변수명.cursor()
커서명.excute("쿼리")

테이블을 생성해보자.


테이블을 삭제해보자.


📌 SQL 파일을 실행하기 위한 코드

  • sql파일 안에 쿼리가 하나인 경우
    커서명 = 변수명.cursor()
sql파일을읽은변수명 = open("파일명.sql").read()
커서명.execute(sql파일을읽은변수명)

sql파일을 생성해보고 실행해보자.

하지만, 파일 안에 쿼리가 여러 개인 경우 에러가 뜬다는데.. (나는 에러없이 실행은 정상적으로 되었지만 데이터가 insert되진 않았다. 심지어 테이블을 생성하는 코드는 multi 옵션 없이도 동작했다. 이유는 아직 모르겠음😓)

일단 대체적으로 쿼리가 여러 개인 경우는 옵션을 하나 더 추가해줘야 한다.


  • sql파일 안에 쿼리가 여러 개인 경우
    커서명 = 변수명.cursor()
sql파일을읽은변수명 = open("파일명.sql").read()
커서명.execute(sql파일을읽은변수명, multi = True)

sql파일을 생성해보고 실행해보자.



Fetch All

쿼리를 실행한 결과값을 변수에 담을 수 있다.

result = 변수명.fetchall()

for data in result:
	print(data) # row마다 출력됨

sql_file 테이블을 조회해보자.(읽어올 데이터 양이 많은 경우 buffered = True 옵션을 주자)


검색 결과를 Pandas로 읽어보자.




Python with CSV

csv 파일에 있는 데이터를 Python으로 insert해보자.

import pandas as pd
import mysql.connector

# csv 파일 읽기
df = pd.read_csv("police_station.csv")
df.head()


# 데이터베이스 연결
remote = mysql.connector.connect(
      host = "database-1.cohcgh9bucnv.ap-southeast-2.rds.amazonaws.com",
      port = 3306,
      user = "admin",
      password = "비밀번호",
      database = "zerobase"
  )


# cursor 만들기
cursor = remote.cursor(buffered=True)

# insert 문 만들기
sql = "insert into police_station values (%s , %s)" 
                                        # 컬럼 두 개의 정보를 읽기 위함
                                        
# 데이터 입력
for i , row in df.iterrows():
    cursor.execute(sql, tuple(row)) # 데이터가 튜플형태로 담겨진 것을 sql %에 들어감
    remote.commit()


# 결과 확인
cursor.execute("select * from police_station")

result = cursor.fetchall()


# 검색 결과를 pandas로 읽기
df = pd.DataFrame(result)
df.head()
  • commit() 은 database에 적용하기 위한 명령
  • csv 한글이 깨지는 경우, encoding = 'euc-kr' 로 읽기

🤔 csv파일을 pandas 데이터프레임으로 읽은 것을 다시 sql 테이블에 데이터를 집어넣어주고 이를 다시 또 pandas 데이터프레임으로 읽어들였다.. 처음에 pandas로 읽은 것을 다시 또 읽는.. 똑같은 작업을 2번 하는게 이해가 안되었지만 나중에 쓰이는 곳이 있겠지..?



Primary key, Foreign key

실습환경 만들기

local database에 접속하고 zerobase 데이터베이스로 이동해주기.

use zerobase;
Primary keyForeign key
테이블의 각 레코드를 식별한 테이블을 다른 테이블과 연결해주는 역할
중복되지 않은 고유값을 포함참조되는 테이블의 항목은 그 테이블의 기본키(혹은 단일값)
NULL 포함 안 됨
테이블 당 하나의 기본키를 가짐

PRIMARY KEY

📌 Primary key 생성 방법

  1. 테이블 생성 시 지정하는 경우
CREATE TABLE 테이블이름
(
	....
    
    CONSTRAINT constraint_name
    PRIMARY KEY (컬럼이름1, 컬럼이름 2, ...) <-- 기본키는 1개 이상일 수 있음 -->
)
  1. 이미 테이블이 생성된 경우
ALTER TABLE 테이블이름
ADD CONSTRAINT constraint_name PRIMARY KEY (컬럼이름1, 컬럼이름 2, ...)
  • constraint_name 을 생략하는 경우, 자동 생성됨.

📌 Primary key 삭제 방법

ALTER TABLE 테이블이름
DROP PRIMARY KEY <-- 모든 기본키가 삭제됨 -->

FOREIGN KEY

📌 Foreign key 생성 방법

  1. 테이블 생성 시 지정하는 경우
CREATE TABLE 테이블이름
(
	....
    
    CONSTRAINT constraint_name
    PRIMARY KEY (컬럼이름1, 컬럼이름 2, ...) 
    
    CONSTRAINT constraint_name
    FOREIGN KEY (컬럼이름3, 컬럼이름 4, ...) REFERENCES 참조하는 테이블이름(참조 컬럼이름)
)
  1. 이미 테이블이 생성된 경우
ALTER TABLE 테이블이름
ADD FOREIGN KEY (컬럼이름1, 컬럼이름 2, ...) REFERENCES 참조하는 테이블이름(참조 컬럼이름)
  • constraint_name 을 생략하는 경우, 자동 생성됨.

📌 자동 생성된 CONSTRAINT를 확인하는 방법

SHOW CREATE TABLE 테이블이름

📌 Foreign key 삭제 방법

ALTER TABLE 테이블이름
DROP FOREIGN KEY 삭제할 외래키 이름



앞서 생성한 prime_station의 name 컬럼을 기본키로, crime_status의 police_station을 외래키로 설정해보자.

Step1. 둘은 기본키, 외래키로 설정 가능한지 확인해야함.

: 두 컬럼은 모두 31개의 데이터로 동일하긴 하고 데이터가 잘 매치되는 것을 보아 기본키, 외래키로 설정할 수 있다. 하지만 police_station에는 구 이름이, name에는 경찰서의 full name이 들어가 있으므로 나중에 이를 맞춰주는 작업이 필요할 듯하다.


Step2. prime_station의 name 기본키 지정


Step3. crime_status의 police_station 외래키 지정

: 그냥 지정하게 되면 두 데이터가 일치되지 않기 떄문에 에러가 뜬다.

그래서 새로운 컬럼 reference를 만들어 외래키로 지정해주고자 한다.

값 채워주기




Aggregate Functions(집계함수)

실습환경 만들기

AWS RDS(database-1)에 접속
crime_status 테이블 데이터 확인

Aggregate Functions(집계함수)

  • count : 총 개수
  • sum : 합계
  • avg : 평균
  • min : 최소값
  • max : 최대값
  • first : 첫번째 결과값
  • last : 마지막 결과값

count

SELECT count(컬럼이름)
FROM 테이블이름
WHERE 조건


sum

SELECT sum(컬럼이름)
FROM 테이블이름
WHERE 조건


avg

SELECT avg(컬럼이름)
FROM 테이블이름
WHERE 조건


min

SELECT min(컬럼이름)
FROM 테이블이름
WHERE 조건


max

SELECT max(컬럼이름)
FROM 테이블이름
WHERE 조건


group by

집계함수는 group by와 함께 자주 사용된다. distinct와 달리 정렬을 할 수 있다는 장점이 있다.

SELECT 컬럼이름
FROM 테이블이름
GROUP BY 컬럼이름
ORDER BY 컬럼이름
WHERE 조건


having

조건에 집계함수가 포함되는 경우 where대신 having을 사용한다.

SELECT 컬럼이름
FROM 테이블이름
WHERE 조건
GROUP BY 컬럼이름
HAVING 조건
ORDER BY 컬럼이름

경찰서 별로 발생한 범죄 건수의 합이 4000건 보다 큰 경우를 조회하라.

profile
황성미
데이터 분석가(가 되고픈) 황성미입니다!
이전 포스트

[SQL] Chapter11. AWS RDS & Chapter12. SQL File

다음 포스트

[SQL] Chapter16. Scalar Functions & Chapter17. SQL Subquery

post-custom-banner

0개의 댓글