Home Foreign keys Sqlite3 Python3
Reply: 0

Foreign keys Sqlite3 Python3

user2026
1#
user2026 Published in May 21, 2018, 1:03 pm

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:

  1. Enabling foreign key support
  2. Rewriting a test script (that is being discussed here) to isolate issue
  3. I have re-written some code after finding issues in how I had initially written it
  4. 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

Code

import sqlite3 as sq

class DATAB:

def __init__(self):

    self.conn = sq.connect("Atest.db")
    self.conn.execute("pragma foreign_keys")
    self.c = self.conn.cursor()
    self.createtable()
    self.defaultdata()
    self.show_details()  # NOTE DEFAULT DATA ALREADY RAN

def createtable(self):
    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,"
                   "password TEXT,"
                   "user_id TEXT REFERENCES userstuff(userid))")

def defaultdata(self):
    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.conn.commit()

def show_details(self):
    self.c.execute("SELECT user_id FROM otherstuff, userstuff WHERE  userstuff.userid=james AND userstuff.userid=otherstuff.user_id")
    print(self.c.fetchall())
    self.conn.commit()

-----NOTE CODE BELOW THIS IS FROM NEW FILE---------

import test2 as ts



x = ts.DATAB()

Many thanks

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.662572 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO