sqlite3.OperationalError: no such table

Question

I'm trying to create a sqlite3 table using python. My code is given below:

def initDb():
 database = 'index.db'
 conn = sqlite3.connect(database)
 cur = conn.cursor()
 # Initialize database
 cur.execute('PRAGMA foreign_keys = ON')
 cur.execute('DROP TABLE IF EXISTS modules')
 cur.execute('DROP TABLE IF EXISTS files')
 cur.execute('DROP TABLE IF EXISTS modulesfiles')
 cur.execute(
 '''CREATE TABLE modules (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 label TEXT UNIQUE NOT NULL
 )'''
 )
 cur.execute(
 '''CREATE TABLE files (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 filename TEXT UNIQUE NOT NULL
 )'''
 )
 cur.execute(
 '''CREATE TABLE modulesfiles (
 module INTEGER UNIQUE NOT NULL,
 file INTEGER UNIQUE NOT NULL,
 PRIMARY KEY (module,file),
 FOREIGN KEY (module) REFERENCES modules(id) ON UPDATE CASCADE ON DELETE CASCADE,
 FOREIGN KEY (file) REFERENCES files(id) ON UPDATE CASCADE ON DELETE CASCADE
 )'''
 )
 cur.close()
 return conn
if __name__ == '__main__':
 conn = initDb()
 conn.commit()
 conn.close()

This code runs fine the first time I run it and my database is created. However, if I run it a second time, I get the following error:

 cur.execute('DROP TABLE IF EXISTS files')
sqlite3.OperationalError: no such table: main.modules

I have no idea what's going wrong. Can anybody help?

Answer 1

Dropping modules first makes the foreign key constraint in modulesfiles invalid.

Drop the child table first.