1. DBMS

1.3. SQL

• inner join의 두 가지 방법

  • inner join 절을 사용하는 방법과 from절에 두 개의 테이블을 놓고 where에 join 조건을 명시하는 경우 둘 다 교집합을 구하는 것이지만, 미묘하게 다름

  • CNO	CNAME
    1	London
    2	Paris
    3	Rome
    4	Vienna

  • CNO	CNAME
    1	London
    2	Paris
    3	Rome
    4	Vienna

-- inner join절 사용 -> 이게 보통 생각하는 교집합
SELECT
	*
FROM
	city
inner join supplier
	ON city.cno = supplier.cno;
    -- from절에 두 개의 테이블을 놓고 where절에 join 조건 명시-> 크로스 조인 후 CNO가 동일한 애들만 필터링
SELECT
	*
FROM
    city, supplier
WHERE
	city.cno = supplier.cno;

=> 왜냐하면 from절에 두 개의 테이블을 명시하면 cross join과 동일하게 사용되기 때문!!!

※ 아래 내용은 수업내용 중 중요한 부분만 간단히 정리한 것이므로 이 .pynb 파일을 참고할 것! 단, 이 파일은 수업을 들으면서 막 적은거라 조금 뒤죽박죽일 수도 있음,,

1.4. SQLite

SQLite을 파이썬에 임베딩하여 사용해보자. SQLite는 파일 기반의 RDBMS이고 나만 사용하기 때문에 트랜젝션이나 권한 설정을 하지 않아도 되는 장점이 있다.

모든 DBMS가 동작하는 데에는 동일한 구조가 사용된다. 사용자가 DBMS에게 어떤 명령을 내리면 Connection에 접속한다. 사용자는 DB와 직접 소통하는 것이 아니기 때문에 DBMS에게 명령하므로써 DB에있는 데이터를 간접적으로 접근하게 된다. DBMS는 Cursor를 이용해 작업을 수행한다.

따라서 SQLite도 사용하기 위해서는 Connection을 우선적으로 만들고, Cursor를 만들어야한다! Connection은 DBMS에 관련된 명령들(commit, rollback 등)을 처리하고 Cursor에는 쿼리 실행, 결과 출력과 관련된 명령을 처리한다.

1.4.1. Connection과 Cursor 만들기

Python에서 SQLit을 사용하기 위해서 Connection과 Cursor을 만들어보자

# Connection과 Cursor 만들기
import sqlite3

con = sqlite3.connection(':memory:')
cur = con.cursor()

sqlite3.connection(':memory:') 를 하게 되면 DB가 열리게 된다. 메모리로 아규먼트를 지정했으므로 파일로 저장되지는 않고 메모리 상에만 올려진 상태이다.

주의할 점은 DB를 열었으면 작업이 다 끝나고 반드시 닫아줘야 한다는 점이다.

# DB 닫기
con.close()

1.4.2. execute와 fetch

쿼리를 실행하기 위해서는 Cursor의 두 가지 메소드를 이해해야 한다. execute와 fetch이다. execute는 쿼리를 실행하는 명령이고 fetch는 그 결과를 반환하라는 명령이다.

기본적으로 Cursor는 실행 결과를 메모리에 올려놓고 그 메모리 주소를 반환한다. 그리고 fetch 명령이 떨어지면 그 메모리 주소에 가서 값을 불러오고 결과의 다음 row로 이동한다.

cur.execute('''
CREATE TABLE CITY(
    CNO    INTEGER  PRIMARY KEY,
    CNAME  TEXT
);
''')

위 코드를 실행하면 repr은 <sqlite3.Cursor at 0x24bf238fc00>로 나온다.(메모리 주소가 반환됨)

위 쿼리는 데이터를 조회하는 쿼리가 아니기 때문에 fetch 명령을 줘도 나오는게 없다.

execute는 3가지 종류가 있으며 약간씩 다르다.

• execute : 하나의 쿼리를 한 번 실행한다.
• executemany : 하나의 쿼리를 n번 실행한다.
• executescript : 여러 개의 쿼리를 한 번에 실행한다.

이 중 executescript는 표준 사용법이 아니며 중간에 한 쿼리에서 에러가 나오면 그냥 동작을 멈추기 때문에 어디에서 에러가 났는지 일일히 찾아야되고, 무엇보다 롤백에 신경써야한다. 따라서 롤백하는 법도 알아야 한다. 롤백은 con.rollback()을 통해 해줄 수 있다.

fetch도 3가지 종류가 있다.

• fetchone : 데이터 한 개를 가져온다.
• fetchmany : 데이터 n 개를 가져온다.
• fetchall : 모든 데이터를 가져온다.

1.4.3. 데이터를 변수에 담아서 쿼리 실행하기

SQLite에서 변수를 담아서 사용하는 방법은 2가지 종류가 있다.(Python에서 string을 print할 때 format을 지정하는 것과 비슷하다.)

• Qmark style : 쿼리에 ?로 위치를 찍고 python의 iterable 객체 값을 넣음
• named style : 쿼리에 :key로 위치를 찍고 python의 dict 객체 값을 넣음

# qmark 스타일
cur.execute('''
    INSERT INTO CITY(CNAME)
    VALUES(?)
''', ('동대문구', ))

cur.execute('SELECT * FROM CITY;')
cur.fetchall()

# named 스타일
cur.execute('''
    INSERT INTO CITY(CNAME)
    VALUES(:gu)
''', {'gu' : '중랑구'})

cur.execute('SELECT * FROM CITY;')
cur.fetchall()

1.4.4. 파일로 저장하기

위에서 Connection을 만들 때 ':memory:'로 설정했다. 이 때 경로를 지정하면 그 위치에 db 파일을 저장한다.

# 현재 경로에 db 파일을 만들기
con = sqlite3.connect('fnb.db')
cur = con.cursor()

db를 저장했다고 하더라도 테이블을 만들거나 데이터를 밀어넣는 것들이 바로바로 물리적으로 저장되는 것은 아니다. 다른 DBMS처럼 트랜젝션 관리를 하기 때문이다. 따라서 con.commit()을 통해 물리적으로 저장한다.