Integrating Python Barcode Scanners with a Database

Connecting your scanner to a database transforms a simple visual tool into a functional Inventory Management or Attendance System. In this walkthrough, we will use SQLite because it is built into Python, requires no external server setup, and is perfect for edge devices like a Raspberry Pi or a local laptop.

🗄️ Integrating Python Barcode Scanners with a Database

To make our scanner useful, it needs a memory. We will implement a system that detects a code, checks if it's already been scanned during the current session to avoid duplicates, and then logs the data with a precise timestamp.

1. Setting up the Database Schema

First, we need to initialize a database file and a table to store our entries. We will store the content of the code, the type (QR vs Barcode), and the timestamp.

import sqlite3
from datetime import datetime

def init_db():
    conn = sqlite3.connect("inventory.db")
    cursor = conn.cursor()
    # Create table if it doesn't exist
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS scans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code_data TEXT,
            code_type TEXT,
            scan_time DATETIME
        )
    """)
    conn.commit()
    return conn

---

2. Implementing "Anti-Duplicate" Logic

In a real-time video feed, the scanner sees the same barcode 30 times per second. Without a filter, your database would fill up with thousands of identical entries.

We use a Python Set in memory to keep track of what we've seen in the current session.

• New Scan: Add to set and save to database.
• Existing Scan: Draw the box on screen but ignore the database write.

---

3. The Full Technical Implementation

This script combines the SQLite database logic with the real-time OpenCV scanner.

import cv2
from pyzbar import pyzbar
import sqlite3
from datetime import datetime

def live_scanner_with_db():
    # 1. Initialize Database
    conn = sqlite3.connect("inventory.db")
    cursor = conn.cursor()
    
    # 2. Local session memory to prevent spamming the DB
    found_codes = set()
    
    cap = cv2.VideoCapture(0)
    print("Scanner Active. Recording to inventory.db...")

    while True:
        ret, frame = cap.read()
        if not ret: break

        for obj in pyzbar.decode(frame):
            data = obj.data.decode("utf-8")
            type = obj.type
            
            # Draw visual feedback (always)
            (x, y, w, h) = obj.rect
            cv2.rectangle(frame, (x, y), (x + w, y + h), (255, 0, 0), 2)

            # Database Logic: Only save if it's a "new" scan for this session
            if data not in found_codes:
                timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                
                # Insert into SQLite
                cursor.execute("INSERT INTO scans (code_data, code_type, scan_time) VALUES (?, ?, ?)",
                               (data, type, timestamp))
                conn.commit()
                
                # Update memory
                found_codes.add(data)
                print(f" [SAVED] {type}: {data}")

            # Label the barcode on the video feed
            cv2.putText(frame, f"{data} (LOGGED)", (x, y - 10), 
                        cv2.FONT_HERSHEY_SIMPLEX, 0.5, (255, 0, 0), 2)

        cv2.imshow("Inventory Scanner", frame)
        if cv2.waitKey(1) & 0xFF == ord('q'): break

    cap.release()
    conn.close()
    cv2.destroyAllWindows()

# init_db() # Run this once first
# live_scanner_with_db()

---

4. Why This Matters for Production

Feature	Technical Benefit
Persistence	Data survives even if the script crashes or the computer restarts.
Querying	You can easily run SQL commands to see "How many items were scanned between 9 AM and 10 AM?"
Scalability	While we used SQLite, the code structure is 90% identical for PostgreSQL or MySQL if you move to a multi-user environment.

📚 Sources & References

• [1.1] Python SQLite3 Documentation: Official Library Reference - Standard for local data persistence.
• [1.2] Real Python: Data Management with SQLite - Best practices for handling connections and commits.
• [1.3] PyImageSearch: Building a Barcode Scanner with Database Integration - Conceptual guide for the "Anti-Spam" Set logic [4.5].