1. SQL vs SQLAlchemy Core 메서드 매핑
| SQL 구문 | SQLAlchemy Core 메서드 | 설명 |
|---|---|---|
| CREATE DATABASE | engine.execute("CREATE DATABASE") | 데이터베이스 생성 |
| CREATE TABLE | Table(...), metadata.create_all(engine) | 테이블 구조 정의 및 실제 테이블 생성 |
| INSERT INTO...VALUES | table.insert(), conn.execute(...) | 테이블에 데이터 추가 |
| SELECT ... FROM ... | select() | 특정 조건을 만족하는 데이터 조회 |
| WHERE 조건 | .where() | 특정 조건을 만족하는 데이터 필터링 |
| ORDER BY | .order_by() | 특정 컬럼 기준으로 데이터 정렬 |
| LIMIT 숫자 | .limit() | 조회 결과 개수 제한 |
| COUNT(컬럼) | func.count(table.c.column) | 특정 컬럼의 개수 세기 |
| AVG(컬럼) | func.avg(table.c.column) | 특정 컬럼의 평균 계산 |
| GROUP BY 컬럼 | .group_by() | 특정 컬럼 기준으로 데이터를 그룹화 |
| HAVING 조건 | .having() | 그룹화된 결과에 조건 필터링 |
| AS 별칭 | .label(별칭) | 칼럼이나 집계 결과에 별명 지정 |
| JOIN | .join() | 여러 테이블을 연결하여 데이터 조회 |
| UPDATE...SET...WHERE | update().where().valuses() | 테이블의 특정 데이터 수정 |
| DELETE FROM ...WHERE | delete().where() | 테이블에서 특정 데이터 삭제 |
| COMMIT | conn.commit() | 데이테버이스 변경사항 최종반영(필수!!) |
[Colab에서 SQLAlchemy 사용하는 법]
- Google Colab이나 Jupyter Notebook 같은 환경에서 '서버 인프라 구축'과 '환경 설정'을 한꺼번에 수행하는 코드
- Colab은 브라우저를 닫고 일정 시간이 지나면 할당되었던 가상 컴퓨터가 삭제되기 때문에 다시 접속했을 때 초기화되어 있다면 매번 실행해야 함_
[사용할 데이터]
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Float, DateTime, ForeignKey, select, func, update, delete, text
from datetime import datetime
.
➡️ import문은 "내 컴퓨터에 설치된 도구 상자(SQLAlchemy)에서 특정한 도구(함수, 클래스)들을 꺼내오겠다"고 선언하는 과정
➡️ 따라서 이 코드는 파이썬 파일(.py)이나 주피터 노트북(.ipynb)을 새로 만들 때마다 상단에 꼭 한 번은 입력해야 함.
데이터베이스 만들기
with engine.connect() as conn:
conn.execute(text("CREATE DATABASE IF NOT EXISTS counsel_db"))
conn.commit()
.
➡️ engine.connect() 만들어진 database에 접속한다는 의미
➡️ CREATE DATABASE IF NOT EXISTS counsel_db" : counsel_db이라는 데이터베이스를 만들어달라는 말
➡️ conn.commit() : 최종적으로 데이터베이스를 만든다고 확인해주는 역할
engine = create_engine('mysql+pymysql://root:@localhost/counsel_db')
metadata = MetaData()
.
➡️ 다시 engine을 언급한 이유: USE DATABASE같이 앞으로 이 데이터베이스를 이용할거라는 의미로 한번 더 쓰임
2. 데이터 조회하기
SELECT 문
- 데이터베이스에서 정보를 가져오는 가장 기본적인 작업
➡️ SQLAlchemy Core : (예) select(patients): patients
➡️ SQL: SELECT * FROM patients
2-1. WHERE
- 특정 조건을 만족하는 행(레코드)만 선택할 때 사용
SQL에서는?:
SELECT * FROM clients WHERE age >= 20;
💡 with engine.connect() as conn:
➡️ with : “연결을 열고 → 작업하고 → 자동으로 정리하고 닫아줌(자원관리)
➡️ with 아래 적힌 코드가 전부 실행되고 나면 데이터베이스와의 연결이 닫혀서 불필요한 데이터 손실을 막음
💡 as conn은 engine.connect를 conn으로 줄여서 쓰겠다는 의미
💡 fetch
1️⃣ fetchall(): 전부 가져오기
2️⃣ fetchone(): 한 줄만 가져오기
3️⃣ fetchmany(n): n개의 줄 가져로기
2-2. 결과 정렬 (ORDER BY)
- order_by() 메서드는 SQL의 ORDER BY 절처럼 조회된 결과를 특정 컬럼을 기준으로 정렬할 때 사용
- patients.c.age.desc(): age 컬럼을 기준으로 내림차순(descending)으로 정렬
- 오름차순은 .asc()를 사용하거나 생략(기본값)
SQL에서는?:
SELECT * FROM clients ORDER BY clients.age DESC;
2-3. 결과 개수 제한 (LIMIT)
- limit() 메서드는 SQL의 LIMIT절처럼 조회 결과의 개수를 제한할 때 사용
- 주로 상위 N개의 데이터를 확인할 때 유용
SQL에서는?:
SELECT * FROM clients LIMIT 2;
‼️ SQL에서 LIMIT 사용 시 ()사용 안함!!!
3. 데이터 집계 (COUNT, AVG, GROUP BY, HAVING)
3-1.집계 함수 (COUNT, AVG)
- func.COUNT()/AVG()/SUM()/MAX()/MIN()
‼️ SQL에서는?:
SELECT COUNT(id), AVG(satisfaction) FROM sessions;
3-2.별칭 지정 (LABEL/AS)
- SQL의
AS키워드처럼 컬럼이나 계산된 결과에 읽기 쉬운 별명(Alias)을 붙일 때 사용
‼️ SQL에서는?:
SELECT
COUNT(id) AS 상담횟수,
AVG(satisfaction) AS 만족도
FROM sessions;
3-3. 그룹화 (GROUP BY)
- SQL의 GROUP BY 절처럼 특정 컬럼의 동일한 값을 기준으로 데이터를 그룹으로 묶음
‼️ SQL에서는?:
SELECT concern, COUNT(id) FROM clients GROUP BY concern;
3-4. 그룹 필터링 (HAVING)
- group_by()로 그룹화된 결과에 대해 조건을 필터링할 때 사용
- WHERE절이 개별 행에 조건을 적용한다면, HAVING절은 그룹 전체에 조건을 적용
‼️ SQL에서는?:
SELECT counselor_id, COUNT(id) AS 상담횟수
FROM sessions GROUP BY counselor_id
HAVING COUNT(id) >= 2;
4. 데이터 조인(Join)
- 여러 테이블에 흩어져 있는 관련 데이터를 하나로 합쳐서 조회할 때 사용
- select_from() : 어떤 테이블에서부터 조인을 시작할지 명시적으로 지정
- join(다른테이블, 조인조건) : 기본적으로 INNER JOIN을 수행
- isouter=True: LEFT JOIN이 되어, 왼쪽 테이블(시작 테이블)의 모든 데이터를 유지하고 오른쪽 테이블에 정보가 없으면
NULL로 표시
‼️ SQL에서는?:
SELECT clients.name, sessions.satisfaction FROM clients
INNER JOIN sessions ON clients.id = sessions.client_id;
LEFT JOIN시 실수형으로 출력되는 이유
➡️ NaN이 숫자가 아니기 때문에 자연수가 아니라고 판단해서 정수형으로 출력이 안됨.
5. 데이터 수정 및 삭제
5-1. 변경사항 반영 (conn.commit())
- update()나 delete() 같은 데이터 변경(DML: Data Manipulation Language) 작업은 conn.execute()로 실행한 후, 반드시 conn.commit()을 호출해야 데이터베이스에 최종적으로 변경사항이 반영됨
*commit()을 하지 않으면 변경사항은 임시 상태로만 존재하다가 연결이 끊기면 사라질 수 있다. - 이는 데이터의 일관성과 무결성을 보장하기 위한 중요한 절차
5-2. 데이터 추가(INSERT)
단일 추가 .values() 사용
- 쿼리문 안에 데이터 값을 직접 고정해서 보냅니다.
복수 추가 (리스트 전달)
- insert(clients)라는 빈 틀만 먼저 만들고, 실제 데이터는 따로 리스트(list) 형태로 묶어서 추가
‼️ SQL에서는?:
➡️ INSERT INTO clients (name, age, concern) VALUES ('가나영', 20, '대인관계');
.
➡️ INSERT INTO clients (name, age, concern)
VALUES
('다라영', 20, '직장 스트레스'),
('마바영', 20, '취업');
5-3.데이터 수정 (UPDATE)
- update(테이블).where(조건).values(수정할_값)
- update(patients): patients: 테이블의 데이터를 수정하겠다는 의미
- ⭐️
WHERE절을 사용하지 않으면 테이블의 모든 데이터가 수정되므로 주의
‼️ SQL에서는?:
UPDATE clients SET concern = '진로변경' WHERE name = '이민지';
5-4 데이터 삭제 (DELETE)
- delete(테이블).where(조건)
- ⭐️WHERE절을 사용하지 않으면 테이블의 모든 데이터가 삭제되므로 극도로 주의해야 함!
‼️ SQL에서는?:
DELETE FROM sessions WHERE client_id = 3;
DELETE FROM clients WHERE id = 3;
