CS50x 2023 - Lecture 7 - SQL

Processing csv files using Python

 

SQL이지만 앞부분은 파이썬의 남은 부분들을 다루고 있음.

파이썬으로 아래처럼 생긴 csv파일을 읽어내서 간단한 연산을 하는 코드를 짠다.

 

위 csv파일에서 language 컬럼에 있는 C, Python, Scratch가 각각 몇번 나왔는지 세어주는 것이 아래 코드이다.

3번줄: "favorites.csv" file을 read mode로 읽음

4번줄: 읽어낸 파일을 dictionary 형태로 가져옴

5번줄: 카운트 결과물을 담아낼 빈 dictionary를 만듦

6~11번줄: language 컬럼을 한줄 한줄 읽어내어, 읽어낸 스트링을 favorite에 담고, 이것이 counts의 key value로 있으면 값에 1을 더하고, key value로 없으면 값을 1로 세팅해줌

13~14번줄: favorite에 담긴 language명들만큼 print를 하는데, favorite값과 해당 favorite의 counts value값을 함께 출력함.

이때, sorted함수 안에서는 counts별로 sort하는데, key는 counts의 value값들이고, reverse= True로 놓아서 내림차순으로 출력하게 함

 

그렇게 출력된 결과물이 아래와 같다.

 

Relational Database

sql 기초 문법 설명

• SELECT, DELETE, UPDATE...
• WHERE, LIKE GROUP BY, ORDER BY, LIMIT...
• AVG, COUNT, DISTINCT, LOWER, MAX, MIN, UPPER

 

Primary keys, Foreign keys, Relationships 

 

아래와 같은 테이블들의 관계도를 참조하여, Steve Carell이 출연한 작품 명을 people -> starts -> shows 테이블 순서대로 찾아가 조회하는 nested query를 짜는 예시를 보여줌.

 

또한, nested 쿼리 대신 JOIN~ON 신택스로 서로 다른 테이블을 공통된 키값으로 연결할 수 있음.

 

JOIN대신 아래처럼 from절에 원하는 테이블들을 모두 넣고 WHERE절에 join 조건을 넣는 implicit join도 있음.

 

LIKE '~%~' 를 써서 wildcard를 조건절에 사용할 수도 있음.

 

INDEX를 만들어서 SELECT 문의 퍼포먼스를 훨씬 향상시킬수도 있음. memory문제가 있다는 trade off가 있긴 하다.

퍼포먼스 향상 내역은 Run Time의 변화로 확인할 수 있음.

 

 

Python & SQL

파이썬을 활용하여 db에 접근해서 SQL문을 실행하는 예시이다.

3번줄에서 접근할 db를 정의하고, 

5번줄에서 input값을 받아서,

7번줄에서 해당 input값을 조건절에 넣어 select문을 실행한다.

그리고 9번줄에서 실행한 결과의 값을 가져오는데, db.execute의 7번의 결과물은 언제나 list이기 때문에 [0]을 넣어서 첫번째 결과값을 가져온다고 명시한 뒤, 원하는 컬럼인 "n"의 값을 가져온다고 하는것이다.

[0]대신 for row in rows: 방식으로 for loop을 돌려서 리스트의 결과값을 하나하나 가져오는 식으로 할 수도 있다.

이때도 어차피 count(*)결과값은 한줄이기 때문에 한줄만 가져온다. 어느쪽으로 해도 상관없다.

또한, 7번줄에서 보다시피 SQL문에서는 '?'를 placeholder로 쓴다는 것에 유의하자.

 

 

Race condition

 

한 테이블에 대해서 Update, Delete 등 DML이 동시 다발적으로 이뤄지는 경우, 하나의 명령어가 온전히 데이터 업데이트를 끝내기도 전에 다른 세션에서 실행된 명령어가 미처 업데이트 되지 않은 그 테이블을 업데이트 하여, 결국 테이블의 업데이트가 제대로 이뤄지지 않는 문제가 있을 수 있다.

 

이를 아래 코드라인의 맨 처음과 끝줄처럼 BEGIN TRANSACTION, COMMIT 을 이용하여, 한 세션이 첨부터 끝까지 온전히 실행되거나, 아예 실행자체를 안시킬수 있도록 제어할 수 있다.

 

 

SQL Injection Attacks

 

SQL문으로 인풋값을 받게 될 때 아래처럼 파이썬 문법으로 f string, single quote를 활용하여 받는 방법도 있으나, 이는 안전한 방법이 아니다.

 

해커 등 불순한 의도를 가진 사람이 아래처럼 username을 넣어야 하는 부분에 '-- (single quote마무리하고 --로 SQL 주석처리) 따위로 끝나는 SQL injection을 시도할 수 있기 때문이다.

 

 

그래서 이렇게 SQL을 위한 library를 사용하여 ? placeholder를 쓰는 방식으로 인풋값을 single quote(')로 받지않아도 되게끔 하는것이다. 이렇게 받으면 input값의 single quote가 파이썬 문법의 single quote가 아닌, 유저 네임자체에 들어있는 single quote로 안전하게 받아들여 인풋값이 sanitized되고, 문제도 예방할수 있게 된다.

 

아래의 자동차 번호판도 SQL injection attacks의 예시로, 차주가 자기 자동차 번호 (ZU 0666)가 인식되면 drop databse문을 실행시키도록 SQL injection attack을 시도하려는 것이다. (이건 좀 웃겼음..ㅋㅋㅋ)