| CRUD |
SQL 예시 |
SQLAlchemy 2.0 코드 |
결과 반환 메서드 |
| SELECT All |
SELECT * FROM users; |
stmt = select(User) |
db.scalars(stmt).all() (객체 리스트) |
| SELECT One |
SELECT * FROM users WHERE id = 10; |
stmt = select(User).where(User.id == 10) |
db.scalars(stmt).one_or_none() (단일 객체 또는 None) |
| UPDATE |
UPDATE users SET name = 'New Name' WHERE id = 10; |
stmt = update(User).where(User.id == 10).values(name="New Name") |
db.execute(stmt); db.commit( |
| DELETE |
DELETE FROM users WHERE id = 10; |
stmt = delete(User).where(User.id == 10) |
db.execute(stmt); db.commit() |
| INSERT |
INSERT INTO users (name) VALUES ('Dgeon'); |
new_user = User(name='Dgeon');
db.add(new_user);
db.commit() |
db.refresh(new_user) (ID 등을 위해) |
| INNER JOIN |
|
stmt = select(User, Item).join(Item)
select(User, Item).join(Item).where(User.name == Item.title) |
db.execute(stmt).all() (튜플 리스트) |
| LEFT JOIN |
|
stmt = select(User, Item).outerjoin(Item) |
db.execute(stmt).all() (튜플 리스트) |
| COUNT All |
SELECT count(*) FROM users; |
stmt = select(func.count()).select_from(User) |
db.scalar(stmt) (정수 값) |
| COUNT Filter |
SELECT count(*) FROM users WHERE name = 'Alice'; |
stmt = select(func.count()).where(User.name == 'Alice') |
db.scalar(stmt) (정수 값) |