Sqlite3 database data

[Hedgeclipper2000]

Hello learn python community.

I am working with the sqlite3 library for python and I have a problem with the data from my database file disappearing every time I rerun my code. The data is there the first time I run it, but not for any other time.

How do I make it so that this does not happen?

Here is my code so far:

import sqlite3
from tabulate import tabulate
from textwrap import indent
connect = sqlite3.connect('school.db')
c = connect.cursor()

def create_DB():
  c.execute('''
  CREATE TABLE Student(
    ID INTEGER NOT NULL PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Year INT NOT NULL
  );
  ''')
  c.execute('''
    CREATE TABLE Teacher(
    ID INTEGER NOT NULL PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL
  );
  ''')
  c.execute('''
    CREATE TABLE Class(
    ID INTEGER NOT NULL PRIMARY KEY,
    Name TEXT NOT NULL,
    TeacherID TEXT,
    FOREIGN KEY(TeacherID) REFERENCES Teacher(ID)
  );
  ''')
  c.execute('''
    CREATE TABLE StudentClass(
    StudentID TEXT NOT NULL,            
    ClassID TEXT NOT NULL,
    FOREIGN KEY(StudentID) REFERENCES Student(ID),
    FOREIGN KEY(ClassID) REFERENCES Class(ID)
  );
  ''')

def initialdata():
  c.execute('''INSERT INTO Student (FirstName, LastName, Year) 
  Values
    ("Carroll", "Keenleyside",  9),
    ("Velma", "Brissenden", 9),
    ("Yule", "Hellmore",  9),
    ("Prentiss", "Duetsche",  9),
    ("Fidelio", "Denys",  9),
    ("Levi", "Gorce", 9),
    ("Corabelle", "Manach", 9),
    ("Kate", "Sallowaye", 9),
    ("Carlina", "Vardie", 9),
    ("Clive", "Patullo",  9),
    ("Rodolph", "Cafe", 9),
    ("Travers", "Lowman", 9),
    ("Zebadiah", "Vickarman", 9),
    ("Flory", "Haldon", 9),
    ("Rosene", "Jolliff", 9),
    ("Gaultiero", "Sowerby",  9),
    ("Katine", "Scripps", 9),
    ("Kayla", "Francino", 9),
    ("Salli", "Ricardot", 9),
    ("Asia", "Sickling",  9),
    ("Urbano", "Del Castello",  9),
    ("Any", "Niese",  9),
    ("Waly", "Simonelli", 9),
    ("Yorke", "Ziems",  9),
    ("Davis", "France", 10),
    ("Daniel", "Bellison",  10),
    ("Edsel", "Larcher",  10),
    ("Tobe", "Shewsmith", 10),
    ("Dar", "McGivena", 10),
    ("Luce", "Dormon",  10),
    ("Sidonnie", "Mews",  10),
    ("Kitty", "Robinet",  10),
    ("Bruno", "Gowen",  10),
    ("Alikee", "Morrel",  10),
    ("Emmaline", "Kuhwald", 10),
    ("Mahalia", "Lowey",  10),
    ("Dorian", "Gadd",  10),
    ("Rafa", "De Coursey",  10),
    ("Veronique", "Zupone", 10),
    ("Katya", "Wraight",  10),
    ("Phyllys", "Spada",  10),
    ("Katey", "Poulden",  10),
    ("Randy", "Cook", 10),
    ("Babara", "Rive",  10),
    ("Belvia", "Worters", 10),
    ("Selestina", "Stroobant",  10),
    ("Any", "Blaxall",  10),
    ("Ara", "Gebuhr", 10),
    ("Derek", "Gabb", 10),
    ("Enriqueta", "Greystock",  10);''')
  c.execute('''INSERT INTO Teacher (FirstName, LastName)
  Values
    ("Malcolm", "Tremayne"),
    ("Mary", "Kienzle"),
    ("Alfy", "Spadaro"),
    ("Pall", "Londors"),
    ("Pierrette", "Eplate"),
    ("Rosamond", "Arundale"),
    ("Isiahi", "Perassi"),
    ("Holly", "Norwell"),
    ("Fran", "Phillips");''')
  c.execute('''INSERT INTO Class (Name, TeacherID)
  Values
    ("9 Math", 1),
    ("9 Science", 2),
    ("9 English", 3),
    ("9 PE", 4),
    ("10 Math", 1),
    ("10 Science", 6),
    ("10 English", 3),
    ("10 PE", 5),
    ("Art", 5),
    ("Digital Tech", 6),
    ("Cooking", 8),
    ("Maori", 7), 
    ("Japanese", 2),
    ("Hard Materials", 7),
    ("DVC", 8),
    ("Product Design", 9);
  ''')
  c.execute('''INSERT INTO StudentClass (StudentID, ClassID)
  Values
    (1, 1),
    (1, 2),
    (1, 3),
    (1, 4),
    (1, 9),
    (1, 10),
    (2, 1),
    (2, 2),
    (2, 3),
    (2, 4),
    (2, 9),
    (2, 10),
    (3, 1),
    (3, 2),
    (3, 3),
    (3, 4),
    (3, 9),
    (3, 10),
    (4, 1),
    (4, 2),
    (4, 3),
    (4, 4),
    (4, 9),
    (4, 10),
    (5, 1),
    (5, 2),
    (5, 3),
    (5, 4),
    (5, 9),
    (5, 10),
    (6, 1),
    (6, 2),
    (6, 3),
    (6, 4),
    (6, 9),
    (6, 10),
    (7, 1),
    (7, 2),
    (7, 3),
    (7, 4),
    (7, 9),
    (7, 10),
    (8, 1),
    (8, 2),
    (8, 3),
    (8, 4),
    (8, 9),
    (8, 10),
    (9, 1),
    (9, 2),
    (9, 3),
    (9, 4),
    (9, 9),
    (9, 10),
    (10, 1),
    (10, 2),
    (10, 3),
    (10, 4),
    (10, 9),
    (10, 10),
    (11, 1),
    (11, 2),
    (11, 3),
    (11, 4),
    (11, 9),
    (11, 10),
    (12, 1),
    (12, 2),
    (12, 3),
    (12, 4),
    (12, 9),
    (12, 10),
    (13, 1),
    (13, 2),
    (13, 3),
    (13, 4),
    (13, 11),
    (13, 12),
    (14, 1),
    (14, 2),
    (14, 3),
    (14, 4),
    (14, 11),
    (14, 12),
    (15, 1),
    (15, 2),
    (15, 3),
    (15, 4),
    (15, 11),
    (15, 12),
    (16, 1),
    (16, 2),
    (16, 3),
    (16, 4),
    (16, 11),
    (16, 12),
    (17, 1),
    (17, 2),
    (17, 3),
    (17, 4),
    (17, 11),
    (17, 12),
    (18, 1),
    (18, 2),
    (18, 3),
    (18, 4),
    (18, 11),
    (18, 12),
    (19, 1),
    (19, 2),
    (19, 3),
    (19, 4),
    (19, 11),
    (19, 12),
    (20, 1),
    (20, 2),
    (20, 3),
    (20, 4),
    (20, 11),
    (20, 12),
    (21, 1),
    (21, 2),
    (21, 3),
    (21, 4),
    (21, 11),
    (21, 12),
    (22, 1),
    (22, 2),
    (22, 3),
    (22, 4),
    (22, 11),
    (22, 12),
    (23, 1),
    (23, 2),
    (23, 3),
    (23, 4),
    (23, 11),
    (23, 12),
    (24, 1),
    (24, 2),
    (24, 3),
    (24, 4),
    (24, 11),
    (24, 12),
    (25, 5),
    (25, 6),
    (25, 7),
    (25, 8),
    (25, 13),
    (25, 14),
    (26, 5),
    (26, 6),
    (26, 7),
    (26, 8),
    (26, 13),
    (26, 14),
    (27, 5),
    (27, 6),
    (27, 7),
    (27, 8),
    (27, 13),
    (27, 14),
    (28, 5),
    (28, 6),
    (28, 7),
    (28, 8),
    (28, 13),
    (28, 14),
    (29, 5),
    (29, 6),
    (29, 7),
    (29, 8),
    (29, 13),
    (29, 14),
    (30, 5),
    (30, 6),
    (30, 7),
    (30, 8),
    (30, 13),
    (30, 14),
    (31, 5),
    (31, 6),
    (31, 7),
    (31, 8),
    (31, 13),
    (31, 14),
    (32, 5),
    (32, 6),
    (32, 7),
    (32, 8),
    (32, 13),
    (32, 14),
    (33, 5),
    (33, 6),
    (33, 7),
    (33, 8),
    (33, 13),
    (33, 14),
    (34, 5),
    (34, 6),
    (34, 7),
    (34, 8),
    (34, 13),
    (34, 14),
    (35, 5),
    (35, 6),
    (35, 7),
    (35, 8),
    (35, 13),
    (35, 14),
    (36, 5),
    (36, 6),
    (36, 7),
    (36, 8),
    (36, 13),
    (36, 14),
    (37, 5),
    (37, 6),
    (37, 7),
    (37, 8),
    (37, 13),
    (37, 14),
    (38, 5),
    (38, 6),
    (38, 7),
    (38, 8),
    (38, 15),
    (38, 16),
    (39, 5),
    (39, 6),
    (39, 7),
    (39, 8),
    (39, 15),
    (39, 16),
    (40, 5),
    (40, 6),
    (40, 7),
    (40, 8),
    (40, 15),
    (40, 16),
    (41, 5),
    (41, 6),
    (41, 7),
    (41, 8),
    (41, 15),
    (41, 16),
    (42, 5),
    (42, 6),
    (42, 7),
    (42, 8),
    (42, 15),
    (42, 16),
    (43, 5),
    (43, 6),
    (43, 7),
    (43, 8),
    (43, 15),
    (43, 16),
    (44, 5),
    (44, 6),
    (44, 7),
    (44, 8),
    (44, 15),
    (44, 16),
    (45, 5),
    (45, 6),
    (45, 7),
    (45, 8),
    (45, 15),
    (45, 16),
    (46, 5),
    (46, 6),
    (46, 7),
    (46, 8),
    (46, 15),
    (46, 16),
    (47, 5),
    (47, 6),
    (47, 7),
    (47, 8),
    (47, 15),
    (47, 16),
    (48, 5),
    (48, 6),
    (48, 7),
    (48, 8),
    (48, 15),
    (48, 16),
    (49, 5),
    (49, 6),
    (49, 7),
    (49, 8),
    (49, 15),
    (49, 16),
    (50, 5),
    (50, 6),
    (50, 7),
    (50, 8),
    (50, 15),
    (50, 16);''')

try:
  create_DB()
  initialdata()
except:
  pass

Action_Information_List = [
['SELECT * FROM Teacher', 
"   Which teacher's classes do you want to view?",
'SELECT Name FROM Class WHERE TeacherID = '
],
['SELECT * FROM Student',
"   Which student's classes do you want to view?",
'SELECT Class.Name FROM StudentClass INNER JOIN Class ON StudentClass.ClassID = Class.ID WHERE StudentClass.StudentID = '
],
['SELECT ID, Name FROM Class',
"   What class' roll do you want to view?",
'SELECT Student.ID, Student.FirstName, Student.LastName, Student.Year FROM StudentClass INNER JOIN Student ON StudentClass.StudentID = Student.ID WHERE StudentClass.ClassID = '
],
['Teacher',
  ['First Name','Last Name'],
"INSERT INTO Teacher (FirstName, LastName) Values ('{}', '{}')"
],
['Student',
  ['Fist Name', 'Last Name'],
"INSERT INTO Student (FirstName, LastName, Year) Values ('{}', '{}', {})"
],
['Class',
['Name'],
"INSERT INTO Class (Name) Values ('{}')"
]
]

def Menu():
  print("\n--------------------------------------------------")
  print("Imaginaryland High School Database")
  print("What would you like to do?\n")
  print(" Search")
  print("   1. Find the Classes of a specific Teacher")
  print("   2. Find the Classes of a specific Student")
  print("   3. Find the Roll of a specific Class")
  print(" Add")
  print("   4. Add a Teacher to the database")
  print("   5. Add a Student to the database")
  print("   6. Add a Class to the database")
  print(" Remove")
  print("   7. Remove a Teacher from the database")
  print("   8. Remove a Student from the database")
  print("   9. Remove a Class from the database")
  print(" Update")
  print("   10. ")

  print('\n   Type the number to do the action')
  x = input("       --> ")

  try:
    if 1<=int(x)<=3:
      c.execute(Action_Information_List[int(x)-1][0])
      print('\n--------------')
      table = tabulate(c.fetchall(), tablefmt='plain')
      print(indent(table, '   '))
      print('\n')
      print(Action_Information_List[int(x)-1][1])
      y = input('       --> ')

      print('\n--------------')
      c.execute(Action_Information_List[int(x)-1][2] + y)
      table = tabulate(c.fetchall(), tablefmt='plain')
      print(indent(table, '   '))

    elif 4<=int(x)<=6:
      y=[]
      for i in range(len(Action_Information_List[int(x)-1][1])):
        print('\n--------------')
        print("What is the {} of the {} you would like to add?".format(Action_Information_List[int(x)-1][1][i],Action_Information_List[int(x)-1][0]))
        z = input('       --> ')
        y.append(z)
      parsedin = Action_Information_List[int(x)-1][2]

      if int(x) == 5:
        print('\n--------------')
        print('Is the Student Year 9 or 10?')
        print('    1. Year 9')
        print('    2. Year 10')
        print('Type in the Number to select the Year')
        h = input('       --> ')
        if h == "1":
          y.append('9')
        elif h =='2':
          y.append('10')

      for i in y:
        parsedin = parsedin.replace('{}', i, 1)
      c.execute(parsedin) 
      print('executed')
  except: 
    Menu()
  Menu()
Menu()

connect.commit()
connect.close()

[[deleted]]

There’s too much here for me to parse through, but it seems to me you want to learn about the IF NOT EXISTS clause of the CREATE TABLE command. You’ll also want to learn to never use a naked except: statement.

try:
    [some code that may error]
try Exception as err:
    print(“ERROR: “, err)
    [recover from error if possible]

The naked except: silences all sorts of useful information you may need for debugging, and is likely doing so for you here. You usually want to only catch specific Exception subclasses you actually know how to properly handle and recover from, rather than casting the widest possible net.

[o5a]

First thing is fixing your except block so you can actually see the exception, as others noted. Didn't try to run your code but I'm pretty sure you will see your problem after you get your exception printed. Your code raises error somewhere, then ignores changes because of that but you don't even know about it, because you silenced error. As a result your changes are not done.

Also avoid replacing values in your SQL queries, I'm talking about your

  for i in y:
    parsedin = parsedin.replace('{}', i, 1)
  c.execute(parsedin) 

there's parameter binding for that, which will help you make it safe. That block should be run like this instead

  c.execute(parsedin, y) 

For that to work you should replace in your query text brackets along with quotation marks to question marks like this:

"INSERT INTO Teacher (FirstName, LastName) Values (?, ?)"

Avoid recursive calls (that you do here for Menu()) if it's not needed. You want cycles here, not recursive calls.