QR/数据矩阵码扫描程序问题求助：数据库异常与停止按钮失效

问题1：「Unread result found」异常 #

原因 #

mysql.connector要求每个游标执行查询后必须读取完整的结果集，否则后续使用同一连接的操作会抛出该异常。你的代码中以下场景未正确处理结果集：

• create_table_if_not_exists方法中执行SELECT 1后未读取结果
• 部分数据库操作的游标未确保结果集被完全消费

解决方法 #

1. 完善create_table_if_not_exists方法，执行查询后读取结果集：

def create_table_if_not_exists(self):
    try:
        cursor = self.conn.cursor()
        cursor.execute(f"SELECT 1 FROM {self.table_name} LIMIT 1")
        cursor.fetchall()  # 消费结果集
        cursor.close()
    except mysql.connector.Error as err:
        self.create_table()
    else:
        print(f"Table '{self.table_name}' already exists.")

2. 确保所有数据库操作的游标在使用后及时关闭，且结果集被完全读取（比如通过fetchall()）。

问题2：「Stop scanning」按钮无法直接停止采集 #

原因 #

扫描逻辑在主线程中执行，cv2.waitKey(1)会阻塞tkinter的主事件循环，导致按钮点击事件无法及时响应，循环条件的状态也无法实时更新。

解决方法 #

将扫描逻辑放到独立线程中运行，避免阻塞主事件循环，同时调整循环依赖的状态变量：

1. 导入threading模块
2. 修改start_scanning函数，启动线程执行扫描
3. 调整start方法的循环条件，仅依赖全局变量scanning
4. 通过tkinter的after方法在主线程更新UI，避免线程安全问题

完整修改后的代码 #

import cv2
import json
import mysql.connector
from tkinter import *
from tkinter import messagebox
from tkinter import ttk
from tkinter import font
import pyzbar.pyzbar as pyzbar
import pylibdmtx.pylibdmtx as pylibdmtx
import threading  # 新增导入

class QRCodeScanner:
    def __init__(self, conn):
        self.conn = conn
        self.decoder = cv2.QRCodeDetector()
        self.table_name = 'qr_codes2'
        self.create_table_if_not_exists()
        
    def create_table_if_not_exists(self):
        try:
            cursor = self.conn.cursor()
            cursor.execute(f"SELECT 1 FROM {self.table_name} LIMIT 1")
            cursor.fetchall()  # 消费结果集
            cursor.close()
        except mysql.connector.Error as err:
            self.create_table()
        else:
            print(f"Table '{self.table_name}' already exists.")

    def create_table(self):
        cursor = self.conn.cursor()
        query = f"""
        CREATE TABLE {self.table_name} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            data LONGTEXT NOT NULL
        )
        """
        cursor.execute(query)
        self.conn.commit()
        print(f"Table '{self.table_name}' created successfully.")
        cursor.close()

    def start(self):
        cap = cv2.VideoCapture(0, cv2.CAP_DSHOW)

        if not cap.isOpened():
            window.after(0, lambda: messagebox.showerror("Error", "The webcam is not connected."))
            print("The webcam is not connected.")
            return

        while scanning:
            ret, frame = cap.read()

            if not ret:
                window.after(0, lambda: messagebox.showerror("Error", "The webcam is not working properly."))
                print("The webcam is not working properly.")
                break

            decoded_objects_qr = pyzbar.decode(frame)
            decoded_objects_dm = pylibdmtx.decode(frame)

            for decoded_object in decoded_objects_qr:
                data = decoded_object.data.decode()
                barcode_type = decoded_object.type

                print(barcode_type)
                
                if barcode_type == "QRCODE":
                    print("QR code detected:", data)
                else:
                    print("Unknown barcode type detected:", barcode_type)

                json_data = json.dumps(data)

                if json_data and json_data != '''""''':
                    try:
                        cursor = self.conn.cursor(buffered=True)
                        cursor.execute(f"INSERT INTO {self.table_name} (data) VALUES (%s)", (json_data,))
                        self.conn.commit()
                        print("Barcode data inserted into the database.")
                        retrieve_records()
                        cursor.close()
                    except mysql.connector.Error as err:
                        print(f"An error occurred while inserting the data: {err}")
                        self.conn.rollback()
                else:
                    print("Empty barcode data. Skipped inserting into the database.")

            for decoded_object in decoded_objects_dm:
                data = decoded_object.data.decode()

                print("2D data matrix code detected:", data)

                json_data = json.dumps(data)

                if json_data and json_data != '''""''':
                    try:
                        cursor = self.conn.cursor(buffered=True)
                        cursor.execute(f"INSERT INTO {self.table_name} (data) VALUES (%s)", (json_data,))
                        self.conn.commit()
                        print("2D data matrix data inserted into the database.")
                        retrieve_records()
                        cursor.close()
                    except mysql.connector.Error as err:
                        print(f"An error occurred while inserting the data: {err}")
                        self.conn.rollback()
                else:
                    print("Empty 2D data matrix data. Skipped inserting into the database.")

            cv2.imshow("Barcode Scanner", frame)

            key = cv2.waitKey(1)
            if key == ord('q'):
                global scanning
                scanning = False
                break

        cap.release()
        cv2.destroyAllWindows()
        # 线程结束后更新按钮状态
        window.after(0, lambda: [
            start_button.configure(state=NORMAL),
            stop_button.configure(state=DISABLED)
        ])

    def close(self):
        self.conn.close()


