Python3.7.5 Mariadb 10.4.11查询绑定导致异常

Question

在这个精心设计的例子中，我尝试选择“Fred”硬编码，然后使用绑定变量。

选得很好。绑定变量示例在异常情况下失败。

import sys
import string
import mysql.connector

def sanity_test():
    try:
        print("connecting")
        connection = mysql.connector.connect(user='root', password='root',
                                             host='127.0.0.1', database="MyJournal")
        print("test1")
        cursor1 = connection.cursor()
        cursor1.execute("select 'Fred'")
        records = cursor1.fetchall()
        print("There are " + str(cursor1.rowcount) + " rows in the first result set")

        print("test2")
        cursor2 = connection.cursor()
        name = "Fred";
        cursor2.execute("select %s", name)
        records2 = cursor2.fetchall()
        print("There are " + str(cursor2.rowcount) + " rows in the second result set")

    except:
        e = sys.exc_info()[0]
        print ("Exception: ", e)
    else:
        cursor1.close()
        cursor2.close()
        connection.close()
    input("Press Enter to continue...")

if __name__ == '__main__':
    sanity_test()

结果的输出是

connecting
test1
There are 1 rows in the first result set
test2
Exception:  <class 'mysql.connector.errors.ProgrammingError'>

我遗漏了什么？有什么技术可以获得比简洁的例外更多的信息？

Answer 1

1)处理每个逻辑块的异常(例如连接，然后是cursor.create + execute)，但不超过几个块。

2)获取更多描述性错误消息。

except mysql.connector.Error as err:
    print("Unexpected error : ", format(err))

3) SQL标准要求语句中字符串的单引号或双引号，例如cursor.execute("SELECT '%s'", ("foo",))