Insert new value in sql database using the same id

Hi everyone, I created a database for the school where I work that counts all the days when our pupils weren’t at school.

 pupils = Table('alunno', metadata_obj,
              Column('id', Integer, primary_key=True),
               Column('nome', String(25), nullable=False),
               Column('classe', String(25), nullable=False),
               Column('assenze', Integer, nullable=True),
               Column('data', DATETIME, nullable=True)
)

metadata_obj.create_all(db)

populate the database

for nome, classe, assenze in alunni_lst:
    ins = pupils.insert().values(nome=nome,
                                 classe=classe,
                                assenze=assenze)
    conn = db.connect()
    conn.execute(ins)


frst_question = input("Ci sono assenti oggi?\n> ")
if frst_question == 'si':
today_str = datetime.today().strftime("%d/%m/%Y")
today = datetime.strptime(today_str, "%d/%m/%Y")

    ask_for_number = int(input("Quanti sono gli assenti oggi?\n> "))
    assenti_lst = []
    for i in range(ask_for_number):
        ask_for_names = input("Dimmi il nome dell'assente.\n> ")
        ask_for_hours = int(input("Quante ore di assenza?\n> "))
        ask_for_room = input("Di che classe è?\n> ")
        data = (ask_for_names, ask_for_hours, ask_for_room)
        assenti_lst.append(data)
        for nome, assenze, classe in assenti_lst:
            ins = pupils.insert().values(nome=nome,
                                         classe=classe,
                                         assenze=assenze,
                                         data=today)
            conn = db.connect()
            conn.execute(ins)

The code works without problem. The problem is that every time that I insert a pupil the database will be updated with a new id, instead I would like to have the same id for the same pupil always.

How could I do it?

I’m using sqlalchemy with python

Thank you very much!

P.s.
Sorry for coding in italian, but I needed it for showing the result to a colleague

That’s because in the alunno table, id alone is the primary key.

For your use case, it seems like you should have a composite key of id and data because in your use case you want each row to be identified by a student,date pair.

The above will get you to your goal, but in addition to this, I recommend creating two tables. One for the student information (which will mostly be static) and another for tracking their arrival times.

1 Like

So, should I avoid to declare a primary key and or declare primary keys both name of the student and id?

Neither of those options, you should declare primary keys for both id and data.