SQL SERVER - "Invalid column name '0'-CodePudding

This is the query I am sending:

 SELECT "*" FROM "tkscale" WHERE "UfText1" IN ("12","23","45","0");

But I am getting the following error.

Error (pymssql._pymssql.ProgrammingError) (207, b"Invalid column name '0'.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")

I create the query like this:

table="tkscale",
cols="*",
where="UfText1",
ids=[12, 23, 45, 0]

SELECT_QUERY = "SELECT {cols} FROM {table} WHERE {where} IN ({ids});"
query = SELECT_QUERY.format(
        table='"{}"'.format(table),
        where='"{}"'.format(where),
        ids=",".join(['"{}"'.format(id) for id in ids]), 
        cols=",".join(['"{}"'.format(col) for col in cols]), 
    )

What am I doing wrong?

CodePudding user response：

None of those things should be "wrapped" "in" "double" "quotes." Perhaps try something like this:

query = SELECT_QUERY.format(
        table='{}'.format(table),
        where='{}'.format(where),
        ids=",".join(['{}'.format(id) for id in ids]), 
        cols=",".join(['{}'.format(col) for col in cols]), 
    )

Or simpler as, uh, TeddyBearSuicide suggested:

query = SELECT_QUERY.format(
        table=table,
        where=where,
        ids=",".join(['{}'.format(id) for id in ids]), 
        cols=",".join(['{}'.format(col) for col in cols]), 
    )