Python Maria 数据库语法

Question

有谁知道我哪里出错了？数据库更新的语法对我来说看起来是正确的。另外我想知道是否需要关闭连接，比如在每个函数中打开和关闭连接。假设每个函数执行不同类型的数据库命令，一个用于插入，一个用于更新，一个用于删除，就像一个通用示例。

输出:

[root@localhost student_program]# python modify_student.py
Connection successful!!
Enter the id of the student record you wish to modify: 21
Is this student personal information you want to modify - y or n: y
Enter the first name: Jake
Enter the last name: Mc Intyre
Enter the email address: jake@noemail.com
Enter the address: 300 Main Street, New York
Enter the DOB in YYYY-MM-DD: 1960-01-01
Traceback (most recent call last):
  File "modify_student.py", line 38, in <module>
    modify_student()
  File "modify_student.py", line 29, in modify_student
    cur.execute(sql, [firstname, lastname, email, address, DOB, student_id])
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 893, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1103, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1396, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1059, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(firstname, lastname, email, address, DOB)VALUES ('Jake','Mc Intyre','jake@noema' at line 1")

我的代码:

import os,pymysql

db_root = '/var/lib/mysql/'

db_to_create = 'students'
db_to_use = 'students'


conn = pymysql.connect(host='localhost',  user='root', passwd='dbadmin',  cursorclass=pymysql.cursors.DictCursor)

print('Connection successful!!')


def modify_student():
student_id = input("Enter the id of the student record you wish to modify: ")
student_info = input("Is this student personal information you want to modify - y or n: ")
if student_info == 'y':
    firstname = input("Enter the first name: ")
    lastname = input("Enter the last name: ")
    email = input("Enter the email address: ")
    address = input("Enter the address: ")
    DOB = input("Enter the DOB in YYYY-MM-DD: ")

    cur = conn.cursor()
    command = "use %s; " %db_to_use
    cur.execute(command)

    sql = 'UPDATE students_info SET (firstname, lastname, email, address, DOB)VALUES (%s,%s,%s,%s,%s) WHERE ID = (%s);'
    cur.execute(sql, [firstname, lastname, email, address, DOB, student_id])

    print(cur.execute)
    conn.commit()
    cur.close()
    conn.close()
else:
    print("else")

modify_student()

Answer 1

更新的语法是:

UPDATE tablename SET name='%s', email='%s' WHERE id='%s'

您正在尝试像 INSERT 一样进行更新。但是 UPDATE 只支持单独设置每个列名，不支持列列表。

尝试:

sql = "UPDATE students_info SET firstname='%s', lastname='%s', email='%s', address='%s', DOB='%s' WHERE ID='%s'"
cur.execute(sql, [firstname, lastname, email, address, DOB, student_id])

参见 https://mariadb.com/kb/en/library/update/