用python更新MySQL数据库
用python更新MySQL数据库
提问于 2022-05-27 18:51:21
我有一些问题要更新我的数据库，它总是抛出相同的错误。
ProgrammingError: 1064 (42000)：您的SQL语法出现了错误；请检查与MySQL服务器版本对应的手册，以获得在第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()任何帮助都将是最感谢的。
请注意，上面的部分用于在数据库中插入数据，并且没有出现任何问题。
参见错误屏幕截图的链接。
回答 2
Stack Overflow用户
发布于 2022-05-27 19:00:59
语法错误是由于要设置的列列表后面的额外逗号造成的。
但是，您也缺少要将列设置为的值。不能在查询中使用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列没有意义，因为您没有更改它--您正在使用WHERE子句中的id来选择带有该ID的行。
Stack Overflow用户
发布于 2022-05-27 19:23:25
这是完整的代码行。
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()
ProgrammingError: 1146 (42S02): Table 'employee_db.employee_db' doesn't exist页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72409908
复制相关文章
相似问题
腾讯云开发者
