pymysql: one try except can't catch multi-sql errors

Question

UPDATE

conn = pymysql.connect(host='localhost',user='user',password='password',db='mydb',charset='utf8')
cur = conn.cursor(pymysql.cursors.DictCursor)
try:
    query = 'select * from test where id = 1;abcd'
    cur.execute(query)
    res = cur.fetchone()
    print(res)
except Exception as e:
    print(e)

try:
    query = 'select * from test where id = 2;'
    cur.execute(query)
    res = cur.fetchone()
    print(res)
except Exception as e:
    print(e)

The code makes the result as below:

{'name': '', 'score': 1.1, 'id': 1}
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'abcd' at line 1")

The first line {'name': '', 'score': 1.1, 'id': 1} comes from the first query before ; in the first try...except.... And then the error message comes from the second try...except... but it comes from the sql query abcd, which means that the cur.execute(query) in the second try...except... produces the exception because of abcd and select * from test where id = 2; isn't executed.

So how can I ignore the abcd and make the second query execute as expected?

ORIGINAL QUESTION
I'm building a web server with python. I use MySQL as the database and the pymysql as the interface of the database.

Now I get an issue:
When some sql query makes an error because of a ;, the program will be blocked even if I've used try...except.... Here is an example:

import pymysql
import pymysql.cursors

conn = pymysql.connect(host='localhost',user='user',password='password',db='mydb',charset='utf8')
cur = conn.cursor(pymysql.cursors.DictCursor)
try:
    query = 'select * from test where id = 1;abcd'  <--- exception!
    cur.execute(query)
    res = cur.fetchone()
    print(res)
except Exception as e:
    print(e)

query = 'select * from test where id = 2;'
cur.execute(query)
res = cur.fetchone()
print(res)

As you see, the first query is illegal because of the part ;abcd. So it will produce an error. However, the try...except... can not catch this exception because I find that the print(e) isn't executed. Here is the messages that I get:

{'score': 1.1, 'name': '', 'id': 1}
Traceback (most recent call last):
  File "db_mysql.py", line 23, in <module>
    cur.execute(query)
  File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 161, in execute
    while self.nextset():
  File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 103, in nextset
    return self._nextset(False)
  File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 98, in _nextset
    conn.next_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 860, in next_result
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1014, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 107, 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 MySQL server version for
the right syntax to use near 'abcd' at line 1")

I can't understand why the error can not be caught by try...except.... Moreover, it seems that the query which produces the error is abcd, instead of select * from test where id = 1;abcd. If I'm right, I think the ; make this query into two queries.

Also, if I remove the ;, which means that the first query becomes query = 'select * from test where id = 1abcd', the try...except... could catch the error so the second query can be executed as expected.

So my question is: why the try...except... can't catch the error? What could I do to handle all of sql errors so that the program won't be blocked?

Answer 1

So my question is: why the try...except... can't catch the error? What could I do to handle all of sql errors so that the program won't be blocked?

Maybe with the good exception name?

try:
    …
except pymysql.err.ProgrammingError as except_detail:
        print("pymysql.err.ProgrammingError: «{}»".format(except_detail))

See all exceptions type in help(pymysql.err) or on PyMySQL's Github