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

I guess the comma after "email" should be removed.

Michael Butscher
– Michael Butscher

2022-05-27 18:56:23 +00:00
Commented May 27, 2022 at 18:56 — Michael Butscher
– Michael Butscher, Commented May 27, 2022 at 18:56

Barmar · Accepted Answer · 2022-05-27 19:00:59Z

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.

answered May 27, 2022 at 19:00

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

raj_b Over a year ago

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 Over a year ago

Your code in the screenshot had the %s in the parameters list, it needs to be in the SQL string.

raj_b · Accepted Answer · 2022-05-27 19:23:25Z

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

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

Collectives™ on Stack Overflow

Updating MySQL database with python

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related