Get data from database and write it back in again-CodePudding

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:[email protected]: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

CodePudding user response：

c.fetchall() returns a list of tuples you need to unpack in your loop

c.execute("SELECT * FROM zugversuch_probe_1")
for record in c.fetchall():
    record_id, probe_dehnung, dehnung, probe_standardkraft = record
    dehnung = calc_dehnung(probe_dehnung)
    c.execute("""UPDATE zugversuch_probe_1 SET
        Dehnung = %s
        WHERE ID = %s""",
              (
                  dehnung.get(),
                  record_id
              ))

CodePudding user response：

I dont see any reason why you're using dehnung.get() . The .get() function is used when you retrieve values from a dictionary, but dehnung seems to be a list (or number?). This possibly breaks your code in some unexpected way and confuses the sql engine.

you're printing that variable here

print("Dehnung", dehnung)

if thats the value you want to insert, simply remove the .get() the next 2 times you're using it.

You also need to unpack the values from the variable "record_id". I suggest you rename this variable to "record" because it isnt the record_id - its one line of the results from the db, which contains all the values from that line, and the id is only one of them (the first one). you can access it with record_id[0]

c.execute("SELECT * FROM zugversuch_probe_1 WHERE ID = "   str(record_id[0]))

you're also possibly using the wrong value here:

dehnung = calc_dehnung(records[1])

you probably meant to use record_id[1] instead of records[1]. Again, I strongly recommend you rename the variable "record_id" to "record", this seems to be the source of some confusion