Zenn
Open7

fastapi sqlalchemy ためす

初心者
FastAPI
SQLAlchemy
alembic
PostgreSQL
jimiijimii

今回のGoal

PythonのWebフレームワーク(fastapi)でORM(sqlalchemy)を用いてDB(postgreSQL)へCRUD操作を行う

PC環境

sw_vers
ProductName:		macOS
ProductVersion:		14.2.1
BuildVersion:		23C71

準備する環境

ローカルでPython, fastapi, sqlalchemy, postgresqlが実行可能

jimiijimii

環境構築

楽だし環境汚さないので、今回はdev containerを使ってみようと思う。

やったこと

ディレクトリ作成し、vscodeで開く




Ubuntuである必要は無い気はする




jimiijimii

alembicによるDBのマイグレーション

sql alchemyを使う場合のマイグレーションのパターン

Alembic, Celery, RQ, or ARQ.

Alembic

https://alembic.sqlalchemy.org/en/latest/tutorial.html

init

alembic init migration
  Creating directory '/workspaces/fast-api-tutorial/migration' ...  done
  Creating directory '/workspaces/fast-api-tutorial/migration/versions' ...  done
  Generating /workspaces/fast-api-tutorial/alembic.ini ...  done
  Generating /workspaces/fast-api-tutorial/migration/env.py ...  done
  Generating /workspaces/fast-api-tutorial/migration/script.py.mako ...  done
  Generating /workspaces/fast-api-tutorial/migration/README ...  done
  Please edit configuration/connection/logging settings in '/workspaces/fast-api-tutorial/alembic.ini' before
  proceeding.

作成されたファイル

tree
.
├── alembic.ini
└── migration
    ├── README
    ├── env.py
    ├── script.py.mako
    └── versions

.iniファイル変更

alembic.ini
- sqlalchemy.url = driver://user:pass@localhost/dbname
+ sqlalchemy.url = postgresql://user:password@localhost:5432/database

マイグレーションファイルの作成

alembic revision -m "create init table"
  Generating /workspaces/fast-api-tutorial/migration/versions/e2f60403cd22_create_init_table.py ...  done

env.pyにモデル情報を設定

env.py
from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context
+ from sql_app.database import Base, engine

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
- target_metadata = None
+ target_metadata = Base.metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
+     url = config.get_main_option("sqlalchemy.url")

-     connectable = engine_from_config(
-         config.get_section(config.config_ini_section, {}),
-         prefix="sqlalchemy.",
-         poolclass=pool.NullPool,
-    )
    + connectable = engine

    with connectable.connect() as connection:
        context.configure(
+             url=url,
            connection=connection,
            target_metadata=target_metadata
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()
jimiijimii

マイグレーション → できない

vscode ➜ /workspaces/fast-api-tutorial (main) $ alembic revision --autogenerate -m "create table"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
ERROR [alembic.util.messaging] Can't locate revision identified by '0d3b0f1644fc'
  FAILED: Can't locate revision identified by '0d3b0f1644fc'

パスが通ってなくてimportできてなかった → 関係なさそう

調べたところ、以下

このエラーは、migration ファイルとデータベースの状態に不一致がある場合に発生します。データベースには、migration を追跡するテーブルがあり、ファイルが削除された場合はそのテーブルをクリアする必要があります。

確かにalembic_versionっていうテーブルがあったので消してみる

psql -h localhost -p 5432 -U user -d database
psql (16.2)
Type "help" for help.

database=# \d
            List of relations
 Schema |      Name       | Type  | Owner
--------+-----------------+-------+-------
 public | alembic_version | table | user
(1 row)

database=# drop table alembic_version ;
DROP TABLE
database=# \dt
Did not find any relations.
database=# \q

次こそいけるか。

vscode ➜ /workspaces/fast-api-tutorial (main) $ alembic revision --autogenerate -m "create table"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
ERROR [alembic.util.messaging] Target database is not up to date.
  FAILED: Target database is not up to date.

だめ
versionsのディレクトリに古い情報があるから？
消してみる

vscode ➜ /workspaces/fast-api-tutorial (main) $ rm -rf migration/versions/*******忘れた

いけた

vscode ➜ /workspaces/fast-api-tutorial (main) $ alembic revision --autogenerate -m "create table"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
  Generating /workspaces/fast-api-tutorial/migration/versions/5259106f86e1_create_table.py ...  done

一応テーブルできてるか確認
→だめ

jimiijimii

モデル定義が別ファイルでインポートされてなかった

以下のようにBaseとModelを別ファイルで定義していた。

database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

user_name: str = "user"
password: str = "password"
host: str = "localhost"
port: int = 5432
db_name: str = "database"
# dialect+driver://username:password@host:port/database
SQLALCHEMY_DATABASE_URL = f"postgresql://{user_name}:{password}@{host}:{port}/{db_name}"

engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()
models.py
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from .database import Base


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)

    items = relationship("Item", back_populates="owner")


class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True)
    title = Column(String, index=True)
    description = Column(String, index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))

    owner = relationship("User", back_populates="items")

そのため、env.pyで以下のように定義していたが

env.py
from sql_app.database import Base, engine
target_metadata = Base.metadata

Modelの定義がされていないので、alembic revision --autogenerate -m "hoge"をした際に以下のように空定義のが出来上がってしまってた。

9cb3141383f7_hoge.py
def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

よって、env.pyでModelのファイルもインポートしてあげた

from sql_app import models

これでいけた

vscode ➜ /workspaces/fast-api-tutorial (main) $ alembic revision --autogenerate -m "crate users, items table"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'users'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_users_email'' on '('email',)'
INFO  [alembic.autogenerate.compare] Detected added table 'items'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_items_description'' on '('description',)'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_items_title'' on '('title',)'
  Generating /workspaces/fast-api-tutorial/migration/versions/9cb3141383f7_crate_users_items_table.py ...  done

vscode ➜ /workspaces/fast-api-tutorial (main) $ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade e9cbd9abc614 -> 9cb3141383f7, crate users, items table

database=# \d
              List of relations
 Schema |      Name       |   Type   | Owner
--------+-----------------+----------+-------
 public | alembic_version | table    | user
 public | items           | table    | user
 public | items_id_seq    | sequence | user
 public | users           | table    | user
 public | users_id_seq    | sequence | user
(5 rows)
jimiijimii

テーブルお片付け

vscode ➜ /workspaces/fast-api-tutorial (main) $ alembic downgrade base
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 9cb3141383f7 -> e9cbd9abc614, crate users, items table
INFO  [alembic.runtime.migration] Running downgrade e9cbd9abc614 -> , init table

これはのこってるのねー

database=# \dt
            List of relations
 Schema |      Name       | Type  | Owner
--------+-----------------+-------+-------
 public | alembic_version | table | user
(1 row)

database=# select * from alembic_version ;
 version_num
-------------
(0 rows)

database=# drop table alembic_version ;
DROP TABLE
database=# \dt
Did not find any relations.