SQLAlchemy – Insert Data – 엔지니어 대한민국

시작하기에 앞서 데이타베이스 연결을 우선적으로 합니다.

from sqlalchemy import create_engine
from sqlalchemy import text

uri = 'mysql://DB_USER:DB_PASS@DB_HOST/DB_NAME'

engine = create_engine(uri, echo=False)

코드를 실행하다보면 connection이 끊어질때가 있는데 그때마다 engine을 다시 연결해주시면 됩니다.

그리고 이전시간에 사용했던 테이블을 계속해서 사용할 건데요. 테이블이 데이타베이스에 없으신 분들은 아래 코드를 실행하여 생성해주시기 바랍니다.

from sqlalchemy import Integer, String, ForeignKey
from sqlalchemy import ForeignKey
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user_account"

    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String(30), nullable=False)
    fullname = mapped_column(String(30))
    addresses = relationship("Address", back_populates="user")

    def __repr__(self) -> str:
        return f"User(id={self.id!r})"

class Address(Base):
    __tablename__ = "address"

    id = mapped_column(Integer, primary_key=True)
    user_id = mapped_column(Integer, ForeignKey("user_account.id"))
    email_address = mapped_column(String(30), nullable=True)
    user = relationship("User", back_populates="addresses")

    def __repr__(self) -> str:
        return f"Address(id={self.id!r})"

# 테이블 생성
Base.metadata.create_all(engine)

# 생성한 테이블 삭제
Base.metadata.drop_all(engine)

INSERT 문 사용하기

insert를 구현하는 가장 간단한 방법은 insert()함수를 사용하는 것입니다.

from sqlalchemy import insert

stmt = insert(User).values(name="spongebob", fullname="Spongebob Squarepants")

위에 코드는 insert를 실행한것은 아니구요. Insert를 하는 명령어를 stmt에 저장한 거에요. 출력해보면 다음과 같습니다.

>>> print(stmt)
INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)

위의 statement을 컴파일하면 다음과 같이 넘겨주는 매개변수들을 parsing할 수 있습니다.

>>> compiled = stmt.compile()
>>> compiled.params
{'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}

정의된 statement을 실행하여 데이타를 입력하려면 execute()함수를 실행합니다.

with engine.connect() as conn:
    result = conn.execute(stmt)
    conn.commit()

실행 후 아래 명령을 실행하면 방금 실행한 레코드의 PK를 가져올수 있습니다.

>>> result.inserted_primary_key
(1,)

해당 테이블의 Insert문을 생성하는 방법은 아래와 같습니다.

>>> print(insert(User))
INSERT INTO user_account (id, name, fullname) VALUES (:id, :name, :fullname)

한번에 여러개의 행을 삽입하고 싶다면 아래의 코드와 같이 배열안에 값을 넣으시면 됩니다.

with engine.connect() as conn:
    result = conn.execute(
        insert(User),
        [
            {"name": "sandy", "fullname": "Sandy Cheeks"},
            {"name": "patrick", "fullname": "Patrick Star"},
        ],
    )
    conn.commit()

이번에는 User의 name을 username에 바인드를 해서 Address에 데이타를 넣는데, 이름 데이타만 넣어도 User.id를 자동으로 찾아와서 데이타를 넣어주는 고급기술을 써보도록 하겠습니다.

from sqlalchemy import select, bindparam

scalar_subq = (
    select(User.id)
    .where(User.name == bindparam("username"))
    .scalar_subquery()
)

with engine.connect() as conn:
    result = conn.execute(
        insert(Address).values(user_id=scalar_subq),
        [
            {
                "username": "spongebob",
                "email_address": "spongebob@sqlalchemy.org",
            }, {
                "username": "sandy",
                "email_address": "sandy@sqlalchemy.org",
            }, {
                "username": "sandy",
                "email_address": "sandy@squirrelpower.org",
            },
        ],
    )
    conn.commit()

이렇게 하면 scalar_subq가 서브쿼리로 들어가서 User.id를 가져오는 아래와 같은 쿼리를 생성하여 실행합니다.

BEGIN (implicit)
INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?)
[...] [('spongebob', 'spongebob@sqlalchemy.org'), ('sandy', 'sandy@sqlalchemy.org'),
('sandy', 'sandy@squirrelpower.org')]
COMMIT

이건 별로 중요한건 아닌데 참고로, insert()에 values를 아무것도 넣지않으면 필드와 데이타가 모두 비어있는 Insert문을 반환합니다. 필요한 경우 유용할수도 있겠죠.

>>> print(insert(User).values().compile(engine))
INSERT INTO user_account () VALUES ()

Insert후에 반환되는 값 사용하기

insert문을 만들때 아래와 같이 returning함수를 붙여서 그 안에 반환받을 필드를 나열하면

insert_stmt = insert(Address).returning(
    Address.id, Address.email_address
)

아래와 같이 쿼리를 만들어 주고 쿼리 실행 후에 반환되는 값을 사용할 수 있습니다.

>>> print(insert_stmt)
INSERT INTO address (id, user_id, email_address) VALUES (:id, :user_id, :email_address) RETURNING address.id, address.email_address

Insert.from_select()를 이용하면 Select해온 결과를 Insert할 수도 있습니다.

select_stmt = select(User.id, User.name + "@aol.com")
insert_stmt = insert(Address).from_select(
    ["user_id", "email_address"], select_stmt
)
>>> print(insert_stmt.returning(Address.id, Address.email_address))
INSERT INTO address (user_id, email_address) SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account RETURNING address.id, address.email_address

Resources

Using INSERT Statements

INSERT 문 사용하기

Insert후에 반환되는 값 사용하기

Resources

Insert후에 반환되는 값 사용하기