Get data from database and write it back in again

I have written a function by reading an excel file and writing it to the database. This works!

Getting data from the database also works.

Where I get stuck is at the point that the function should read each row, calculate it and write the result back to the database.

I also want to find out how many rows were written in the database and then pass that number to the for-loop.

The first function is used to read the Excel data and write it to the database:

    def Load_into_database():
    file_path = label_file["text"]
    try:
        excel_filename = r"{}".format(file_path)
        if excel_filename[-4:] == ".csv":
            df = pd.read_csv(excel_filename, header=0, names=['Probe_Dehnung', 'Probe_Standardkraft'], sheet_name='Probe 1', skiprows=2, usecols="A:B")
        else:
            df = pd.read_excel(excel_filename, header=0, names=['Probe_Dehnung', 'Probe_Standardkraft'], sheet_name='Probe 1', skiprows=2, usecols="A:B")

    except ValueError:
        tk.messagebox.showerror("Information", "The file you have chosen is invalid")
        return None
    except FileNotFoundError:
        tk.messagebox.showerror("Information", f"No such file as {file_path}")
        return None

    engine = create_engine("mariadb+mariadbconnector://root:pw123@127.0.0.1:3306/polymer")
    df.to_sql('zugversuch_probe_1',
              con=engine,
              if_exists='append',
              index=False)

    c.execute("SELECT * FROM zugversuch_probe_1")
    records = c.fetchall()
    print("Records", records)
    calculation(records)

The second function is to read each row from the one column and calculate it row by row and then write it back into the database:

def calculation(records):
    # query the database
    for record_id in records:
        c.execute("SELECT * FROM zugversuch_probe_1 WHERE ID = " + str(record_id))
        records = c.fetchall()[0]

        # Berechnung Dehnung
        dehnung = calc_dehnung(records[1])
        print("Dehnung", dehnung)

        sql_command = """
        INSERT INTO zugversuch_probe_1
        (Dehnung)
        VALUES(%s)"""

        c.execute("""UPDATE zugversuch_probe_1 SET
            Dehnung = %s
            WHERE ID = %s""",
                  (
                      dehnung.get(),
                      record_id
                  ))

        values = (dehnung.get())

        # commit changes
        conn.commit
        # close connection
        conn.close()

def calc_dehnung(value_list):
    return (value_list[0] / 0.123) * 100

After I run my code, I get the error message:

c.execute(“SELECT * FROM zugversuch_probe_1 WHERE ID = ” + str(record_id)) mariadb.OperationalError: Unknown column ‘None’ in ‘where clause’

Unfortunately, I also have the problem that the ID in the database does not start again at 0 after I have deleted data, but continues to count.

Here is a screenshot of the database: Screenshot Database

Here is a screenshot of the Excel file: Screenshot Excel

Thank you in advance

Answer

yes it works!

it didn’t work for the reason that I forgot the brackets in conn.commit()

Here is my new code which i got from @vinzBad

    def update():
        c.execute("SELECT * FROM zugversuch_probe_1")
        for record in c.fetchall():
            try:
                record_id, probe_dehnung, dehnung, probe_standardkraft = record
                dehnung = (probe_dehnung / 0.123) * 100

                c.execute("""UPDATE zugversuch_probe_1 SET
                        Dehnung = %s
                        WHERE ID = %s""",
                          (
                              dehnung,
                              record_id
                          ))

                conn.commit()

            except mariadb.Error as e:
                print(f"Error connecting to MariaDB Platform: {e}")
                sys.exit(1)

        conn.close()

Here is a Screenshot mariadb

Thank you for the help!