01. 너무나 쉬운 sqllite3 (파이썬으로 하는 sql 기초)

sql은 DB를 다루기 위한 기초적인 언어이다.

간단하게 DB를 이해하는 것에 필요한 기초적인 지식을 알아보자.

 

DB는 정보를 저장하는 것이다. 제품에 대한 것, 고객에 대한 것, 직원들에 대한 것 등을 저장해두면

보안, 마케팅, 전략 수립, 그리고 간단한 로그인 부터 모두 DB의 역할이 없다면 불가능하다.

 

우선 야무지게 sqllite3를 임포트해준다.

dbpath는 자신이 불러올 DB의 이름이나, 그 DB가 없을 경우 새로운 이름으로 생성된다.

connect 함수에 넣어주어 접속 통로 conn을 만들어준다.

아래는 conn이 쓸 수 있는 5가지 함수이다. 

corsor() 함수 같은 경우는 우리가 DB를 조작할 때 마우스에 해당하는 개념이다. 객체를 생성해서 많은 다룰 수 있다.

commit()은 백업, rollback()은 마지막으로 commit을 했을 때로 돌아간다.

close()는 우리가 연 connect를 해제한다.

우선 우리가 DB를 다루려면 corsor() 함수 객체를 만들어 다루어야 한다.

자 이렇게 corsor()를 만들어 두었다. cursor로 조작할 수 있는 함수는 

이 것이다. 우선

execute 형제들은 sql문을 실행하는 함수 이다.

execute() 한 개의 sql문을 실행할 때 사용한다.

executescript()는 여러 개의 sql문을 실행할 때 사용한다.

executemany()는  동일한 sql문을 여러번 실행할 때 사용한다.

fetch() 함수들은 나중에 알아보도록 하자.

 

우리는 script라는 문자열안에다 sql문을 넣고 위 execute함수를 사용해 연결된 DB에 명령을 내릴 것이다.

이런 식으로 """ """ 안에다 sql문을 넣어준다. employees라는 테이블을 만들도록 하겠다.

우선 현재 생성된 DB에  테이블 즉 정형데이터를 담는 표가 없기 때문에 우리는 테이블을 새로 만들어야 한다.

그 전에 테이블을 정의하기 위해 사용하는 언어를 간단하게 살펴보자.

CREATE는 테이블을 만들고, ALTER는 테이블을 변경, DROP은 삭제한다. 이 명령어들은 

데이터베이스 특성상 많이 하지 않는다. 이 점을 알고 아래를 보자.

위의 생성한 문자열 안에 """ """ 안에 DROP이라는 삭제하는 명령어를 적어준다 TABLE IF EXISTS 는 테이블이 존재할 경우를 의미한다. 만일 우리가 이전에 employees라는 테이블을 만들었다면 그 테이블을 삭제해주는 것이다.

왜냐하면 같은 이름의 DB가 있다면 생성되지 않는다. 물론 편의상 적어둔거지 우리가 DB를 삭제할 일도 거의 없을 것이다. 여러 개의 sql문을 실행할 때는 끝에 ;로 구분해준다.

CREATE를 사용해 TABLE을 만든다. 이름을 emplyees라고 만들어 주었다. 안에 column이 되는 값을 하나씩 넣어준다.

(열이름) (타입) (기타 설정) 순을 입력해주면 된다. 

우선 첫번째 열이름을 id로 해준다. INTEGER타입으로 숫자를 입력해주는 값으로 지정을 하고 인덱스 역할을 할 수 있게, PRIMARY KEY값을 넣어준다. 이 것을 넣어주면 각 행이 구별되는 고유한 값을 가지게 되는 것이다. 이 것은 이후에 다양하게 활용할 수 있다. AUTOINCREMENT를 사용하면 이 열에값을 입력하지 않아도 자동을 1부터 오름차순으로 입력된다.

다음 열의 이름은 name이다. type은 TEXT로 해주고 추가 설정으로 NOT NULL을 해준다 NOT NULL을 해주면 우리가 이후 데이터를 입력할 때 NULL값이 들어오면 즉, 입력을 해주지 않는다면 오류를 발생시킨다. 그러니 꼭 값을 입력하라는 의미이다.

이후는 원하는 열의 이름 그리고 type을 입력하면 된다. 

이제 script를 담은 것을 커서로 실행시켜주자. 여러개의 sql문이니까 executescript()를 사용한다.

커넥션이 연결되어 있는 통로인 conn에게 commit() 함수를 실행해주면 지금까지 해온 명령이 save가 된다. 

(매번 해줄 필요는 없다고 생각한다.)

이제 하나의 TABLE을 만들었으니 데이터를 저장할 차례이다.

위에서 우리가 데이터를 정의할 때 SQL문을 사용했다면 이번엔 조작할 때 사용한다. 

SELECT는 우리가 가장 많이 사용하는 명령어이다. DB에서 정보를 읽어오는 명령어로 SQL문 중에 가장 많은 비중을 차지하고 있다. INSERT는 데이터를 추가할 때 사용하는 명령어.  UPDATE는 기존의 데이터를 수정하는 명령어. DELETE는 삭제하는 명령어이다.

우리가 데이터를 추가하려면 순서에 맞게 입력해야한다. 기억이 안난다면 위에 CREATE문을 참고하자.

커서를 이용해 executemany를 이용하면 한 sql문을 여러번 사용할 수 있다. INSERT INTO 테이블명(인자)를 사용하면 된다. VALUES()안에 두 번째 인자인 리스트가 들어간다.

