Python Maria DB Syntax

Question

Does anyone know where I am going wrong here? The syntax for the DB update looks correct to me. Also I am wondering if I need to close connection say to open and close a connection within each function. Lets say for example that each function performs a different type of DB command, one for insert, one for update and one for delete, just as a generic example.

Output:

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

My code:

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

score 2 · Accepted Answer · edited Aug 02 '18 at 05:33

2

The syntax for update is:

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

You are trying to UPDATE like an INSERT. But UPDATE only supports setting each column name independently, Not with a column list.

Try:

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

See https://mariadb.com/kb/en/library/update/

edited Aug 02 '18 at 05:33

Lex

4,749
3
45
66

answered Aug 02 '18 at 04:15

juanbits

347
2
11

Thanks. You are correct. Shortly after I posted this I realised I had to map each column to variable one by one like above – anfield Aug 02 '18 at 18:17
But what I am confused about also is - should the connection be opened and then closed in each function in a program? – anfield Aug 02 '18 at 18:24
Yes, because you are closing the connection with the lines: cur.close() conn.close() – juanbits Aug 02 '18 at 18:25

score 0 · Answer 2 · answered Aug 02 '18 at 04:18

0

Query statement is nor right. Try this-

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

Hope this helps.

answered Aug 02 '18 at 04:18

V.Khakhil

285
5
22

1

This is vulnerable to SQL injection. You should use named variables or `escape_string` https://stackoverflow.com/questions/3617052/escape-string-python-for-mysql – Lex Aug 02 '18 at 04:26

Python Maria DB Syntax

2 Answers2