I have been having some trouble with my understanding of how foreign keys work in sqlite3.
Im trying to get the userid (james) in one table userstuff to appear as foreign key in my otherstuff table. Yet when I query it returns None.
So far I have tried:
- Enabling foreign key support
- Rewriting a test script (that is being discussed here) to isolate issue
- I have re-written some code after finding issues in how I had initially written it
- After some research I have come across joins but I do not think this is the solution as my current query is an alternative to joins as far as I am aware
import sqlite3 as sq
self.conn = sq.connect("Atest.db")
self.c = self.conn.cursor()
self.show_details() # NOTE DEFAULT DATA ALREADY RAN
self.c.execute("CREATE TABLE IF NOT EXISTS userstuff("
"userid TEXT NOT NULL PRIMARY KEY,"
" password TEXT)")
self.c.execute("CREATE TABLE IF NOT EXISTS otherstuff("
"anotherid TEXT NOT NULL PRIMARY KEY,"
"user_id TEXT REFERENCES userstuff(userid))")
self.c.execute("INSERT INTO userstuff (userid, password) VALUES (?, ?)", ('james', 'password'))
self.c.execute("INSERT INTO otherstuff (anotherid, password, user_id) VALUES (?, ?, ?)",('aname', 'password', 'james'))
self.c.execute("SELECT user_id FROM otherstuff, userstuff WHERE userstuff.userid=james AND userstuff.userid=otherstuff.user_id")
-----NOTE CODE BELOW THIS IS FROM NEW FILE---------
import test2 as ts
x = ts.DATAB()