Python 3.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(处理每个逻辑块的异常(例如连接，然后游标创建+执行(，但不能处理多个块。

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

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

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