[PLAYDATA] 데이터 엔지니어링 8월 4주차 8/22

• 8월 22일

오늘 처음으로 SQL 수업을 시작했다. DB나 SQL쪽은 아예 손대본 적이 없어서 배우는 내용마다 새로웠다. 하지만 별로 어려운 건 아닌 것 같다.

 

 

API

API가 뭔가?
Application Programming Interface의 약자로, 응용 프로그램과 응용프로그램 또는 응용 프로그램과 시스템 프로그램끼리 소통하기 위한 프로그램의 일부이다.
API는 외부로부터 들어온 입력에 대해 내부 프로그램에 맞게 입력값을 가공한 후 내부 프로그램으로 전송하는 부분과, 반대로 내부로부터 값을 받아서 출력값을 가공한 후 외부로 전송하는 부분으로 이루어져 있다.


JSON
JSON이란 키-값으로 이루어진 데이터들을 계층화 시키고 이를 공통적인 방법으로 다룰 수 있게 하는 표준 파일 형식이다.

import json
import json을 통해 python에서 json관련 작업을 할 수 있다.

(알아두기)
기본적으로 dict구조이나, 특정 key 하나에 여러 element가 들어간다면 value 자리에 list가 들어간다.

json.dumps(dict) dict 자료형을 json형태의 str으로 변환
-> 옵션은 indent, sort_keys, ensure_ascii 등이 있다.
-> indent는 들여쓰기를 할 때 공백의 갯수
-> sort_keys는 key들을 정렬
-> ensure_ascii는 출력에서 모든 Non ASCII문자들을 ASCII로 변환
json.loads(str) json형식의 str을 dict형태로 변환 (dumps함수와 정 반대)

참고) requests.get(url) 함수에서 .text의 경우 str형태로 반환하고, .json()의 경우 dict형태로 반환한다. 그래서 json형식의 url을 .text로 받아온다면 str->dict 변환을 추가로 해 줘야하기 때문에 번거롭다.

 

 

import xmltodict
xml형식의 str을 dict로 변환해 주는 모듈이다

xml이란?
eXtensible Markup Language의 약자로, 다른 마크업 언어를 만드는데 사용되는 다목적 마크업 언어이다.
예)
<?xml version="1.0" encoding="UTF-8" ?>
<사용자정보>
    <이름>홍길동</이름>
    <나이>25</나이>
    <거주지>서울</거주지>
    <신체정보>
        <키 unit="cm">175.4</키>
        <몸무게 unit="kg">71.2</몸무게>
    </신체정보>
    <취미>등산</취미>
    <취미>자전거타기</취미>
    <취미>독서</취미>
</사용자정보> 

xmltodict함수들
xmltodict.parse(xml_str, xml_attribs = T/F) xml문자열을 dict 형태로 변환한다.
-> xml_attribs가 True라면 xml의 tag속성(위에선 unit)이 key-value형태로 삽입된다.
-> xml_attribs가 False라면 tag속성을 제외하고 content만 들어간다.
-> xml_attribs를 True로 할 때, tag속성 key값은 앞에 @를 붙인다. content는 #text를 key값으로 가지는 dict의 value부분에 있다.

Tip) url에 변수값 붙이는 방법
변수이름 : 변수값 구조의 dict 변수를 만들고, requests.get()함수를 사용할 때 params옵션에 dict변수를 넣는다.
예시)
req_url = "http://api.openweathermap.org/data/2.5/weather"
req_parameter = {"lat":LAT, "lon":LON , "appid": API_KEY, "units":UNIT}
r = requests.get(req_url, params=req_parameter)
이때 실제로 request되는 url은 다음과 같다.
 http://api.openweathermap.org/data/2.5/weather?lat=37.57&lon=126.98&appid=b235c57pc357fb68acr1e81&units=metric 


Oracle SQL 조작법
conn <user_name> 다른 user로 접속하기
conn /as sysdba 최고 관리자로 접속
show user 현재 어떤 유저로서 프롬프트를 조작하는지 확인
create user <user_name> identified by <password> account unlock;
-> <user_name>을 가진 유저를 생성하고 계정을 unlock상태로 함
alter user <user_name> identified by <password> account unlock;
-> 해당 유저의 상태 변경
grant <privilage, ...> to <user_name> 해당 유저에게 권한 부여
exit SQL 탈출
sqlplus / as <user_name> 해당 유저로 시작(최고관리자는 sysdba)
-> SQL 실행 전에 실행하는 커맨드임
drop user <user_name>; 유저 삭제
-> 유저가 생성한 테이블이 있는 경우엔 뒤에 cascade를 추가
-> 해당 유저가 접속중일 때에는 삭제 불가능

 

Table 관리 (중요!)

CREATE TABLE <table_name>(<column_name> <datatype>, <column_name> <datatype>, ...); Table 생성
-> datatype은 정수형인 number와 varchar인 문자(열)이 있다. (예시: NUMBER(4), VARCHAR(13))

INSERT INTO <table_name> VALUES(var, var, var, ...); 한 행에 신규 데이터 추가(var배열은 각각 하나의 열을 의미)

