医師国家試験をデータベース化する(4)

第117回医師国家試験問題の A をデータベース化しています。

問題文・選択肢・解答と別々のテーブルに記録してきましたが、アプリを作成するのであればテーブルを1つにした方が多分使いやすいと思います。

3つのテーブルを1つにするSQL

phpmyadmin 上で以下のような SQL を実行するとテーブルを一つにすることができます。

-- 117A テーブルが存在する場合は削除
DROP TABLE IF EXISTS 117A;

-- 117A テーブルを作成
CREATE TABLE 117A (
  id INT AUTO_INCREMENT PRIMARY KEY,
  qnum VARCHAR(255),
  question VARCHAR(1000),
  a VARCHAR(255),
  b VARCHAR(255),
  c VARCHAR(255),
  d VARCHAR(255),
  e VARCHAR(255),
  f VARCHAR(255),
  g VARCHAR(255),
  h VARCHAR(255),
  answer VARCHAR(255) -- answer カラムを追加
);

-- 117A_c、117A_q、117A_a テーブルからデータを挿入
INSERT INTO 117A (qnum, question, a, b, c, d, e, f, g, h, answer)
SELECT
  117A_c.qnum,
  117A_q.question,
  117A_c.a,
  117A_c.b,
  117A_c.c,
  117A_c.d,
  117A_c.e,
  117A_c.f,
  117A_c.g,
  117A_c.h,
  117A_a.answer
FROM 117A_c
INNER JOIN 117A_q ON 117A_c.qnum = 117A_q.qnum
LEFT JOIN 117A_a ON 117A_c.qnum = 117A_a.qnum; -- LEFT JOIN を使用して、answerが存在しない場合も挿入する

python で SQL 実行

pythonで実行します。

import pymysql

# データベース接続の設定
db_config = {
    'host': 'localhost',
    'user': 'user',
    'password': 'pass',
    'database': 'ntexdoc',
    'charset': 'utf8mb4',  # 文字コードの指定 (必要に応じて変更)
    'cursorclass': pymysql.cursors.DictCursor  # 結果を辞書形式で取得するように指定
}

# データベースに接続
conn = pymysql.connect(**db_config)
cursor = conn.cursor()

# 1つ目のクエリを実行
cursor.execute("DROP TABLE IF EXISTS 117A;")

# 2つ目のクエリを実行
cursor.execute("""
    CREATE TABLE 117A (
      id INT AUTO_INCREMENT PRIMARY KEY,
      qnum VARCHAR(255),
      question VARCHAR(1000),
      a VARCHAR(255),
      b VARCHAR(255),
      c VARCHAR(255),
      d VARCHAR(255),
      e VARCHAR(255),
      f VARCHAR(255),
      g VARCHAR(255),
      h VARCHAR(255),
      answer VARCHAR(255)
    );
""")

# 3つ目のクエリを実行
cursor.execute("""
    INSERT INTO 117A (qnum, question, a, b, c, d, e, f, g, h, answer)
    SELECT
      117A_c.qnum,
      117A_q.question,
      117A_c.a,
      117A_c.b,
      117A_c.c,
      117A_c.d,
      117A_c.e,
      117A_c.f,
      117A_c.g,
      117A_c.h,
      117A_a.answer
    FROM 117A_c
    INNER JOIN 117A_q ON 117A_c.qnum = 117A_q.qnum
    LEFT JOIN 117A_a ON 117A_c.qnum = 117A_a.qnum;
""")

# 変更を確定
conn.commit()

# 接続を閉じる
cursor.close()
conn.close()