Updating MySQL database with python

Question

I having some issues to update my database, it keeps throwing the same error

ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ID = id' at line 10

The database update function.

def update():
display_db()
cursor = employee_db.cursor()
id = int(input('Selected Employee ID to be updated: '))
sql = "SELECT * FROM employee_db.employee_data;"
db_conn = create_engine("mysql+mysqldb://root:root@localhost/employee_db") 
data = pd.read_sql_query(sql, db_conn)
df = pd.DataFrame(data)
name = input('Enter the Empoyee name: ')
surname = input('Enter Empoyee surname: ')
address = input('Enter Empoyee address: ')
credentials = input('Enter Empoyee credentials: ')
department = input('Enter Empoyee department: ')
hr_rate = input('Enter Empoyee hourly rate: ')
email = name[0] + surname + '@avengers.av'
email = email.lower()
emp = [{ 
 'ID': id,
 'Name': name, 
 'Surname': surname, 
 'Address': address,
 'Credentials': credentials,
 'Department': department,
 'Hourly_rate': hr_rate,
 'email': email
 }]
# Update employee
updated_employee = """UPDATE employee_db 
 SET ID = id,
 Name = name,
 Surname = surname,
 Address = address,
 Credentials = credentials,
 Department = department,
 Hourly_rate = hr_rate,
 email = email,
 WHERE ID = id;
cursor.execute(updated_employee)
employee_db.commit()
display_db()

Any help will be the most appreciated.
Note that the top part was used to insert data in the database and it worked without any issue.

See link to screenshot of error.

Update menu errors

• Michael Butscher: I guess the comma after "email" should be removed.

Answer 1

The syntax error is due to the extra comma after the list of columns to set.

But you're also missing the values that you want to set the columns to. You can't use python variables in the query. You should put placeholders there and pass the values as the second argument to cursor.execute().

updated_employee = """UPDATE employee_db 
 SET Name = %s,
 Surname = %s,
 Address = %s,
 Credentials = %s,
 Department = %s,
 Hourly_rate = %s,
 email = %s
 WHERE ID = %s;"""
cursor.execute(updated_employee, (name, surname, address, credentials, department, hr_rate, email, id))
employee_db.commit()

There's no point in setting the id column, since you're not changing it -- you're using id in the WHERE clause to select the row with that ID.

• raj_b: Thks, I have made these amendments, there some line of codes which I commented to resolve the aforementioned error, I'm editing my post and posting the whole code
• Barmar: Your code in the screenshot had the %s in the parameters list, it needs to be in the SQL string.

Answer 2

Here is the complete line of codes.

def update():
display_db()
cursor = employee_db.cursor()
db_connect()
id = int(input('Selected Employee ID to be updated: '))
sql = "SELECT * FROM employee_db.employee_data;"
db_conn = create_engine("mysql+mysqldb://root:root@localhost/employee_db") 
data = pd.read_sql_query(sql, db_conn)
df = pd.DataFrame(data)
name = input('Enter the Empoyee name: ')
surname = input('Enter Empoyee surname: ')
address = input('Enter Empoyee address: ')
credentials = input('Enter Empoyee credentials: ')
department = input('Enter Empoyee department: ')
hr_rate = input('Enter Empoyee hourly rate: ')
email = name[0] + surname + '@avengers.av'
email = email.lower()
emp = [{ 
 'ID': id,
 'Name': name, 
 'Surname': surname, 
 'Address': address,
 'Credentials': credentials,
 'Department': department,
 'Hourly_rate': hr_rate,
 'email': email
 }]
 
employee = emp
db_connect()
for item in employee:
 employee_df = pd.DataFrame([item])
 display(employee_df)
 db_conn = create_engine("mysql+mysqldb://root:root@localhost/employee_db")
 employee_df.to_sql(con=db_conn, name='employee_data', if_exists='replace', index=False)
# Update employee
updated_employee = """UPDATE employee_db 
 SET Name = %s,
 Surname = %s,
 Address = %s,
 Credentials = %s,
 Department = %s,
 Hourly_rate = %s,
 email = %s
 WHERE ID = %s;"""
cursor.execute(updated_employee, (name, surname, address, credentials, department, hr_rate, email, id))
cursor.execute(updated_employee)
employee_db.commit()
display_db()

screenshot of "new" errors... Update menu Errors

ProgrammingError: 1146 (42S02): Table 'employee_db.employee_db' doesn't exist

• raj_b: Database table was wrongly set it should have been employee_data, thks @barmar for your help... very much appreciated...