Data can be inserted into an SQLite table from Python using the sqlite3 module and the SQL INSERT INTO statement. The execute() method inserts a single record, while executemany() is useful for inserting multiple records.

1. Inserting Data Using Only Values

In this approach, values are supplied in the same order as the columns defined in the table. The number of values must match the number of columns.

Syntax

INSERT INTO table_name
VALUE (?, ?, ?)

Example:

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS STUDENT (
        NAME TEXT,
        CLASS TEXT,
        SECTION TEXT
    )
""")

cursor.execute(
    "INSERT INTO STUDENT VALUES (?, ?, ?)",
    ("Raju", "7th", "A")
)

conn.commit()

cursor.execute(
    "SELECT * FROM STUDENT ORDER BY rowid DESC LIMIT 1"
)
print(cursor.fetchone())

conn.close()

Output

('Raju', '7th', 'A')

Explanation:

• sqlite3.connect() opens the database and creates the file if it does not exist.
• CREATE TABLE IF NOT EXISTS creates the STUDENT table only when it is absent.
• execute() inserts the supplied values using question-mark placeholders.
• commit() saves the transaction, and fetchone() retrieves the latest inserted record.
• close() releases the database connection.

2. Inserting Using Column name and Values

This approach specifies the target columns explicitly. It allows values to be inserted in a different order from the table definition, provided each value matches its corresponding column.

Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (?, ?, ?);

Example:

Following example uses the STUDENTS table created in the previous section.

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

cursor.execute(
    """
    INSERT INTO STUDENT (CLASS, SECTION, NAME)
    VALUES (?, ?, ?)
    """,
    ("8th", "B", "Shyam")
)

conn.commit()

cursor.execute(
    "SELECT * FROM STUDENT ORDER BY rowid DESC LIMIT 1"
)
print(cursor.fetchone())

conn.close()

Output

('Shyam', '8th', 'B')

Explanation:

• INSERT statement specifies CLASS, SECTION and NAME in the desired order.
• Parameter tuple follows that same order: 8th, B and Shyam.
• SELECT statement returns columns in their table-defined order: NAME, CLASS and SECTION.
• commit() saves the inserted record.

3. Inserting Multiple Records Using executemany()

This method executes a parameterized SQL statement for each set of values supplied in an iterable. It is useful when inserting multiple records with the same structure.

Example:

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

students = [
    ("Meena", "8th", "B"),
    ("Aman", "9th", "C"),
    ("Priya", "7th", "A")
]

cursor.executemany(
    """
    INSERT INTO STUDENT (NAME, CLASS, SECTION)
    VALUES (?, ?, ?)
    """,
    students
)

conn.commit()

print("Inserted records:", cursor.rowcount)

conn.close()

Output

Inserted records: 3

Explanation:

• students contains three tuples, each representing one record.
• executemany() executes the INSERT statement for each tuple.
• Each tuple must provide values in the same order as the specified columns.
• rowcount reports the number of affected rows for this insertion.
• commit() saves the changes to the database.