时间戳

首页 > 解决方案 > python mysql connector multi=true 即使出现SQL错误也继续

python - python mysql connector multi=true 即使出现SQL错误也继续

问题描述

我正在使用 mysql 连接器 python 连接到 mysql 并通过设置 multi=True 运行多个查询。我得到了结果。如果任何语句中存在 sql 错误，则忽略错误之后的查询。即使发生 SQL 错误，如何使用“--force”并继续

conn = MYSQL.MySQLConnection(user=sql_username,password=sql_password,host='127.0.0.1',database=sql_main_database,port=3306)
cursor = conn.cursor(buffered=True)
try:
    results=cursor.execute("select now();SELECT UNIX_TIMESTAMP(now());select 'test';SELECT UTC_TIMESTAMP();select 'aravinth';select yuu;show processlist;select 1",multi=True)
except Exception as e:
    print(e)

count = 1

for result in results:
    if result.with_rows:

        print("Rows produced by statement '{}':".format(
        result.statement))
        print(result.fetchall())

    else:
         print("Number of rows affected by statement '{}': {}".format(
         result.statement, result.rowcount))

except Exception as e:
    print(e)

我的输出是

Rows produced by statement 'select now()':
[(datetime.datetime(2020, 6, 3, 13, 4, 54),)]
Rows produced by statement 'SELECT UNIX_TIMESTAMP(now())':
[(1591169694,)]
Rows produced by statement 'select 'test'':
[('test',)]
Rows produced by statement 'SELECT UTC_TIMESTAMP()':
[(datetime.datetime(2020, 6, 3, 7, 34, 54),)]
Rows produced by statement 'select 'aravinth'':
[('aravinth',)]
1054 (42S22): Unknown column 'yuu' in 'field list'
end

即使任何中间查询失败，我也想继续执行所有查询。

标签： pythonmysqlpython-3.x

解决方案

因为其中一个中间查询抛出一个Exception，所以您不能强制该try块从该点继续执行。你宁愿做的是：

  1. 宣布 results = []
  2. try-catch在其自己的块中单独运行每个查询。
  3. 将每个查询的结果附加到results.
  4. 以后使用results。

示例代码可以是：

# assume that queries is a list containing all queries
results = []
for query in queries:
    try:
        result = cursor.execute(query)
        results.append(result)
    except Exception as e:
        print(e)

for result in results:
    if result.with_rows:

        print("Rows produced by statement '{}':".format(
        result.statement))
        print(result.fetchall())

推荐阅读