Syntax error executing query from python (postgres)

Question

I am getting syntax error in sql query statement stored in cur to store multiple list value

nam = ['sau','sing','loe','sta'] 
for d in nam:
 cur.execute("""INSERT INTO pages_post (pname) VALUES ({})""", .format(d))
 print(len(nam))'

getting error for the following statement

cur.execute("""INSERT INTO pages_post (pname) VALUES ({})""", .format(d))

the erroe message giving is

 File "C:\Users\surya\Desktop\whtscric\fe.py", line 12
 cur.execute("""INSERT INTO pages_post (pname) VALUES ({})""", .format(d))
 ^
SyntaxError: invalid syntax

• Mihai Chelaru: You should not use string formatting like this to add parameters to your queries, as it's vulnerable to SQL injection. A better practice is to follow the psycopg2 docs and pass variables as arguments to cur.execute.
• Bergi: Well, you're passing two arguments to execute - separated by a comma - and the second one is just .format(d), which is not a valid Python expression.

Answer 1

The psycopg2.extras.execute_values function can be used in inserting many values in the table.

import psycopg2.extras
psycopg2.extras.execute_values(
 cur, 
 "INSERT INTO pages_post (pname) VALUES %s", 
 ((n,) for n in nam)
)

Answer 2

You should take a look at the official docs for how to pass parameters to a query. They provide 2 methods.

Method 1: using a sequence

You can pass your parameters using a tuple or list, but a list may be less confusing because you don't have to remember the special case of a single element tuple.

nam = ['sau','sing','loe','sta'] 
for d in nam:
 cur.execute("INSERT INTO pages_post (pname) VALUES ([%s])", d)
 print(len(nam))

Method 2: using a dictionary

This one seems a little clumsy, but if you have multiple parameters in your INSERT statement, it might improve readability

nam = ['sau','sing','loe','sta'] 
for d in nam:
 cur.execute("INSERT INTO pages_post (pname) VALUES (%(pname)s)",
 {'pname': d} )
 print(len(nam))

@OluwafemiSule's answer using execute_values is worth looking at, too. I think it comes down to personal preference to which solution is the most readable. Unless you are worried about raw speed. In that case, the docs say that execute_values does have some optimizations that can make it run faster than simple loops like I've shown in Method 1 and Method 2.