Python, sqlite3: data format for inserting multiple values?

Question

I have some difficulties to come up with proper title for my problem.

I’m trying to learn a little bit about SQLite and database and I came up with little exercise (I’m aware that for this specific case making multiple tables doesn’t make much sense, it’s all just for exercise and finding out ‘how-to’). The database is supposed to contain a few tables: main “movies table”, “year table”, “director table” and “category table”. Picture for easier understanding.

After creating tables, I wanted to populate them with tuple of tuples. I have no problem with “little” tables for example, populating directors table.

    Dir = ((0, ‘Blip’), (1, ‘Blop’), (2, ‘Blap’))

then:

with con:
    cur = con.cursor()    

    cur.execute("DROP TABLE IF EXISTS Directors")
    cur.execute("CREATE TABLE Directors(Id INT PRIMARY KEY, Director TEXT)")
    cur.executemany("INSERT INTO Directors VALUES(?, ?, ?)", Dir)

The problem appears with main movie table. How to pass multitude category values for each movie? Should I pass this collection of values as another tuple?

Movies = ((‘Dog’, 0, 0, 1), (‘Cat’, 0, 0, (1, 2)), 
         (‘Ant’, 1, 1, (0, 2)), (‘Crow’, 2, 2, (0, 1, 2)))

Daniel Roseman · Accepted Answer · 2016-05-04 14:34:45Z

1

You need another table. This is a many-to-many relationship, and as such needs a through table with foreign keys to both Movies and Categories. Then you can insert one row for each pair of (movie, category): so:

Dog, 1
Cat, 1
Cat, 2
Ant, 0
Ant, 2
etc.

(Also, it's good practice to have a primary key for each table: for movies, you might want to use an autoincrement integer field rather than the title.)

answered May 4, 2016 at 14:34

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python, sqlite3: data format for inserting multiple values?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related