Python遍历SQL调用Azure语音转文字循环写法及ffmpeg报错求助

核心问题根因 #

• 循环逻辑与数据库操作位置错误：你将cursor.close()、conn.close()放在了遍历数据库行的循环体内，处理完第一条通话OID就直接关闭了数据库连接，后续行无法正常读取；同时你额外嵌套的数组遍历逻辑完全冗余，没有实际作用还容易引发变量作用域问题。
• MP3文件有效性校验缺失：调用请求接口下载音频后，没有校验接口返回是否成功，若出现鉴权失败、OID无效、网络异常等情况，下载到本地的是无效空文件或错误响应内容，pydub调用ffmpeg读取非法MP3文件就会抛出你遇到的解码错误。
• 函数定义位置不合理：将音频识别函数放在循环体内，每次循环都会重复定义函数，属于不必要的性能损耗。

修正后代码 #

import azure.cognitiveservices.speech as speechsdk
import time
from os import path, remove
from pydub import AudioSegment
import requests
import hashlib
import sys
import os.path
import pyodbc

# 把识别函数提到循环外，接受OID作为参数
def speech_recognize_continuous_from_file(oid):
    speech_config = speechsdk.SpeechConfig(subscription="*", region="*")
    speech_config.speech_recognition_language = "nl-NL"
    audio_config = speechsdk.audio.AudioConfig(filename="Telefoongesprek.wav")

    speech_recognizer = speechsdk.SpeechRecognizer(speech_config=speech_config, audio_config=audio_config)

    done = False

    def stop_cb(evt):
        print('CLOSING on {}'.format(evt))
        nonlocal done
        done = True

    all_results = []
    def handle_final_result(evt):
        if evt.result.text:
            all_results.append(evt.result.text)
    speech_recognizer.recognized.connect(handle_final_result)
    speech_recognizer.session_started.connect(handle_final_result)
    speech_recognizer.session_stopped.connect(handle_final_result)
    speech_recognizer.canceled.connect(handle_final_result)
    speech_recognizer.session_stopped.connect(stop_cb)
    speech_recognizer.canceled.connect(stop_cb)

    speech_recognizer.start_continuous_recognition()
    while not done:
        time.sleep(.5)

    speech_recognizer.stop_continuous_recognition()

    print(all_results)

    telefoongesprek = str(all_results)
    filename = f"C:\\Users\\Beau\\Contact-verkeer\\contact-verkeer\\telefoon\\STT Transcriptions\\Telefoongesprek#{oid}.txt"
    with open(filename, "w", encoding="utf-8") as file:
        file.write(telefoongesprek)

databaseName = '*'
username = '*'
password = '*'
server = '*'
driver = '*'

try:
    CONNECTION_STRING = 'DRIVER='+driver+';SERVER='+server+';DATABASE='+databaseName+';UID='+username+';PWD='+ password
    conn = pyodbc.connect(CONNECTION_STRING)
    cursor = conn.cursor()
    storedproc = "* = *'"
    cursor.execute(storedproc)
    
    # 简化循环逻辑，直接遍历所有行
    for row in cursor.fetchall():
        OID = int(row[1])
        print(f"正在处理OID：{OID}")
        
        # 计算哈希
        string = f"{OID}*"
        encoded = string.encode()
        result = hashlib.sha256(encoded)
        resultHash = result.hexdigest()
        
        # 下载音频并校验响应
        Telefoongesprek = requests.get(f"*{OID}", headers={"api-key": resultHash})
        # 校验请求是否成功，且返回内容是音频
        Telefoongesprek.raise_for_status()
        if not Telefoongesprek.content or len(Telefoongesprek.content) < 1024:
            print(f"OID {OID} 音频文件无效，跳过处理")
            continue
        
        # 写入临时文件
        src = "Telefoongesprek.mp3"
        dst = "Telefoongesprek.wav"
        with open(src, "wb") as f:
            f.write(Telefoongesprek.content)
        
        # 转格式
        sound = AudioSegment.from_file(src)
        sound.export(dst, format="wav")
        
        # 调用识别函数
        speech_recognize_continuous_from_file(OID)
        
        # 清理临时文件，避免旧文件干扰下一次处理
        remove(src)
        remove(dst)
    
    # 所有行处理完再关闭数据库连接
    cursor.close()
    conn.close()
    print("所有通话处理完成")

except Exception as e:
    print("Error: %s" % e)

额外优化说明 #

• 临时文件处理：每次处理完一个OID就删除临时的MP3、WAV文件，避免上一次的残留文件导致本次处理出错
• 响应校验：新增raise_for_status()自动判断接口请求是否出错，同时校验文件大小避免空文件转码
• 循环简化：直接用for循环遍历cursor.fetchall()返回的所有行，不需要手动维护while游标，代码更简洁
• 文件写入改用with语法，自动关闭文件句柄，避免资源泄漏

内容的提问来源于stack exchange，提问作者Beau