When I put data in DB with python, I met a problem that auto increment column value is larger than I expected.

Assume that I use the following function multiple times to put data into the DB. 'db_engine' is a DB, which contains table 'tbl_student' and 'tbl_score'. To manage the total number of student, table 'tbl_student' has Auto increment column named 'index'.

def save_in_db(db_engine, dataframe):
    # tbl_student
    student_dataframe = pd.DataFrame({
        "ID":dataframe['ID'],
        "NAME":dataframe['NAME'],
        "GRADE":dataframe['GRADE'],
        })
    student_dataframe.to_sql(name="tbl_student",con=db_engine, if_exists='append', index=False)

    # tbl_score
    score_dataframe = pd.DataFrame({
        "SCORE_MATH": dataframe['SCORE_MATH'],
        "SCORE_SCIENCE":dataframe['SCORE_SCIENCE'],
        "SCORE_HISTORY":dataframe['SCORE_HISTORY'],
    })
    score_dataframe.to_sql(name="tbl_score",con=db_engine, if_exists='append', index=False)

'tbl_student' after some inputs is as follows:

Please take a look column 'index'. When I put in several times, 'index' has larger value than I expected.

What should I try to solve this problem?

CodePudding user response：

You could try:

student_dataframe.reset_index()

CodePudding user response：

Actually, the problem situation is 'index' part connected to another table as a FOREIGN KEY. Every time I add a data, the error occurred because there was no key(because the index value is not continuous!).

I solve this problem by checking the index part once before put data in DB and setting it as key. Following code is what I tried.

index_no = get_index(db_engine)
dataframe.index = dataframe.index   index_no   1 - len(dataframe)
dataframe.reset_index(inplace=True)

If anyone has the same problem, it could be nice way to try another way rather than trying to make auto increment key sequential.