使用Python更新MySQL数据库

Question

我遇到了一些问题来更新数据库，它不断抛出相同的错误

编程：1064（42000）：您的SQL语法中有错误；查看与您的MySQL Server版本相对应的手册，以获取正确的语法，该语法在第10行中附近使用“ Where id = ID”的手册

。数据库更新功能。

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()

任何帮助将是最受赞赏的。
请注意，顶部用于在数据库中插入数据，并且没有任何问题起作用。

请参阅链接到错误的屏幕截图。

更新菜单 errors

Answer 1

语法错误是由于要设置的列列表后的额外逗号造成的。

但是您也缺少要将列设置为的值。您不能在查询中使用Python变量。您应该将占位符放在那里，并将其作为第二个参数将其传递给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()

设置ID列没有意义，因为您没有更改它 - 您正在使用id在中使用 strape oflect带有该ID的行。

Answer 2

这是代码的完整行。

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()

“新”错误的屏幕截图...
更新菜单
errors

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