SQLAlchemyでデータを一部取得できない問題を解決した備忘録

自分のミスに気付くのにものすごく時間がかかったので、未来の自分用のメモです。ついでに誰かのためになれば。

状況

DBにあるはずのレコードが、超単純なクエリでも、どうしても一部抽出できなかったのです。エラーは起きない。再現性はある。（dockerでImageを作り直すところからやり直しても、まったく同じ状況）
SQLAlchemy 2.0.19で、ORMを使っています。DBはMySQL8.1です。

• SQLAlchemy Documentation — SQLAlchemy 2.0 Documentation
• MySQL :: MySQL 8.0 リファレンスマニュアル

詳細

とあるテーブルをこんな風に定義してました。

Create Table文とデータ

CREATE TABLE `my_table1` (
    `my_id`         INT         NOT NULL,
    `hoge_date`     DATE        NOT NULL,
    `fuga_date`     DATE,
    PRIMARY KEY (my_id, hoge_date)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO `my_table1` VALUES
(1, "2023-01-01", "2023-01-31"),
(1, "2023-02-01", "2023-02-28"),
(1, "2023-03-01", NULL),
(2, "2023-01-01", "2023-01-31"),
・・・・（もっとある）・・・
;

ORMの定義

class MyTable1(Base):
    __tablename__ = "my_table1"
    
    my_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    hoge_date: Mapped[date] = mapped_column(Date, nullable=False)
    fuga_date: Mapped[date] = mapped_column(Date)

hoge_dateに、primary_key=Trueを入れ忘れているバグ。これが原因なんですが。。

この状態で、SQLAlchemyでSELECTすると・・・

Selectしてるところ

# 無条件で抽出
recs = session.query(
    MyTable1
).all()
# 件数確認
print(len(recs))

上のコードはただのSELECT * FROM my_table1;で、DBクライアントソフトでDBへ接続して全件調べると、DBクライアントでは146件、Pythonでは144件でした。

my_idごとにカウントすると、3件あるmy_idが全部で17個あったのですが、そのうち2個のmy_idだけは2件しか抽出できず、それ以外の15個は3件ずつ抽出できました。つまりPython・SQLAlchemyからはこんな感じに見えている。最初の2個だけ、1件ずつ足りなかった。

SELECT my_id, COUNT(*) FROM my_table1
GROUP BY my_id HAVING COUNT(*)>1;

my_id count
    1     2
    2     2
    3     3
    4     3
  ... (省略)
   17     3

当然、DBクライアントからのクエリ結果は、全部3件。

原因

上にも書きましたが、原因はprimary_key=Trueの書き忘れです。正誤の違いはhoge_dateのmapped_columnの引数です。

ORMの定義（誤り）

class MyTable1(DbBase):
    __tablename__ = "my_table1"
    
    my_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    hoge_date: Mapped[date] = mapped_column(Date, nullable=False)
    fuga_date: Mapped[date] = mapped_column(Date)

ORMの定義（正しい）

class MyTable1(DbBase):
    __tablename__ = "my_table1"
    
    my_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    hoge_date: Mapped[date] = mapped_column(Date, primary_key=True)
    fuga_date: Mapped[date] = mapped_column(Date)

※ primary_key=Trueを指定するとnullable=False(NOT NULL)がデフォルト、primary_keyでなければnullable=Trueがデフォルト。(リファレンスより)

おわりに（感想）

にらみっこしたり投げ出したりして、半日くらい詰まってましたよ。。ググっても、中途半端に抽出できちゃうなんて人いないし、そんな致命的でわかりやすいバグが残ってる気がしない。最終的には、PK関係か！？っていうところから自分でたどり着きました。PKの問題だったら、2件とか3件とか取れちゃわないで、全部1件しか取れなかったら、もう少し早く気付けたかもしれない。コロンブスの卵！

ちなみに今回の私のミスとは直接関係ないけど、SQLAlchemyは、

• 日本語の情報がちょっと少ない
• バージョンが1.4→2.0で大きく変わったから、1.x時代の記事によくはまる
• ORM的な使い方と、SQLを投げるように使う使い方がある(らしい)

なのでちょっと注意です。