CODEWITHSUNDEEP
python-3.xsqlite
Dovahkiin
Dovahkiin

Reputation: 65

How to get the id of another table in sqlite3 Python

I have a text file which contains tasks, firstnames, lastnames and scores. I am then creating two tables like this in python:

c.execute('create table if not exists persons (id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, UNIQUE(name1, name2))')


c.execute('create table if not exists scores (idPerson INTEGER, task INTEGER, score INTEGER, FOREIGN KEY (idPerson) REFERENCES persons(id) ON DELETE CASCADE, UNIQUE(idPerson, task))')

I am trying to get the id from persons and inserting that into scores, but don't know if this is the right way to do it:

for lines in file:
    fields = lines.split(" ")
    name1 = fields[2]
    name2 = fields[3]
    point = fields[4]
    task = fields[1]
    persons = (name1, name2)
    c.execute('INSERT OR IGNORE INTO persons (name1, name2) VALUES (?, ?)', persons)
    id = c.lastrowid
    scores = (id, task, point)
    c.execute('INSERT OR IGNORE INTO scores (idPerson, task, score) VALUES (?, ?, ?)', scores)

But with this I am getting an foreign key error and I don't know what to do here?

Upvotes: 0

Views: 764

Answers (1)

Shawn
Shawn

Reputation: 52529

Get the appropriate id to use with the scores table with something like:

persons = (name1, name2)
c.execute('INSERT OR IGNORE INTO persons (name1, name2) VALUES (?, ?)', persons)
scores = (name1, name2, task, point)
c.execute('INSERT OR IGNORE INTO scores (idPerson, task, score) VALUES ((SELECT id FROM persons WHERE name1=? AND name2=?), ?, ?)', scores)

Upvotes: 1

Related Questions