Python + SQlite3, using foreign keys

Question

Hi I'm writing this little frontend for a customer database for my workplace. We just needed something light and simple to keep track of customers and tasks and stuff.

This is getting kind of confusing because I'm learning python and SQL at the same time, but I'd like to get the whole foreign key relations part working smoothly before I go on.

What I want to do is be able to have many tasks assigned to 1 customer

here is an example:

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('''CREATE TABLE customers (custid INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone TEXT, email TEXT, notes TEXT)''')
cur.execute('''CREATE TABLE tasks (taskid INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, notes TEXT, taskcust INTEGER, FOREIGN KEY(taskcust) REFERENCES customer(custid))''')

cur.execute('''INSERT INTO customers (name, phone, email, notes) VALUES('Jeff', '555555', '[email protected]', 'balblalal')''')
cur.execute('''INSERT INTO tasks (title, notes, taskcust) VALUES ('Toshiba A200', 'replace RAM, add 2 gigs', 1)''')
cur.execute('''INSERT INTO tasks (title, notes, taskcust) VALUES ('WD External HDD', 'Diagnose, tick of death, hdd probably dead', 1)''')

So now I have 2 tasks assigned to 'Jeff'. What if I want to print Jeff's contact info and all his tasks?

cur.execute('''SELECT * FROM customers where custid=1''')
for row in cur:
    for i in row:
        print i
cur.execute('''SELECT * FROM tasks WHERE taskcust=1''')
for row in cur:
    for i in row:
        print i

am I doing it right?

mklauber · Accepted Answer · 2011-01-04 21:11:12Z

2

Don't indent

    for row in cur:
        for i in row:
            print i

below cur.execute('''SELECT * FROM tasks WHERE taskcust=1''')

Besides that, I think it all works the way you want. Foreign keys are useful because now if you want to find the customer who requested you "replace RAM, add 2 gigs" you can trace back to the customer record.

answered Jan 4, 2011 at 21:11

mklauber

1,14411 silver badges21 bronze badges

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

5 Comments

Steinthor.palsson Over a year ago

thanks the indent was just a typo :) Just wanted to make sure I was doing it properly.

mklauber Over a year ago

That's what I figured. One quick gotcha before you get bit by it, depending on your database system, foreign keys are enforced, so if a customer with the given ID does not exist when you try to insert a task, the DB may throw an error.

Steinthor.palsson Over a year ago

I kind of think the foreign keys aren't being enforced properly. I can make a task for a customer that doesn't exist and if I delete a customer the tasks assigned to him remain :S Seems the foreign is just acting like a normal integer column...

mklauber Over a year ago

It aapears according to sqlite.org/foreignkeys.html#fk_enable that SQLite's foreign key constraints are disabled by default, for backwards compatibility.

Steinthor.palsson Over a year ago

i see. Thanks ___ conn = sqlite3.connect(db) conn.execute('pragma foreign_keys = on') ___ seems to be working

Collectives™ on Stack Overflow

Python + SQlite3, using foreign keys

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related