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
Advertisement
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!