SQL String Substitution Error Not Enough Arguments For Format String

I'm trying to return a query to get all records that begin with string like a varibale i have so i do so : '''select name from pos_order where name like '%s'||'%' order by id DESC

Solution 1:

Python tries to substitute both '%' characters in your sql. But it only has one value - darsh[0] - to use. Hence the error message, it is trying to fill in two values, but you've only given it one.

To prove this, escape the second %%, making your statement

"""select name from pos_order where name like '%s'||'%%' order by id DESC limit 1"""%(darsh[0])

but Don't do this - it makes you vulnerable to SQL Injection. For example, if you had a function in your database called DO_BAD_THING a malicious user could make that function execute using a carefully crafted input string.

The correct answer is to use a bind variable, see this question :

question about postgresql bind variables

For an example of how to do this.

For emphasis - don't use string concatenation for SQL for anything where an end user can ever manipulate the string.

Solution 2:

It would be necessary to escape the % with another % like in %%

"""select name from pos_order where name like '%s'||'%%' order by id DESC limit 1"""%(darsh[0])

But that is bad practice as it opens the door to SQL injection. As you are using Psycopg use the cursor.method parameter passing:

cursor.execute("""
    select name 
    from pos_order 
    where name like %s||'%%' 
    order by id DESC 
    limit 1
    """, (darsh[0],)
)

The binding cited in the accepted answer is used for prepared statements which is not your case.