Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using foreign keys in sqlite3 for Python

Tags:

python

sqlite

I'm writing a program that creates a sqlite3 database through python. I have one table of Authors (AuthorID, Name) and a second table of books (BookID, Title, AuthorID) I've created these as shown below:

Authors = sqlite3.connect('Authors.db')
Authors.execute('''CREATE TABLE Authors
       (AuthorID      INT  PRIMARY KEY, 
       Name           TEXT);''')
Authors.close()


Books = sqlite3.connect('Books.db')
Books.execute('''CREATE TABLE Books
       (BookID            INT  PRIMARY KEY, 
       Title              TEXT,
       AuthorID           INT,
       FOREIGN KEY(AuthorID) REFERENCES Authors(AuthorID));''')
Books.close()

I then go to add a record to each of the tables as shown below:

Authors = sqlite3.connect('Authors.db') 
Authors.execute("INSERT INTO Authors (AuthorID, Name) \
        VALUES (1, 'Jane Austin')");

Authors.commit()
Authors.close()


Books = sqlite3.connect('Books.db')
Books.execute("INSERT INTO Books (BookID, Title, AuthorID) \
        VALUES (1, 'Emma', 1)");

Books.commit()
Books.close()

The database is correctly updated but I don't think the foreign keys are working correctly because it allows me to remove the Author 'Jane Austin', when there are books associated with it.

I've seen some tutorials use this line:

Books.execute("PRAGMA foreign_keys = 1")

Is this the answer to the problem and if so where do I put this line?

like image 475
Paul McDonald Avatar asked Apr 02 '15 19:04

Paul McDonald


People also ask

Does sqlite3 support foreign keys?

SQLite foreign key constraint supportSQLite has supported foreign key constraint since version 3.6.

How do I reference a foreign key in SQLite?

How to Add a Foreign Key to an Existing Table. You can not use the ALTER TABLE statement to add a foreign key in SQLite. Instead you will need to rename the table, create a new table with the foreign key, and then copy the data into the new table.

How do you create a foreign key in Python?

Create a foreign key in a databaseRight-click a child table and select New | Foreign Key. In the Target table pane, specify the name of the target table. In the From field, specify the name of the column in the child table. In the To field, specify the name of the column in the target table.


3 Answers

The PRAGMA foreign_keys setting applies to a connection, so you should execute it immediately after calling sqlite3.connect().

Please note that foreign key constraints work only inside the same database; you should put both tables into the same file.

like image 91
CL. Avatar answered Sep 18 '22 15:09

CL.


So to do what you want to do you need to create one database file with 2 tables.

Example:

conn=sqlite3.connect("clientdatabase.db")
conn.execute("PRAGMA foreign_keys = 1")
cur=conn.cursor()

# Create 2 tables if they don't exist: Clients and Work_Done
cur.execute('''CREATE TABLE IF NOT EXISTS Clients
(CID INTEGER PRIMARY KEY,
First_Name  TEXT    NOT NULL,
Last_Name       TEXT,
Business_Name   TEXT,
Phone           TEXT,
Address         TEXT,
City            TEXT,
Notes           TEXT,
Active_Status   TEXT    NOT NULL)''')      

cur.execute('''CREATE TABLE IF NOT EXISTS Work_Done
(ID INTEGER PRIMARY KEY,
Date            TEXT    NOT NULL,
Onsite_Contact  TEXT,
Work_Done       TEXT    NOT NULL,
Parts_Installed TEXT,
Next_Steps      TEXT,
CID             INT,
FOREIGN KEY (CID) REFERENCES CLIENTS (CID))''')
conn.commit()

Note that both tables are in the same database and you add the line after connection and before the cursor object.

Hope this helps.

like image 29
John Harris Avatar answered Sep 20 '22 15:09

John Harris


Also note that if there is an active transaction, the PRAGMA foreign_keys does not work. There is no error message if you try to do so but foreign keys will still be turned off.

If you have problems with foreign keys even after using the pragma, it may be worth an attempt to execute COMMIT once before using it.

like image 22
Lukas Loos Avatar answered Sep 20 '22 15:09

Lukas Loos