SQLAlchemy - 高度なデータモデリング: 外部キー制約とユニーク制約を活用した実践例

SQLAlchemy - 外部キーリレーションシップで列を個別にユニークにする方法

要件:

• SQLAlchemy 1.4 以降
• Python 3.x

例:

次の例では、Book というテーブルと Author というテーブルがあるとします。 Book テーブルには、author_id という外部キー列と、title および isbn という 2 つの列があります。 author_id 列は、Author テーブルの id 列を参照します。

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey

engine = create_engine('sqlite:///database.db')
Base = declarative_base()

class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey('authors.id'))
    title = Column(String(255))
    isbn = Column(String(255), unique=True)

class Author(Base):
    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True)
    name = Column(String(255))

Base.metadata.create_all(engine)

上記のコードでは、isbn 列は個別にユニークに設定されています。つまり、同じ ISBN を持つ複数の書籍を保存することはできません。

外部キー制約との組み合わせ:

unique オプションを外部キー制約と組み合わせることもできます。これにより、同じ著者が書いた同じタイトルの書籍を保存できなくなります。

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey

engine = create_engine('sqlite:///database.db')
Base = declarative_base()

class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey('authors.id'))
    title = Column(String(255))
    isbn = Column(String(255), unique=True)

    __tableargs__ = (
        ForeignKeyConstraint(['author_id', 'title'], 'authors.id', 'name'),
    )

class Author(Base):
    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True)
    name = Column(String(255))

Base.metadata.create_all(engine)

上記のコードでは、Book テーブルの author_id および title 列は、Author テーブルの id および name 列を参照する外部キー制約で結合されています。さらに、unique オプションを使用して、author_id と title の組み合わせが個別にユニークになるようにしています。

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey

# データベースへの接続
engine = create_engine('sqlite:///database.db')

# 基底クラスの宣言
Base = declarative_base()


# 書籍を表すクラス
class Book(Base):
    __tablename__ = 'books'  # テーブル名

    id = Column(Integer, primary_key=True)  # 主キー
    author_id = Column(Integer, ForeignKey('authors.id'))  # 外部キー
    title = Column(String(255))  # タイトル
    isbn = Column(String(255), unique=True)  # ISBN（個別にユニーク）

# 著者を表すクラス
class Author(Base):
    __tablename__ = 'authors'  # テーブル名

    id = Column(Integer, primary_key=True)  # 主キー
    name = Column(String(255))  # 名前


# テーブルの作成
Base.metadata.create_all(engine)

1. ライブラリのインポート:

   • create_engine: SQLAlchemy エンジンを作成するために使用されます。
   • declarative_base: SQLAlchemy のデクラレーティブベースクラスをインポートします。
   • Column, Integer, String, ForeignKey: テーブルの列を定義するために使用される SQLAlchemy データ型。

2. データベースへの接続:

3. 基底クラスの宣言:

4. 書籍を表すクラス:

   • __tablename__ = 'books': テーブルの名前を books に設定します。
   • id = Column(Integer, primary_key=True): id という名前の主キー列を作成します。これは、各書籍の一意の識別子となります。
   • author_id = Column(Integer, ForeignKey('authors.id')): author_id という名前の外部キー列を作成します。これは、authors テーブルの id 列を参照します。
   • title = Column(String(255)): title という名前の列を作成し、最大長 255 文字の文字列を格納します。
   • isbn = Column(String(255), unique=True): isbn という名前の列を作成し、最大長 255 文字の文字列を格納します。この列は unique=True オプションで個別にユニークに設定されているため、同じ ISBN を持つ複数の書籍を保存することはできません。

6. テーブルの作成:

• unique オプションを使用して、books テーブルの isbn 列を個別にユニークにする方法。
• 外部キー制約を使用して、books テーブルの author_id 列を authors テーブルの id 列に関連付ける方法。
• SQLAlchemy を使用して、books と authors という 2 つのテーブルを定義する方法。

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker

# データベースへの接続
engine = create_engine('sqlite:///database.db')

# 基底クラスの宣言
Base = declarative_base()

# 書籍を表すクラス
class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey('authors.id'))
    title = Column(String(255))
    isbn = Column(String(255))

# 著者を表すクラス
class Author(Base):
    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True)
    name = Column(String(255))

# セッションの作成
Session = sessionmaker(bind=engine)
session = Session()

def create_book(author_id, title, isbn):
    # 既存の書籍で同じ ISBN を持つものがないか確認
    existing_book = session.query(Book).filter_by(isbn=isbn).first()
    if existing_book:
        raise ValueError(f"ISBN '{isbn}' は既に存在します")

    # 新しい書籍を作成
    book = Book(author_id=author_id, title=title, isbn=isbn)
    session.add(book)
    session.commit()

# 例
try:
    create_book(1, "SQLAlchemy 入門", "978-4-7731-9854-6")
except ValueError as e:
    print(e)

# 出力:
# ISBN '978-4-7731-9854-6' は既に存在します

上記のコードでは、create_book 関数を作成して、新しい書籍を作成します。 この関数は、既存の書籍で同じ ISBN を持つものがないか確認してから、新しい書籍を作成します。

トリガーを使用する:

データベーストリガーを使用して、isbn 列の個別のユニーク性を維持することもできます。 トリガーは、データベース内のデータが変更されたときに自動的に実行されるコードの断片です。

CREATE TRIGGER unique_isbn
BEFORE INSERT OR UPDATE ON books
FOR EACH ROW
BEGIN
    SELECT CASE WHEN NEW.isbn = OLD.isbn THEN 1 ELSE 0 END
    INTO is_update FROM books
    WHERE id <> NEW.id AND isbn = NEW.isbn;

    IF is_update THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ISBN は既に存在します';
    END IF;
END;

上記の SQL コードは、unique_isbn という名前のトリガーを作成します。 このトリガーは、books テーブルに新しい行が挿入されるか、既存の行が更新されるたびに実行されます。

トリガーは、まず、新しい ISBN が既存の ISBN と一致するかどうかを確認します。 一致する場合は、エラーを発生させて操作を中止します。

ビューを使用する:

ビューを使用して、個別にユニークな isbn 列を含む仮想のテーブルを作成することもできます。 ビューは、既存のテーブルからデータを抽出するのに役立つデータベースオブジェクトです。

CREATE VIEW unique_books AS
SELECT id, author_id, title, isbn
FROM books
GROUP BY isbn;

上記の SQL コードは、unique_books という名前のビューを作成します。 このビューには、id, author_id, title, isbn という 4 つの列が含まれます。

isbn 列は、GROUP BY 句を使用して個別にユニークにされます。

アプリケーションロジックを組み合わせる:

上記の方法はそれぞれ単独で使用できますが、より複雑な制約を定義するために組み合わせることもできます。 例えば、unique オプションとトリガーを組み合わせて、データベースレベルとアプリケーションレベルの両方で制約を定義することができます。