CODEWITHSUNDEEP
pythonmysqlfastapipymysql
biccs
biccs

Reputation: 137

pymysql.err.OperationalError: 1054. "Unknown column 'X' in 'where clause'

I'm getting the following error in console, where the column name actually is the value passed through the query:

pymysql.err.OperationalError: (1054, "Unknown column 'LiqNac83437' in 'where clause'")

This is my function:

sql = f"""
            SELECT 
                detallev.clave,
                detallev.cantidad,
                venta.fecha
            FROM
                detallev
                    INNER JOIN
                venta ON detallev.ven_id = venta.ven_id
            WHERE
                clave = '{codigoBarras}'
            AND (fecha BETWEEN {fecha_inicio} AND {fecha_final});"""
    print(sql)
    with bd:
        with bd.cursor() as cursor:
            cursor.execute(sql)
            resultado = cursor.fetchall()
            cursor.close()

which is called by:

@app.get('/{sucursal}/reporte/articulos/')
def reporte_articulo(sucursal: Origenes, clave: str = '', fecha_inicial: str = '', fecha_final: str = fechaHoy(), username: str = Depends(valida_usuario)):
    return reporte_articulos.reporte_articulo(sucursal, clave, fecha_inicial, fecha_final)

I'm using FastAPI, python and Mysql. I've already tried following these solutions with no luck:

Solution 1 Solution 2

and several other solutions outside stackoverflow, already tried wrapping the concatenated value in different type of ways.

Upvotes: -1

Views: 1865

Answers (2)

Aljadeed
Aljadeed

Reputation: 11

I faced the similar error in ERPNext 15:

pymysql.err.OperationalError: (1054, "Unknown column 'tabLoan.repay_from salary' in 'where clause'") Possible source of error: hrms (app)

Also this error:

AttributeError: 'Loan' object has no attribute 'repay_from_salary' Possible source of error: hrms (app)

Then I tried the following commands, after which it worked for both errors:

Command 1

bench execute hrms.setup.after_install

I get this output:

Patching Existing Data...
rename_field: kra_title not found in table for: Appraisal Template
rename_field: kra_template not found in table for: Appraisal

Command 2

bench migrate

Upvotes: 0

Tim Roberts
Tim Roberts

Reputation: 54698

This happens because you are substituting the values yourself, and in this case you have not properly quotes the fields in the BETWEEN clause. It sees LiqNac83437 and thinks it is a column name, because it is not quoted.

For this reason, and to avoid SQL injection problems, you should let the database connector do the quoting:

    sql = """
            SELECT 
                detallev.clave,
                detallev.cantidad,
                venta.fecha
            FROM
                detallev
                    INNER JOIN
                venta ON detallev.ven_id = venta.ven_id
            WHERE
                clave = ?
            AND fecha BETWEEN ? AND ?;"""
    with bd.cursor() as cursor:
        cursor.execute(sql, (codigoBarras, fecha_inicio, fecha_final))
        resultado = cursor.fetchall()
        cursor.close()

Upvotes: 1

Related Questions