우리가 주로 쓸 SELECT 문이다. "SELECT * FROM employees;"의 서순을 보자면 "* 열을 가져온다. employees라는 TABLE로부터" 로 해석하면 된다. *는 모든 열을 의미한다. 이렇게 SELECT문을 사용하면 cur 객체가 해당하는 데이터를 가리키게 된다. 이때 우리는 fetchall()을 사용하면 된다.

fetchall()을 사용하면 SELECT문을 실행해서 가리키게 된 데이터를 가져온다. fetchone()은 하나의 행만 가져오고

fetchall()은 전부를 가져온다.

그리고 리스트의 형태로 가져오게 되는데 이는 위의 코드로 확인할 수 있다. 하지만 fetch()는 우리가 pandas로 가져온다면 거의 쓰일 일이 없다.

description을 사용하면 지금 커서가 위치한 테이블이 모두 어떤 열을 가지고 있는 지 알 수 있다.

자 fetch를 쓰지 않고 데이터를 가져오는 방법이다. script만 저 문자열로 넣어주면 우리가 바로 가져와 데이터프레임으로 활용할 수 있다.

 

자 이렇게 커넥션을 종료해주어야 더이상 메모리를 차지하지 않게 된다. close를 사용하지 않는다면 계속 켜져있는 상태일 것이다.

자 다시 새로운 db를 만들고 커넥트를 한 후에 커서를 만들어주자. 해당하는 db가 없으면 새로운 파일이 생성된다.

우리는 관계형 db의 맛을 보기 위해 세 개의 db를 만들도록 하겠다. 세 개 중 하나는 두개를 연결짓는 DB이다.

여기서는 연락처를 저장하는 DB. 그리고 그룹에 대한 DB. 연락처와 그룹을 묶는 DB (관계)이다.

 

 

참고로 contact_id의 PRIMARY KEY 값은 저 때 지정해주지 않고 괄호 안에서 따로 지정해주어도 가능하다.

PRIMATY KEY (contact_id) 이런식으로 넣어주면 된다.

잘 만들어진걸 확인할 수 있다.

자 이제 말 안해도 이해할거라 믿는다.

이 테이블은 관계형 데이터의 모습을 보여주기 위함이다. 이 연락처는 이 group이다라는 것을 저장하기 위한 db로 이해하면 된다. 나중에 복습하며 더 익숙해질 것이다.

 여기서 FOREIGN KEY는 처음 보는 것인데 CREATE에서 쓰이는 이 것의 의미는 해당 테이블에서 가져온 값이라는 것이다. 그래서 나는 처음에 contact TABLE에서 추가를 하면 이 TABLE에서도 그 값을 가져와 자동으로 추가시켜주는 줄 알았는데, 그게 아니라 이것을 정의해주는 이유가 contact 테이블에 있는 contact_id 값과 contact_groups의 contact_id 값이 명시적으로 같은 값이다라는 것을 보여주는 것이다. 또한 이렇게 연결 지어줄 경우 entity 즉 다른 개체, TABLE의 해당 값이 삭제될 경우 여기에 저장된 값도 삭제할 수 있는 설정을 추가할 수 있다.

 즉 어느 연락처가 필요 없어 지울 경우 연락처와 group을 연결짓는 이 DB에서도 그 연락처의 id를 가진 행이 삭제 되게 된다.

자 devices라는 테이블을 만들어보자. CREATE를 할 때 보이는 UNIQUE라는 설정은 중복된 값이 들어올 경우 오류를 일으킨다. 각기 개별적인 값만을 받는다고 생각하면 된다. 

가 이렇게 멀쩡하게 나왔다.

자 만일 devices의 이름을 equipment로 바꾸고 싶다. 그럼

ALTER TABLE (테이블 명) RENAME TO (바꾸고 싶은 테이블 명) 으로 SQL문을 실행해주면 된다.

 

여기서 잠깐 내가 지금까지 만든 테이블을 보고 싶다? 그럼

이렇게 만들어주면 된다. name이 sqllite 어쩌구면 자동으로 생성되는거니까 신경쓰지 말자.

sqllite_master 같은 경우는 우리가 쓰고 있는 sqlite의 정보를 볼 수 있는 특별한 값이다! 필요할 때 종종 보자.

 

이런 식으로 열을 추가해줄 때는 ALTER TABLE (table 명) ADD COLUMN (추가하고 싶은 column명) (속성)

을 사용해준다. 그러면 

이렇게 열을 추가해줄 수 있다.

만일 열의 이름을 변경해주고 싶은 경우

ALTER TABLE (테이블명) RENAME COLUMN (칼럼명) TO (바꾸고 싶은 칼럼명)

이렇게 입력해주면 된다.

 sqllite에서는 열 삭제를 지원해주지 않는다. 그렇기 때문에 기존 테이블에서 다른 테이블로 필요한 열만 저장한 다음에

DROP TABLE을 해야한다.

자 위 코드에서 봐야 할 건 INSERT이다. 우리는 삭제할 열을 그대로 가져와 새로운 테이블에 넣고자 한다.

INSERT INTO (테이블 명) SELECT (추가할 열들) FROM (열을 가져올 테이블) 이런식으로 문법을 사용하면 된다.

그리고 DROP TABLE 쏼라쏼라 하면 된다. 오늘 이상 끗~