Process Images with OpenALPR and SQLite3 Database

Understanding the Problem and Requirements

As a Python developer, we often encounter scenarios where we need to process images or other data sources and then store the results in a database. In this case, we are given an example of how to use OpenALPR to perform Automatic License Plate Recognition (ALPR) on images stored in a database. However, we want to take it a step further by incorporating the result of the console output into our database.

Background and Prerequisites

To understand the code provided, we need to be familiar with some Python libraries and concepts:

OpenALPR: A library for Automatic License Plate Recognition (ALPR). It’s used to recognize license plates in images.
NumPy: A library for efficient numerical computation. We use it to manipulate arrays of data.
CV2: The OpenCV library, which provides functions for computer vision tasks such as image processing and feature detection.
SQLite3: A self-contained, file-based database that’s perfect for small projects or prototyping.

The code also makes use of some system-level commands to execute SQL queries and interact with the database.

Problem Statement

We need to find a way to print the results of the console output (i.e., the license plate number and its confidence score) in our database. This means we want to take the output from OpenALPR, which is currently being printed on the console, and store it in a database table.

Solution Overview

To solve this problem, we need to modify our existing code to include the results of the console output into our SQL queries. Here’s an overview of how we can achieve this:

We’ll use Python’s built-in logging module to redirect the print statements from OpenALPR to a string variable.
We’ll create a new table in our database to store the license plate number and confidence score, along with other relevant information.
We’ll modify our SQL queries to insert data into this new table when we process an image.

Step-by-Step Solution

Redirect Console Output to a String Variable

To capture the console output from OpenALPR, we can use Python’s built-in logging module. Specifically, we’ll create a logger with a name that corresponds to our script, and then set its level to “DEBUG”. We’ll also specify a handler that captures the output from our console.

# Create a logger
logger = logging.getLogger('speed_camera')
logger.setLevel(logging.DEBUG)

# Create a file handler and set its level to DEBUG
file_handler = logging.FileHandler('/home/pi/speed-camera/results.log')
file_handler.setLevel(logging.DEBUG)

# Create a formatter and attach it to the file handler
formatter = logging.Formatter('%(message)s')
file_handler.setFormatter(formatter)

# Add the file handler to our logger
logger.addHandler(file_handler)

Next, we’ll use this logger to capture the output from OpenALPR.

for i, plate in enumerate(results['results']):
    best_candidate = plate['candidates'][0]
    print('Plate #{}: {:7s} ({:.2f}%)'.format(i, best_candidate['plate'].upper(), 
                   best_candidate['confidence']))
    logger.debug(f'Plate #{}: {best_candidate["plate"]} ({best_candidate["confidence"]})')

Create a New Table in the Database

To store our results in the database, we’ll create a new table to hold the license plate number and confidence score. We can do this by running an SQL query that creates a new column in our existing speed table.

sql_cmd = '''ALTER TABLE speed ADD COLUMN alpr_plate TEXT'''
db_conn.execute(sql_cmd)

Modify SQL Queries to Insert Data into New Table

Now, we’ll modify our SQL queries to insert data into this new table when we process an image. We can do this by adding a new column to our SELECT clause.

cursor.execute("SELECT idx, image_path, alpr_plate FROM speed WHERE status=''")
while True:
    row = cursor.fetchone()
    if row is None:
        break
    row_index = (row["idx"])
    row_path = (row["image_path"])
    # ...
    results = alpr.recognize_ndarray(speed_image)
    for i, plate in enumerate(results['results']):
        best_candidate = plate['candidates'][0]
        print('Plate #{}: {:7s} ({:.2f}%)'.format(i, best_candidate['plate'].upper(), 
                   best_candidate['confidence']))
        logger.debug(f'Plate #{}: {best_candidate["plate"]} ({best_candidate["confidence"]})')
        alpr_plate = f'{best_candidate["plate"]} ({best_candidate["confidence"]})'
    sql_cmd = '''UPDATE speed SET alpr_plate="{}" WHERE idx="{}"'''.format(alpr_plate, row_index)
    db_conn.execute(sql_cmd)

Example Use Case

Here’s an example of how we can use this modified code to process images and store the results in our database:

import sqlite3
import logging

# Create a logger
logger = logging.getLogger('speed_camera')
logger.setLevel(logging.DEBUG)

# Create a file handler and set its level to DEBUG
file_handler = logging.FileHandler('/home/pi/speed-camera/results.log')
file_handler.setLevel(logging.DEBUG)

# Create a formatter and attach it to the file handler
formatter = logging.Formatter('%(message)s')
file_handler.setFormatter(formatter)

# Add the file handler to our logger
logger.addHandler(file_handler)

# Connect to the database
db_conn = sqlite3.connect('/home/pi/speed-camera/database.db')

# Create a cursor object
cursor = db_conn.cursor()

# Create a new table in the database
sql_cmd = '''CREATE TABLE alpr_results (idx TEXT, image_path TEXT, alpr_plate TEXT)'''
cursor.execute(sql_cmd)

# Process images and store results in the database
while True:
    row = cursor.fetchone()
    if row is None:
        break
    row_index = (row["idx"])
    row_path = (row["image_path"])
    # ...

    # Run OpenALPR on the image
    alpr_results = []
    for i, plate in enumerate(results['results']):
        best_candidate = plate['candidates'][0]
        print('Plate #{}: {:7s} ({:.2f}%)'.format(i, best_candidate['plate'].upper(), 
                   best_candidate['confidence']))
        logger.debug(f'Plate #{}: {best_candidate["plate"]} ({best_candidate["confidence"]})')
        alpr_plate = f'{best_candidate["plate"]} ({best_candidate["confidence"]})'
        alpr_results.append(alpr_plate)

    # Insert data into the database
    for result in alpr_results:
        sql_cmd = '''INSERT INTO alpr_results (idx, image_path, alpr_plate) VALUES (?, ?, ?)'''
        cursor.execute(sql_cmd, (row_index, row_path, result))

# Close the database connection
db_conn.close()

# Remove the file handler from our logger
logger.removeHandler(file_handler)

This code demonstrates how we can modify our existing code to include the results of the console output in our SQL queries. By using a logger to capture the output and a new table in the database, we can store this information for future reference or analysis.

Last modified on 2024-08-29