Python Maria DB 구문

Python Maria DB 구문

제가 어디가 잘못됐는지 아는 사람 있나요?DB 업데이트 구문이 맞는 것 같습니다.또한 각 기능 내에서 연결을 열고 닫으려면 연결을 닫아야 하는지 궁금합니다.예를 들어 각 함수가 일반적인 예시와 같이 삽입용, 업데이트용 및 삭제용 등 서로 다른 유형의 DB 명령을 수행한다고 가정합니다.

출력:

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

업데이트 구문은 다음과 같습니다.

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/ 를 참조해 주세요.

쿼리 문장은 올바르지 않습니다.이거 드셔보세요.

sql = 'UPDATE students_info SET firstname="'+firstname+'", lastname=="'+lastname+'", email="'+email+'", address="'+address+'", DOB="'+address+'") Where id="'+student_id+'"'

이게 도움이 됐으면 좋겠다.

언급URL : https://stackoverflow.com/questions/51644037/python-maria-db-syntax