SQLAlchemy

---

SQLAlchemy는 대표적인 파이썬 orm이다

ORM이란?

작동원리

SQLAlchemy는 내부적으로 psycopg2 드라이버를 통해 SQL서버와 소통을 한다. 따라서 psycopg2을 import하야하며

database.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
 
SQLALCHEMY_DATABASE_URL = "postgresql://postgres:seol224@localhost/fastapi"
 
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
 
Base = declarative_base()

위의 코드를 통해 엔진을 만든다.

postgresql://<db_user>:<password>@<db_host>/<db_name>을 통해 주소를 등록한다.

model.py

from sqlalchemy import Boolean, Integer, String, Column
from sqlalchemy.sql.sqltypes import TIMESTAMP
from sqlalchemy.sql.expression import text
 
from .database import Base
 
 
class Posts(Base):
    __tablename__ = 'posts'
 
    id = Column(Integer, primary_key=True, nullable=False)
    title = Column(String, nullable=False)
    body = Column(String, nullable=False)
    published = Column(Boolean, server_default='True', nullable=False)
    created_at = Column(TIMESTAMP(timezone=True), 
				    server_default=text("now()"), nullable=False)

그리고 모델을 만들어 SQL데이터베이스에 어떻게 저장될건지 테이블을 만들어준다.

Query

SELECT

from sqlalchemy.orm import Session as session
 
posts = session.query(Posts).all()

SELECT * FROM posts;

Filter

from sqlalchemy.orm import Session as session
 
filterd_posts = session.query(Posts).filter_by(title="dummy title").first()

SELECT * FROM posts
WHERE title="dummy title" LIMIT 1;

INSERT

from sqlalchemy.orm import Session as session
 
new_post = Posts(title="new title", body="dummy body")
session.add(new_post)
session.commit()

INSERT INTO posts (title, body)
VALUES ('new title', 'dummy body');

UPDATE

from sqlalchemy.orm import Session as session
 
post_to_update = session.query(Posts).filter_by(title='first post').first()
post_to_update.body = "new body"
session.commit()

UPDATE posts SET body='new body'
WHERE title='first post';

DELETE

from sqlalchemy.orm import Session as session
 
post_to_delete = session.query(Posts).filter_by(title='first post').first()
session.delete(post_to_delete)
session.commit()

DELETE FROM posts
WHERE title='first post';

---

• SQLAlchemy - The Database Toolkit for Python