SQLite3 how to create an insert statement for many-to-many relationship?

Question

Let's say you have three basic tables that implement a many-to-many relationship between tasks and categories:

CREATE TABLE task(
    id INTEGER AUTOINCREMENT PRIMARY KEY NOT NULL,
    name INTEGER NOT NULL,
    description INTEGER NOT NULL
);
CREATE TABLE category(
    id INTEGER AUTOINCREMENT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL
);
CREATE TABLE task_category(
    task_id INTEGER,
    category_id INTEGER,
);

If I want to insert into my database a task named "wrote script" with some description "I wrote a python script." that is associated with categories "python" and "scripting", how do I do so? Assume that the python and scripting categories already exist in the database.

I took a look at this question: SQLite many-to-many relationship?

It seems like some implementation of foreign keys might be useful but I still don't know what the INSERT statement would look like.

My best attempt would be to insert the task into the task table, then do a select to get the task I just inserted back with its id, then link up that id to the categories in the task_category table manually. This doesn't seem like a very effective way of doing this.

What is the standard way of inserting a record into a sqlite database and linking it up to a many-to-many relationship?

I am using Python to create a script that will prompt the user to create new tasks and associate them with existing categories. — Samuel Cole, May 09 '18 at 16:26

score 2 · Accepted Answer · answered May 09 '18 at 16:34

Foreign key constraints are just constraints, i.e., they prevent you from inserting invalid data into the database. They do not save you the work of actually inserting the data, so they are not relevant for this question. (They still are useful.)

In Python, the lastrowid property returns the just-inserted ID:

cursor.execute("INSERT INTO task(name, description) VALUES(?, ?)",
               ["wrote script", "I wrote a python script."])
task_id = cursor.lastrowid
cursor.execute("""INSERT INTO task_category(task_id, category_id)
                  SELECT ?, id
                  FROM category
                  WHERE name IN (?, ?)""",
               [task_id, "python", "scripting"])

SQLite3 how to create an insert statement for many-to-many relationship?

1 Answers1

Linked