CODEWITHSUNDEEP
pythonsqlite
Sophie Law
Sophie Law

Reputation: 37

Linking tables in SQLite 3 in Python

I have to create a program which stores people's account details in a table in SQLite 3. One of the columns is interests, which is the user's favourite genre of film to watch. I then need to give them film recommendations based on that genre, so I need to have another table which stores films of that genre. The only problem is that I don't know how to link tables, so when comedy is their favourite genre, how to output films in the genre comedy.

Here is my code for adding a new user:

    #function for creating a new customer's account
def create_id(username, password, name, address, DoB, gender, interestsUp, recent1, recent2, recent3, recent4, recent5, recent6, recent7, recent8, recent9, recent10):
    #When adding something to a SQLite file, you have to put "" around each item
    c.execute('INSERT INTO userId(username,password,name,address,DoB,gender,interests, recent1, recent2, recent3, recent4, recent5, recent6, recent7, recent8, recent9, recent10) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', (username, password, name, address, DoB, gender, interestsUp, recent1, recent2, recent3, recent4, recent5, recent6, recent7, recent8, recent9, recent10))#When adding something thats in a variable you put question marks and the name of the variables in brackets
    conn.commit()

Upvotes: 0

Views: 4490

Answers (1)

Alan
Alan

Reputation: 3042

You should not try to store all data in one table. You should use a table for each topic (e.g. user details, interests) and then express a relationship between the tables. You can do this at design time with a foreign key, which will help with keeping the data clean, but you will also need to define the relationship when extracting the data.

Here's an example:

Table 1: Users
user_id, username

Table 2: User_Details
user_id, password, name, address, DoB, gender

Table 3: Favourites
fav_id, user_id, genre

Table 4: Films
film_id, film_name, genre

To get a list of a user's preferred genre would then be:

SELECT Films.film_name
FROM Users
INNER JOIN Favourites ON Users.user_id = Favourites.user_id
INNER JOIN Films ON Favourites.genre = Films.genre
WHERE user_id = ?

Nearly all the filtering here is done by the INNER JOIN. This will connect tables but only return results which are present in both tables. This means if a user has not yet submitted any preferences, no results will be returned for that user. The ON instruction tells the database what the relation is.

The output will be a list of films associated with any genres the user has favourited.

Upvotes: 4

Related Questions