如何实现Opticon OPN-2001-USB条码扫描数据直接导入Sqlite数据库

核心原理 #

Opticon OPN-2001默认是USB HID键盘楔设备，不需要额外安装驱动，扫描条码成功后，会自动将条码内容模拟成键盘输入，末尾默认携带回车符，完全不需要调用官方SDK或者用自带的wedge软件导出csv。

改造方案 #

你现有的Tkinter代码只要做少量修改即可实现自动扫描录入，完全替代手动输入：

1. 给数量输入框绑定回车事件，扫描条码后自带的回车会直接触发数据库更新逻辑，不需要手动点提交
2. 新增扫描触发按钮，点击后自动将焦点定位到输入框，避免选错输入位置

改造后完整代码 #

from tkinter import *
import sqlite3
from tkinter.ttk import *
import sys
from datetime import datetime

dateTimeObj = datetime.now()
timestampStr = dateTimeObj.strftime("%d-%b-%Y")

root = Tk()
root.geometry('380x280') # 稍微加高一点放新按钮
root.title("Balances")
root.config(bg="lightblue1")
style = Style()

OrderNo = StringVar()
Balance = StringVar()

with sqlite3.connect('Reflex Footwear.sql3') as conn:
    cursor = conn.cursor()
    my_data = cursor.execute( "SELECT OrderNo FROM PlanSandal WHERE Factory=?", ("RF1",))
    my_list = [r for r, in my_data]
    options = StringVar()
    options.set(my_list[0])


def update_clicking(event=None): # 加event参数支持绑定回车事件
    code = options.get()
    balance = Balance.get()
    # 加个简单校验避免空值提交
    if not balance or not balance.isdigit():
        return

    with sqlite3.connect('Reflex Footwear.sql3') as conn:
        cursor = conn.cursor()
        cursor.execute('UPDATE SandalProduction SET Clicking=Clicking-? WHERE Order2=?', [balance, code, ])
        cursor.execute('UPDATE SandalProduction SET Closing=Closing+? WHERE Order2=?', [balance, code, ])
        cursor.execute(r'INSERT INTO ProductionBreakdown (Factory,Date,Order2,Clicking) VALUES(?,?,?,?)', [
                       "RF1", timestampStr, code, balance])
        updated = cursor.rowcount
        cursor.close()
        conn.commit()
        # 提交后清空数量输入框，方便下一次扫描，不用关程序
        Balance.set("")
        # 如果需要提交后退出就保留下面两行，不需要就注释
        # root.destroy()
        # sys.exit(updated)

# 新增扫描触发函数：点击后自动聚焦到数量输入框
def start_scan():
    entry_2.focus_set()

# Inserting labels and field of input
label_0 = Label(root, text="Clicking Score", background="lightblue1",
                width=20, font=("Arial", 20, "bold")).place(x=80, y=23)

label_1 = Label(root, text="Order No.", width=20, background="lightblue1", font=(
    "Arial", 12, "bold")).place(x=20, y=100)
option_1 = OptionMenu(root, options, *my_list).place(x=180, y=100)

label_2 = Label(root, text="Quantity Clicked", width=20,
                background="lightblue1", font=("Arial", 12, "bold")).place(x=20, y=140)
entry_2 = Entry(root, textvar=Balance, background="lightblue1",
                font=("Arial", 12, "bold"))
entry_2.place(x=180, y=140)
# 绑定回车事件触发提交
entry_2.bind("<Return>", update_clicking)


# Style of buttons
style.configure('C.TButton', font=('Arial', 12, 'bold'), foreground='red')
style.configure('S.TButton', font=('Arial', 12, 'bold'), foreground='blue')
style.configure('G.TButton', font=('Arial', 12, 'bold'), foreground='green')

# Inserting buttons
scan_btn = Button(root, text='触发扫描', style='G.TButton',
                 width=11, command=start_scan).place(x=140, y=190)
submit1 = Button(root, text='手动提交', style='S.TButton',
                 width=11, command=update_clicking).place(x=20, y=190)
exit1 = Button(root, text='Close', style='C.TButton', width=11,
               command=root.destroy).place(x=260, y=190)

# 启动程序默认聚焦到数量输入框，打开就可以直接扫
entry_2.focus_set()

root.mainloop()

可选优化 #

如果你的订单号也打印了对应条码，可以把订单号的下拉选择框也改成输入框，扫描订单条码自动匹配对应订单，完全不用手动选择。

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