def start_scanning():
    global scanning
    scanning = True
    start_button.configure(state=DISABLED)
    stop_button.configure(state=NORMAL)
    # 启动扫描线程
    threading.Thread(target=scanner.start, daemon=True).start()

def stop_scanning():
    global scanning
    scanning = False
    # 直接更新按钮状态，线程结束后会再次确认，避免状态不一致
    start_button.configure(state=NORMAL)
    stop_button.configure(state=DISABLED)

def retrieve_records():
    def update_tree():
        try:
            cursor = conn.cursor(buffered=True)
            cursor.execute(f"SELECT * FROM {scanner.table_name}")
            rows = cursor.fetchall()
            cursor.close()

            tree.delete(*tree.get_children())
            for row in rows:
                tree.insert("", "end", values=row)
        except mysql.connector.Error as err:
            messagebox.showerror("Error", f"An error occurred while retrieving the records: {err}")
    # 通过after在主线程更新UI，避免线程安全问题
    window.after(0, update_tree)

def search_records():
    def update_search_result():
        try:
            search_term = search_entry.get().strip()
            if search_term:
                cursor = conn.cursor(buffered=True)
                query = f"SELECT * FROM {scanner.table_name} WHERE data LIKE %s"
                cursor.execute(query, (f"%{search_term}%",))
                records = cursor.fetchall()
                cursor.close()

                tree.delete(*tree.get_children())
                for record in records:
                    tree.insert("", "end", values=record)
            else:
                retrieve_records()
        except mysql.connector.Error as err:
            messagebox.showerror("Error", f"An error occurred while searching the records: {err}")
    window.after(0, update_search_result)

def on_closing():
    global scanning
    scanning = False
    scanner.close()
    window.destroy()
 
# Function to sort the treeview records
def sort_records(column):
    records = tree.get_children("")
    records.sort(key=lambda item: tree.set(item, column))
    for index, item in enumerate(records):
        tree.move(item, '', index)

try:
    config = {
        'user': 'mukesh',
        'password': 'Mukesh@123',
        'host': 'localhost',
        'database': 'qr_demo',
        'raise_on_warnings': True
    }
    conn = mysql.connector.connect(**config)
    print("Connected to the database.")
    
except mysql.connector.Error as err:
    print(f"An error occurred while creating the database connection: {err}")
    raise

scanner = QRCodeScanner(conn)

window = Tk()
window.title("QR Code Scanner")
window.geometry("800x600")
window.protocol("WM_DELETE_WINDOW", on_closing)

# Configure styles
style = ttk.Style()
style.configure("Treeview", font=('Arial', 10))
style.configure("TButton", font=('Arial', 12))
style.configure("TLabel", font=('Arial', 12))

# Create a frame for the buttons
button_frame = Frame(window)
button_frame.pack(side=TOP, padx=1, pady=10)

start_button = Button(button_frame, text="Start Scanning", command=start_scanning)
start_button.pack(side=LEFT, padx=5, pady=5)

stop_button = Button(button_frame, text="Stop Scanning", command=stop_scanning, state=DISABLED)
stop_button.pack(side=LEFT, padx=(5, 5), pady=5)

search_label = Label(button_frame, text="Search:", font=('Arial', 12))
search_label.pack(side=LEFT, padx=(20, 5), pady=5)

search_entry = Entry(button_frame, font=('Arial', 12))
search_entry.pack(side=LEFT, padx=5, pady=5)

search_button = Button(button_frame, text="Search", command=search_records, font=('Arial', 12))
search_button.pack(side=LEFT, padx=5,  pady=5)

# Create a frame for the treeview
tree_frame = Frame(window)
tree_frame.pack(fill=BOTH, expand=True, padx=10, pady=10)

tree = ttk.Treeview(tree_frame, columns=('ID', 'Data'), show='headings')
tree.column('ID', width=100, anchor="center")
tree.column('Data', width=600, anchor="w")
tree.heading('ID', text='ID', anchor="center", command=lambda: sort_records("ID"))
tree.heading('Data', text='Data', anchor="w", command=lambda: sort_records("Data"))
tree.pack(side=LEFT, padx=5, pady=5, fill=BOTH, expand=True)

tree_scroll = Scrollbar(tree_frame)
tree_scroll.pack(side=LEFT, fill=Y)

tree.configure(yscrollcommand=tree_scroll.set)
tree_scroll.configure(command=tree.yview)

# Apply a custom font to the treeview headers
font_style = font.Font(family="Arial", size=12, weight="bold")
tree.tag_configure("custom_font", font=font_style)

for column in tree["columns"]:
    tree.heading(column, text=column, anchor="center")

retrieve_records()
window.mainloop()

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