SQLite3 + Python3 - creating a database with foreign keys

database, foreign-keys, python-3.x, sqlite

Solution

- As the message tells you, you can only execute one statement at a time.

- You need to execute `PRAGMA foreign_keys` only once for the connection.

- Executing `BEGIN TRANSACTIOn` makes no sense here.

- A PRIMARY KEY constraint already implies NOT NULL.

- A PRIMARY KEY constraint already implies UNIQUE.

- If a table name contains a space, you must quote it.

- When you declare a foreign key as a table constraint, you must write it after all the columns, and must not forget the delimiting comma.

- When you declare a foreign key as a column constraint, you must use the correct syntax:

c.execute('''
          CREATE TABLE Journey (
              ID INTEGER PRIMARY KEY AUTOINCREMENT,
              Driver_ID INTEGER NOT NULL REFERENCES Driver(ID),
              Origin text,
              Destination text,
              SeatsAvailable integer,
              Date text,
              Time text,
              DriverNotes text,
              PassengerNotes text)
          ''')
c.execute('''
          CREATE TABLE Assigned_Passengers (
              ID INTEGER PRIMARY KEY AUTOINCREMENT,
              Journey_ID INTEGER NOT NULL REFERENCES Journey(ID),
              Passenger_ID INTEGER NOT NULL REFERENCES Passenger(ID))
          ''')

Problem

Hi there everyone, I'm having a little trouble with sqlite3 and python3. I think I'm having one of those dumb moments again, but I'm trying to essentially trying to make the table below. The sticking point seems to be the foreign keys - I'm doing something wrong, but I haven't quite worked out what - and my google searches aren't really getting me anywhere. I can't find any good documentation. I've tried multiple different syntax's for foreign key's online, but none are getting me any nearer to a working table. ``` c.execute(''' CREATE TABLE Driver ( ID INTEGER PRIMARY KEY NOT NULL UNIQUE AUTOINCREMENT, FirstName text, LastName text, RegistrationNo text) ''') c.execute(''' CREATE TABLE Passenger ( ID INTEGER PRIMARY KEY NOT NULL UNIQUE AUTOINCREMENT, FirstName text, LastName text) ''') c.execute(''' pragma foreign_keys=on; begin transaction; CREATE TABLE Journey ( ID INTEGER PRIMARY KEY NOT NULL UNIQUE AUTOINCREMENT, Driver_ID INTEGER NOT NULL FOREIGN KEY (Driver_ID) REFERENCES Driver, Origin text, Destination text, SeatsAvailable integer, Date text, Time text, DriverNotes text, PassengerNotes text) ''') c.execute(''' pragma foreign_keys=on; begin transaction; CREATE TABLE Assigned Passengers ( ID INTEGER PRIMARY KEY NOT NULL UNIQUE AUTOINCREMENT, Journey_ID INTEGER NOT NULL FOREIGN KEY (Journey_ID) REFERENCES Journey, Passenger_ID INTEGER NOT NULL FOREIGN KEY (Passenger_ID) REFERENCES Passenger ''') ``` One of the messages I'm getting is - ``` Traceback (most recent call last): File "/Users/Computer/Bitbucket/Python Project/sql.py", line 46, in <module> ''') sqlite3.Warning: You can only execute one statement at a time. ``` Line 46 is at the end of the Journey creation block. Thanks in advance to anyone that can help :)

Original source