How to insert into two tables that are both related together with foreign_keys?

Here are my tables :

CREATE TABLE thread
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    post_id INTEGER,
    FOREIGN KEY(post_id) REFERENCES post(id)
);

CREATE TABLE post
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message TEXT,
    thread_id INTEGER,
    user_id INTEGER,
    FOREIGN KEY(thread_id) REFERENCES thread(id),
    FOREIGN KEY(user_id) REFERENCES user(id)
);

I have a thread table that contain the id of the first post (the author's post).

If I want to insert a new post in an existing thread, it's easy because I know the thread_id.

But if I want to create a new thread, I need to know the post_id of the post that does not exist yet.

At the moment I do it with multiples SQL queries and multiples commits :

cur = db.execute("""
      INSERT INTO post (content, user_id)
      VALUES(?, ?, ?)""", [content, user_id])
db.commit()
post_id = cur.lastrowid
cur = db.execute("""
      INSERT INTO thread (title, post_id)
      VALUES(?, ?, ?)""", [title, post_id])
db.commit()
thread_id = cur.lastrowid
db.execute("""
      UPDATE post
      SET thread_id = ?
      WHERE id=?""", [thread_id, post_id])
db.commit()

But it's very ugly and i think there is a better solution. If I could do something like this, it would be perfect, but it's not allowed :

INSERT INTO thread(title)
    VALUES("thread 1");
INSERT INTO post(post, thread_id)
    VALUES("first post of thread 1", LAST_INSERT_ROWID() AS tmp);
UPDATE thread
    SET post_id = LAST_INSERT_ROWID()
    WHERE id = tmp;

Any ideas ?

Thanks !

Solution

You do not need to (and should not) call commit() between your statements.

Reading lastrowid is the correct way to get the autoincrement ID of the inserted row.

Storing the first post's ID is not necessary because it can be derived from other information:

SELECT id FROM post WHERE thread_id = ? ORDER BY /* timestamp */ id LIMIT 1;

Storing it would make sense only as an optimization, but nothing in your question indicates that this would be necessary.