Consulta SQLite con WHERE configurable por el usuario

[Diego]

Buenas, vuelco aquí esta consulta porque no sé dónde ubicarla.

Esta es una típica sentencia de búsqueda escrita en python:

Código:

    cursor = base.cursor()

    data = (estado, )
    sql = "SELECT id, título, autor, editorial, isbn, pvp FROM ejemplares WHERE estado = ? ORDER BY id"
    cursor.execute(sql, data)
   
    for fila in cursor:
        tree.insert("", "end", text=fila[0], values=(fila[1], fila[2], fila[3], fila[4], fila[5]))

Funciona de maravillas.
Pero estoy intentando que el usuario pueda elegir el contenido de data (o sea que pueda elegir sobre qué campos se realizará la consulta).

He intentado de muchas maneras, sin lograrlo. La última es la siguiente:

Código:

    cursor = base.cursor()

    datos=["","","","",""]
    if prod:
        datos[0] =prod
    if estado:
        datos[1] = estado
    if tipo:
        datos[2] = tipo
    if gen:
        datos[3] = gen
    if sub_gen:
        datos[4] = sub_gen
    print("print de datos:", datos)
    print("tipo de datos",type(datos))
    data = tuple(datos)
    print("print de data:",data)
    print("tipo de data",type(datos))
    sql = "SELECT id, título, autor, editorial, isbn, pvp FROM ejemplares WHERE producto = ? AND estado = ? AND categoría = ? AND género = ? AND subgénero = ? ORDER BY id"
    cursor.execute(sql, data)
    dato = cursor.fetchall()

Pero me dí cuenta (haciendo un print a la línea "dato = cursor.fetchall()" (o sea, haciendo un print(dato)) que la consulta no devuelve nada, con lo cual infiero que la línea " cursor.execute(sql, data) " no le está pasando a la sentencia sql los datos.

Quizás no es la manera de hacerlo, esto fué sólo un intento de mi parte.
¿Cómo podría hacerse?

[Francisco]

Hola, Diego.

El signo de interrogación solo se puede usar para valores, no para nombre de campos. O sea, se puede hacer WHERE campo = ?, pero no WHERE ? = valor. Por lo tanto, para que el usuario pueda elegir el campo por el cual filtrar, hay que usar la sintaxis tradicional de Python, algo así:

Código:

# Pseudocódigo
campo = input("Ingrese el nombre del campo por el que quiere filtrar: ")
valor = input("Ingrese el valor: ")
cursor.execute(f"SELECT ... FROM ... WHERE {campo}=?", (valor,))

En este caso hay que tener cuidado con la inyección de código SQL. Lo ideal sería que el usuario solo pueda elegir el campo dentro de una lista predefinida de campos.

Esto último que intentaste:

Código:

sql = "SELECT id, título, autor, editorial, isbn, pvp FROM ejemplares WHERE producto = ? AND estado = ? AND categoría = ? AND género = ? AND subgénero = ? ORDER BY id"

Probablemente no funciona porque a casi todos los campos que están después del WHERE les estás pasando una cadena vacía, por lo cual SQLite te va a querer devolver únicamente las filas que en esos campos tengan valores nulos.

Saludos

[Diego]

"Esto último que intentaste:

sql = "SELECT id, título, autor, editorial, isbn, pvp FROM ejemplares WHERE producto = ? AND estado = ? AND categoría = ? AND género = ? AND subgénero = ? ORDER BY id"

Probablemente no funciona porque a casi todos los campos que están después del WHERE les estás pasando una cadena vacía, por lo cual SQLite te va a querer devolver únicamente las filas que en esos campos tengan valores nulos. "

Exacto, Francisco. Si yo completo todos los campos de búsqueda, me arroja resultados, pero cuando dejo alguno sin completar, no arroja nada porque ese que quedó sin completar pasa como "" y me rompe la consulta.

Me dijeron que tengo que crear una consulta dinámica en donde sólo pasen a la query los valores de aquellos campos que sí se seleccionaron para la búsqueda. También me dijeron que sería algo del tipo "f' SELECT id, título, autor, editorial, isbn, pvp FROM ejemplares WHERE {campo} = {valor_campo}" pero acá me choco con el problema del AND.  Como la búsqueda es combinada los criterios van a ser más de uno ("campo1" y "campo3", por ejemplo, o "campo2", "campo4" y "campo5" ) y ya ahí se me quema el cerebro intentando que los campos subsiguientes se inserten con el AND delante.

[Francisco]

Podés usar join() para unir los campos por los que quieras filtrar con un AND:

Código:

campos = ["campo1", "campo3", "campo5"]
valores = ["valor_campo1", "valor_campo3", "valor_campo5"]
where = " AND ".join(f"{campo} = ?" for campo in campos)
sql = "SELECT ... FROM ... WHERE " + where
print(sql)
cursor.execute(sql, valores)

El resultado del print te va a dar algo como esto:

Código:

SELECT ... FROM ... WHERE campo1 = ? AND campo3 = ? AND campo5 = ?

Saludos

[Diego]

¡Funcionó!!!!

Te paso cómo quedó.

Código:

    cursor = base.cursor()
   
    data=()
    datos=[]
    campos=[]
   
    if prod != "":
        datos.append(prod)
        campos.append("producto")
    if estado !="":
        datos.append(estado)
        campos.append("estado")
    if tipo != "":
        datos.append(tipo)
        campos.append("categoría")
    if gen != "":
        datos.append(gen)
        campos.append("género")
    if sub_gen != "":
        datos.append(sub_gen)
        campos.append("subgénero")
   
    data = tuple(datos)

    where = " AND ".join(f"{campo} = ?" for campo in campos)
    sql = "SELECT id, título, autor, editorial, isbn, pvp FROM ejemplares WHERE " + where

    cursor.execute(sql, data)

Muchísimas gracias, Francisco. Me tenía loco esto.

[Francisco]

Excelente!