Data Analyst로 취업하기 (5) SQL_3

Data Analyst로 취업하기 (4) SQL_2

이전글에 database 를 생성/삭제 하고, 생성된 database에 cursor 까지 연결했었다.

이제는 cursor 를 활용해서 작성된 sql 을 통해 table 을 CRUD 해보자

시작하기전에 먼저 MySQL cheat sheet 을 검색해본다.

앞으로 자주보자 cheat sheet

가장 위의 사이트가 제이 가독성이 좋은데, 혹시 모르니까 나머지 2개의 사이트도 중간중간 확인해야겠다.

Create - Read - Update - Delete 순서로 공부한다.

Create

다행이도 생성은 CREATE 이라는 단어를 명령어로 사용한다.

CREATE TABLE TABLE_NAME ( columns ); 를 통해 table 을 생성하면 된다.

이때 같은 이름의 table 이 존재하면 에러가 나는데 이를 방지하고자 한다면,  IF NOT EXISTS 를 추가하자

 

# CREATE TABLE [IF NOT EXISTS] table_name(
#   column_list
# );

cursor = connect_db(name="studysql")

# CREATE DATABASE IF NOT EXISTS USERS( columns );
sql = '''
    CREATE TABLE USERS (
        UserID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
        LastName varchar(255),
        FirstName varchar(255),
        City varchar(255)
    )AUTO_INCREMENT=1;
'''

cursor.execute(sql)

# >>> cursor.execute(sql)
# 0

 

UserID 의 경우 몇번째 유저인지 알 수 있어야 하기 때문에,

NULL 값을 가지지 않게 하고 (NOT NULL) 따로 자동으로 값이 이전의 UserID 보다 커지도록 하고 (AUTO_INCREMENT) AUTO_INCREMENT를 사용하기 위해서는 해당 field가 PRIMARY KEY 이어야 한다. INCREMENT 값은 1로 한다.

제일 하단처럼 cursor.execute() 함수를 이용하면 정수가 반환되는데, 검색해보니 함수에 들어간 sql 로 인해서 변화된 row 의 값을 반환한다고 한다.

새로운 table 이 생성되었으니까 1이 아닐까 생각했는데, 바로 아니라는걸 알게되었다..

Read

생성한 table 을 읽을때는 아쉽게도 READ 가 아닌 SELECT 라는 명령어를 사용한다.

또한 SELECT USERS; 라고 하면 되는게 아니라 더 복잡하게 SELECT * FROM USERS; 라고 해줘야 한다.

여기서 '*' 은 모든 column 을 의미하다. 만약 * 대신에 column 의 이름을 넣어주면 해당 column 만 볼 수 있다.

row 를 기준으로 table 을 보고 싶으면 WHERE 이라는 명령어로 조건식을 넣어줘야 한다.

예를 들어, SELECT City FROM USERS WHERE UserID = 1; 이란 sql 이 작성되면, UserID가 1 인 유저를 확인 할 수 있다.

대부분의 조건식은 일반적으로 쓰이는 방법과 차이가 없는데, 문자열의 경우에 조금 차이가 있다.

LIKE 라는 명령어를 사용하는데, 여러 방법들이 더 있지만 우선 가장 많이 쓰일 경우만 보자

'start' 라는 문자열을 가장 앞에 포함한 row 를 찾기 원할경우는 'start%'

'end' 라는 문자열을 가장 뒤에 포함한 row 를 찾기 원할경우는 '%end'

'mid' 라는 문자열을 중앙에 포함한 row 를 찾기 원할경우는 '%mid%'

숫자와 문자열에 대해서만 조건식을 걸어줘도 충분히 원하는 table을 Python 으로 가져올 수 있다. 

sql = 'SELECT * FROM USERS;'
cursor.execute(sql)

# fetchall()
# Fetch all the rows.
rows = cursor.fetchall()
print(rows)

# SELECT select_list FROM table_name WHERE condition;
# sql = 'SELECT City FROM USERS WHERE UserID = 1;'
# cursor.execute(sql)

# SELECT * FROM USERS WHERE LastName LIKE 'start%';
# SELECT * FROM USERS WHERE LastName LIKE '%end';
# SELECT * FROM USERS WHERE LastName LIKE '%mid%';

# >>> print(rows)
# ()

 

어? 결과값이 뭐지 싶었다.. table 은 생성했지만 어떠한 값도 입력하지 않았기 때문에 아직은 비어있는 table 이다..

Update

사실 수정은 케이스가 조금 많아서 한번에 내용을 다 정리하기 어려울 것 같다.

우선 간단하게 row 혹은 record 를 입력/삭제 만 알아보고 나중에 따로 update 만 더 알아봐야할꺼 같다.

먼저 table 에 record 를 입력할때는 INSERT INTO 라는 명령어를 쓴다.

주석처리 한 첫줄처럼 해당 table 에 있는 column 이름에 VALUES 를 통해 data 를 넣어준다.

# INSERT INTO table1 (field1, field2) VALUES (value1, value2);

# row
sql = '''INSERT INTO USERS (UserID, LastName, FirstName, City) VALUES (0, 'JAME', 'LEBRON', 'LA');'''
cursor.execute(sql)
conn.commit()

# multiple rows
sql = '''INSERT INTO USERS (UserID, LastName, FirstName, City) VALUES (0, 'JORDAN', 'MICHAEL', 'CHICAGO'), (0, 'BRYANT', 'KOBE', 'LA');'''
cursor.execute(sql)
conn.commit()


sql = 'SELECT * FROM USERS'
cursor.execute(sql)
rows = cursor.fetchall()
print(rows)

# >>> print(rows)
# ((1, 'JAME', 'LEBRON', 'LA'), (2, 'JORDAN', 'MICHAEL', 'CHICAGO'), (3, 'BRYANT', 'KOBE', 'LA'))

 

입력해준 record 는 DELETE FROM table WHERE 명령어를 이용해서 삭제 가능하다.

# INSERT INTO table1 (field1, field2) VALUES (value1, value2);
# DELETE FROM table WHERE id = 1;

DELETE FROM USERS WHERE UserID = 1;

sql = 'SELECT * FROM USERS'
cursor.execute(sql)
rows = cursor.fetchall()
print(rows)

>>> print(rows)
((2, 'JORDAN', 'MICHAEL', 'CHICAGO'), (3, 'BRYANT', 'KOBE', 'LA'))

 

Delete

마지막으로 table 을 삭제하는 명령문은 DROP 이다. 생성할때와 유사하게 IF EXISTS 를 사용 할 수 있다.

잘 삭제 되었는지 SHOW TABLES; 를 통해 확인해보자

# sql = 'DROP TABLE IF EXISTS table;'

sql = 'DROP TABLE IF EXISTS USERS;'
cursor.execute(sql)
conn.commit()

sql = 'SHOW TABLES;'
cursor.execute(sql)
conn.commit()
rows = cursor.fetchall()
print(rows)

# >>> print(rows)
# (('cars',), ('houses',))

 

 

이렇게 Create - Read - Update - Delete 를 간략하게 정리해봤다.

개인적인 생각으로는 Data Analyst 의 포지션에서는 database 를 관리하거나 하지 않기 때문에, Read 부분만 가능해도 충분할 것 같기도 하다. 모든 data 를 다 가져오기엔 비효율적이니 database 에 어떤 table 들이 있고, 해당 table 의 값에 대해서 알고 필요한 table 만 가져올 수 있는 정도면 충분하지 않을까 하는 생각이 든다.

다음에는 MySQL table 을 pandas dataframe 으로 만들고, pandas dataframe 을 MySQL로 변환하는 작업을 해봐야겠다.