[Python] Executing SQL with SQLite3: Creating a Member Table and Inserting Data

2025年12月25日

This article demonstrates how to use the sqlite3 module to perform “Table Creation (CREATE)” and “Data Registration (INSERT)” operations.

We will simulate a database that manages member information for a web service, implementing security best practices by using placeholders.

Source Code

import sqlite3

# Database file name
db_file = "service_members.db"

# 1. Connect to the database
# Using the 'with' statement prevents forgetting to close the connection
with sqlite3.connect(db_file) as conn:
    
    cur = conn.cursor()
    
    # --- 1. Create Table (CREATE) ---
    # Member ID (Integer), Name (Text), Email (Text), Rank (Text)
    create_sql = """
    CREATE TABLE IF NOT EXISTS members (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        rank TEXT
    )
    """
    cur.execute(create_sql)
    
    # --- 2. Insert Data (INSERT) ---
    # Member data to register (List of tuples)
    new_members = [
        (1001, "Taro Yamada", "yamada@example.com", "Gold"),
        (1002, "Hanako Sato", "sato@test.co.jp", "Silver"),
        (1003, "Kenichi Takahashi", "takahashi@web.net", "Bronze")
    ]
    
    # SQL statement using placeholders (?)
    # Always use this instead of string concatenation to safely embed values
    insert_sql = "INSERT INTO members (id, name, email, rank) VALUES (?, ?, ?, ?)"
    
    # Register data in bulk using executemany
    try:
        cur.executemany(insert_sql, new_members)
        
        # --- 3. Finalize Changes (COMMIT) ---
        # A commit is strictly required when modifying data
        conn.commit()
        print(f"Registration Complete: Added {len(new_members)} members.")
        
    except sqlite3.IntegrityError as e:
        # Error handling for duplicate IDs or email addresses
        print(f"Registration Error: {e}")

# After execution, 'service_members.db' is generated in the current directory

Execution Result

Registration Complete: Added 3 members.

Explanation

1. CREATE TABLE IF NOT EXISTS

This command means “Create the table only if it does not exist.” Even if you run the program multiple times, it will ignore the command if the table already exists, allowing you to re-run the script safely without errors.

2. executemany(sql, data_list)

This method processes data much faster than looping through a list and calling execute for each item. It is used when you want to pour a large amount of data (organized in lists or tuples) into the database at once.

3. Transactions and Commits

Data is written to the file only when conn.commit() is executed. If an error occurs during executemany, and the commit has not yet happened, not a single record will be saved. This ensures data integrity (acting like a rollback).

よかったらシェアしてね！