Python存储数据至数据库并在Node.js中检索及视频标签标注技术问询

Hey there! Let's tackle your two questions one by one, step by step.

Python 端：将数据存储到数据库 #

Let's use MySQL as an example (the logic is similar for SQLite/PostgreSQL—only the connection string and dependency package differ):

1. First, install the required dependency:

pip install mysql-connector-python

2. Complete code example (includes connection, table creation, and data insertion):

import mysql.connector
from mysql.connector import Error

def store_data_to_db():
    try:
        # Establish database connection
        connection = mysql.connector.connect(
            host='localhost',
            database='your_database_name',
            user='your_username',
            password='your_password'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            
            # Create sample table (if it doesn't exist)
            create_table_query = """
            CREATE TABLE IF NOT EXISTS user_data (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """
            cursor.execute(create_table_query)
            
            # Insert sample data
            insert_query = """
            INSERT INTO user_data (name, email)
            VALUES (%s, %s)
            """
            data = ("John Doe", "john@example.com")
            cursor.execute(insert_query, data)
            connection.commit()
            print(f"Data inserted successfully, ID: {cursor.lastrowid}")

    except Error as e:
        print(f"Database operation error: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("Database connection closed")

if __name__ == "__main__":
    store_data_to_db()

Node.js 端：Implement Data Retrieval #

We'll use the mysql2 library for this:

1. Initialize the project and install dependencies:

mkdir node-db-retrieve && cd node-db-retrieve
npm init -y
npm install mysql2

2. Complete retrieval code example:

const mysql = require('mysql2/promise');

async function retrieveDataFromDb() {
    let connection;
    try {
        // Establish database connection
        connection = await mysql.createConnection({
            host: 'localhost',
            database: 'your_database_name',
            user: 'your_username',
            password: 'your_password'
        });

        // Execute query
        const [rows] = await connection.execute('SELECT * FROM user_data');
        console.log("Retrieved data:");
        console.log(rows);

    } catch (error) {
        console.error("Database retrieval error:", error);
    } finally {
        if (connection) {
            await connection.end();
            console.log("Database connection closed");
        }
    }
}

retrieveDataFromDb();

Prerequisites #

Before getting started, you need to:

• Enable the Google Cloud Video Intelligence API (search for it in the GCP Console to enable)
• Install the Google Cloud SDK and set up authentication credentials (run gcloud auth application-default login for local authentication)
• Install the required dependency:

pip install google-cloud-videointelligence

Complete Code Implementation #

I've completed your code with result parsing and label extraction logic:

import argparse
from google.cloud import videointelligence

def analyze_labels(path):
    """ Detects labels given a GCS path or local file path. """
    video_client = videointelligence.VideoIntelligenceServiceClient()
    features = [videointelligence.Feature.LABEL_DETECTION]
    
    # Supports GCS paths (gs://bucket-name/video.mp4) or local file paths
    operation = video_client.annotate_video(
        request={"input_uri": path, "features": features}
    )
    print('\nProcessing video for label annotations:')

    # Wait for the operation to complete
    result = operation.result(timeout=90)

    # Extract segment-level labels (covers entire video or large segments)
    segment_labels = result.annotation_results[0].segment_label_annotations
    print('\nSegment level labels:')
    for i, segment_label in enumerate(segment_labels):
        print(f'Label {i+1}: {segment_label.entity.description}')
        print(f'Confidence: {segment_label.segments[0].confidence:.2f}')
        
        # Print sub-labels (if any)
        for entity in segment_label.entities:
            if entity != segment_label.entity:
                print(f'  Sub-label: {entity.description}, Confidence: {entity.confidence:.2f}')

    # Extract frame-level labels (appear at specific timestamps)
    frame_labels = result.annotation_results[0].frame_label_annotations
    print('\nFrame level labels:')
    for i, frame_label in enumerate(frame_labels):
        print(f'Label {i+1}: {frame_label.entity.description}')
        for frame in frame_label.frames:
            time_offset = frame.time_offset.total_seconds()
            print(f'  At {time_offset:.2f}s, Confidence: {frame.confidence:.2f}')

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description='Detect labels in a video.')
    parser.add_argument('path', help='GCS path or local file path to the video.')
    args = parser.parse_args()
    analyze_labels(args.path)

Usage Instructions #

1. Run the code:

   • For a video stored in GCS:

   python video_labeler.py gs://your-bucket/your-video.mp4
   

   • For a local video: Note that local files need to be uploaded to GCS, or you can modify the code to use the input_content parameter to pass binary data (the code above defaults to GCS paths).

2. Result Explanation:

   • The code outputs segment-level labels (tags covering the entire video or large segments) and frame-level labels (tags appearing at specific timestamps). Each label includes a confidence score (0-1, closer to 1 means more accurate).

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