Updating MySQL database with python-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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