UPDATE <table_name> SET <column_name>=value [WHERE <condition>]; 조건이 맞을 때, 한 행의 정보 업데이트

-> condition은 보통 <column_name>=<value> 형태이다.

DELETE FROM <table_name> [WHERE <condition>]; 테이블의 내용(행) 삭제. WHERE 구문이 없으면 전체삭제이니 유의!
SELECT * FROM <table_name> Table 내용 보기
DESC <table_name> Table구조 조회
DROP TABLE <table_name> Table삭제
ROLLBACK; 이전으로 롤백

COMMIT; 변경된 데이터들을 확정시킴(커밋 이후에는 롤백이 안됨!)

 

자주 쓰는 지정자들

SELECT <column_name, column_name, ...>

특정 column들의 데이터를 조회한다

FROM <table_name>

테이블을 지정한다

WHERE <condition>

조건을 지정한다

조건 연산자는 =, AND, OR, NOT, IN, BETWEEN 등이 있다

-> IN연산자는 병렬적인 OR 식을 한꺼번에 묶는 역할을 하며, WHERE <column_name> IN (var, var, ...) 형식으로 사용됨. 즉 어떤 행의 column값이 소괄호 안에 있는 원소들 중 하나에 속한다면 그 행이 True가 된다.

GROUP BY <column_name>

어떤 테이블을 특정 열을 기준으로 그룹화 하는 것이다.

GROUP지정자는 다음과 같이 쓰인다

SELECT <column_name>, <func(column_name)>, <func(column_name)><func(column_name)>, ... FROM <table_name> GROUP BY <column_name>;

-> SELECT 다음의 column_name은 group기준이 되는 column(GROUP BY 뒤에 나오는 것)이 온다.

-> func(column_name)의 의미는, grouping을 할 때, group 기준의 열에 있는 값들이 서로 같은 행 끼리 그룹형태로 모이는데 그 모여진 행끼리 특정 열에 대한 연산을 수행하는 것이다.

-> 대표적인 func로 max, min, avg, count등이 있다.

예시) col1을 기준으로 삼고 max(col2)라고 한다면, 먼저 col1의 값이 동일한 행끼리 모이고 그 행들 중에서 col2값이 가장 높은 행의 값이 표시된다.

col1	col2	name_
11	8	'A'
11	6	'B'
22	2	'C'
33	3	'D'
11	1	'E'
33	7	'F'

SELECT col1, max(col2) FROM tb1 GROUP BY col1;

result:

COL1	MAX(COL2)
22	2
11	8
33	7

ORDER BY <column_name> [ASC | DESC]

지정된 열을 기준으로 행을 정렬한다. ASC는 오름차순이고 DESC는 내림차순이다.

ASC가 기본값이기 때문에 ASC를 사용할 땐 ASC 자체를 생략할 수 있다.

예시) SELECT * FROM <table_name> ORDER BY <column_name>;

AS <name>

특정 구조(주로 column)를 다른 이름으로 부르고 싶을 때 사용한다. 보통 구조 이름 바로 뒤에 이 절을 붙인다.

예시) SELECT COL1 AS PRIMARY, max(col2) AS MAX_ FROM tb1 GROUP BY col1 ORDER BY MAX_ DESC;

PRIMARY	MAX_
11	8
33	7
22	2

 

 

CONSTRAINT 키워드

데이터의 무결성을 위해서 제한 조건을 거는 키워드이다.

대표적인 예시로 PRIMARY KEY가 있다. PRIMARY KEY란, 여러 키(column) 중에서 대표가 되는 키로, 값의 중복이 없어야 하고, NULL값이 없어야 한다.

여기서 constraint_name을 따로 지정하는데, 제약조건 자체를 가리키는 고유 이름이라고 생각하면 된다.

CONSTRAINT <constraint_name> PRIMARY KEY(<column_name>)

테이블 생성 이후 constraint 추가하려면,

ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> PRIMARY KEY(<column_name>)을 사용한다.

constraint 제거하기

ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;

DDL(Data Definition Language)-CREATE/ALTER/DROP OBJECT(TABLE, VIEW, SYNONYM, USER, ...)
DML(Data Manipulation Language)-INSERT/SELECT/UPDATE/DELETE
DCL(Data Control Language)-COMMIT/ROLLBACK

유저의 종류
DBA: DB 관리자 sysdba, DDL, DCL, DML
-> 전체적인 DB시스템 관리
개발자: INSERT/SELECT/UPDATE/DELETE (Java, Python, ...)
-> 테이블 내의 데이터 조작을 담당
사용자: INSERT/SELECT/UPDATE/DELETE
-> DB시스템을 건드리지 않고 사용만 함

DBMS종류
1. ORACLE
-> 대~중 규모의 DB로 주로 사용
2. Informix, Sybase, ...
3. MS-SQL
-> 중~소 규모의 DB로 주로 사용
4. DB2
-> 금융권에서 주로 사용
5. MySQL
-> 소규모의 DB로 주로 사용