[4주차 총정리] Python 기반 SQL 프로그래밍(4) _DML 실습

import sqlite3

dbpath = "chinook.db" 
conn = sqlite3.connect(dbpath)
cur = conn.cursor() 

# conn.cursor(), conn.commit/rollback(), conn.close()
# cur.execute/executescript/executemany(), cur.fetchone/fetchall()

 

---

DDL (Data Manipulation Language, 데이터 조작 언어)

SELECT (Read) / INSERT (Create) / UPDATE (Update) / DELETE (Delete)   => CRUD

1) SELECT (+FROM)

cur.execute("SELECT * FROM tracks;") # "*" means "all columns"
result = cur.fetchall()
print(result[0])
# >(1, 'For Those About To Rock (We Salute You)', 1, 1, 1, 'Angus Young, Malcolm Young, Brian Johnson', 343719, 11170334, 0.99)


import pandas as pd
df = pd.read_sql_query("SELECT * FROM tracks;", conn)
df.head(1)

 

---

2) INSERT (+INTO & VALUES)

script = """
INSERT INTO artists (name) -- artists 테이블에(into) 새로운 값을 삽입(insert)
VALUES('Bud Powell'); -- 넣어줄 값들(values)을 지정
""" 
cur.execute(script) 

script = """
SELECT
    ArtistId,
    Name
FROM
    Artists
ORDER BY
    ArtistId DESC; -- 새로 넣어준 Artist가 가장 마지막에 위치 -> 내림차순으로 정렬해 확인 
    -- 혹은 df.tail()로 확인 가능
""" 
cur.execute(script) 

df = pd.read_sql_query(script, conn)
df.head()

 

# 방법1) 여러 행(record)을 병렬적으로 소괄호에 담아 동시에 삽입
script = """
INSERT INTO artists (name)
VALUES 
    ("Buddy Rich"),
    ("Candido"),
    ("Charlie Byrd");
""" 
cur.execute(script) 


# 방법2) DATA 묶음 한번에 삽입
script = """
INSERT INTO artists (name) VALUES("?")
""" 
data=[("Buddy Rich"),
    ("Candido"),
    ("Charlie Byrd")]
    
cur.executemany(script) 


script = """
SELECT
    ArtistId,
    Name
FROM
    Artists
ORDER BY
    ArtistId DESC;
""" 
cur.execute(script) 

df = pd.read_sql_query(script, conn)
df.head()

 

---

3) UPDATE (+SET)

# 예시1
script = """
UPDATE employees
SET lastname = 'Smith' -- 아래 WHERE 문으로 선택된 행에 대하여 lastname 열의 값을 'smith'로 설정(SET)해줍니다.
WHERE employeeid = 3; -- WHERE 문이 먼저 실행됩니다. (employeeid 열의 값이 3인 행이 먼저 선택된 후, SET 문으로 수정됩니다.)
""" 
cur.execute(script) 


# 예시2
script = """
UPDATE employees
SET city = 'Toronto', 
    state = 'ON',
    postalcode = 'M5P 2N7' -- 이처럼 여러 열에 대해 동시에 수정(SET)이 가능합니다.
WHERE
    employeeid = 4;
""" 
cur.execute(script) 


# 예시3
script = """
UPDATE employees
SET email = UPPER(firstname || "." || lastname || "@chinookcorp.co.kr");    
-- || : concatenate (더하기 기능)
-- ex) UPPER(andrew.adams@chinookcorp.co.kr) 
-- 이처럼 WHERE 문이 없을 경우 모든 행에 대해 동시에 적용 가능
""" 
cur.execute(script)

 

---

4) DELETE (+FROM)

script = """
DELETE FROM employees
WHERE employeeid = 2;
""" 
cur.execute(script)