如何在Flask/Python中动态从SQL表product字段查询数据

Got it, let's tackle this dynamic product query problem you're facing. First, let's clear up why your current approach isn't working, then walk through a secure, flexible solution.

The Problem with Your Current Attempt #

Your example cursor.execute("select * from books where product like ('Balls','Pen')") isn't valid SQL—LIKE only accepts a single match pattern, not a list of values. Additionally, using LIKE 'Books' won't properly match rows where "Books" is part of a comma-separated list (like row 3), which is why you need a better way to check for values in that string.

Recommended Solution: Use FIND_IN_SET (MySQL-Specific) #

If you're using MySQL, the FIND_IN_SET() function is perfect for this scenario—it checks if a value exists within a comma-separated string. It returns a positive number if the value is found, 0 otherwise.

For your use case:

• To find rows containing all specified products (e.g., both "Balls" and "Pen"), we'll generate a dynamic list of FIND_IN_SET() conditions joined with AND.
• This ensures only rows that include every requested product are returned.

Dynamic Query Implementation #

Here's how to build the query dynamically while keeping it safe from SQL injection (using parameterized queries):

Step 1: Handle Variable Product Conditions #

Suppose you have a list of target products (e.g., target_products = ["Books"] or target_products = ["Balls", "Pen"]):

target_products = ["Balls", "Pen"]  # Example input
cursor = conn.cursor()  # Assume your DB connection is already set up

# Build dynamic conditions
conditions = []
params = []

if target_products:
    # Add a FIND_IN_SET condition for each product
    conditions = ["FIND_IN_SET(%s, product) > 0" for _ in target_products]
    params.extend(target_products)

# Build the final SQL query
if conditions:
    sql = f"SELECT * FROM books WHERE {' AND '.join(conditions)}"
    cursor.execute(sql, params)
    results = cursor.fetchall()
else:
    # No product conditions? Fetch all rows
    cursor.execute("SELECT * FROM books")
    results = cursor.fetchall()

Step 2: Combine with ID Query (If Needed) #

To keep your existing ID query functionality while adding dynamic product filters, just merge the conditions:

id = 3  # Example ID input
target_products = ["Balls", "Pen"]

conditions = []
params = []

# Add ID condition if provided
if id:
    conditions.append("id = %s")
    params.append(id)

# Add product conditions if provided
if target_products:
    product_conds = ["FIND_IN_SET(%s, product) > 0" for _ in target_products]
    conditions.extend(product_conds)
    params.extend(target_products)

# Execute query
if conditions:
    sql = f"SELECT * FROM books WHERE {' AND '.join(conditions)}"
    cursor.execute(sql, params)
else:
    cursor.execute("SELECT * FROM books")
results = cursor.fetchall()

Important Notes #

• Consistent Formatting: Make sure your product field uses a consistent format (no extra spaces after commas). If there are spaces (e.g., "Balls, Books, Pen"), modify the query to remove them first:

  product_conds = ["FIND_IN_SET(%s, REPLACE(product, ' ', '')) > 0" for _ in target_products]
  

• Database Compatibility: FIND_IN_SET is MySQL-specific. If you're using a different database (like PostgreSQL), you'd use string functions such as string_to_array and @> instead.

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