Insert Foreign Key with two tables containing the same columns in SQLite with Python

Question

I have a SQLite database on my computer with two tables :

• table_post (contain information about a twitter post : likes, author, URL, etc...)
• table_profile (contain information about a Twitter profile : username, description, followers, etc...)

I am using a python script which create two separate CSV files containing the rows of table_post and table_profile.

Then I use another script to transfer the CSV files to the SQLite database. Everything works fine until I want to link the two tables with a foreign key.

My table_post has these columns : post_ID(PK), profile_ID(FK), postUrl, postText, pubDate, commentCount, likeCount, profileUrl

My table profile has these columns : profile_ID(PK), profileUrl, subCount, userName, profileDesc

Both tables have profileUrl, I would like to insert into table_post.profile_ID the corresponding table_profile.profile_ID using the profileUrl column.

I know that we can use this SQLite query to join the rows:

SELECT * FROM table_profile
JOIN table_post ON table_profile.profileUrl = table_post.profileUrl; 

I would like to insert the corresponding profile_ID in post_ID using Python and sqlite3. What could I do ? Do I need to write the ID while writing in the SQLite database ? If yes, how ?

Can I write a function that check every row in the post_table and associate it with a profile_ID ? If yes, how ?

Thanks.

Answer 1

What you need is an UPDATE statement and not INSERT.

You must update the table table_post after you transfer the CSV files to the SQLite database:

UPDATE table_post
SET profile_ID = (SELECT profile.profile_ID FROM profile WHERE profile.profileUrl = table_post.profileUrl)

If your version of SQLite is 3.33.0+ you can use the UPDATE...FROM syntax:

UPDATE table_post AS t
SET profile_ID = p.profile_ID 
FROM profile AS p
WHERE p.profileUrl = t.profileUrl