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

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. — Mihai Chelaru
– Mihai Chelaru, Commented Sep 18, 2019 at 20:51
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. — Bergi
– Bergi, Commented Sep 18, 2019 at 21:37

Oluwafemi Sule · Accepted Answer · 2019-09-18 22:08:02Z

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)
)

answered Sep 18, 2019 at 22:08

Oluwafemi Sule

39.3k1 gold badge63 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bfris · Accepted Answer · 2019-09-19 00:02:02Z

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.

Collectives™ on Stack Overflow

Syntax error executing query from python (postgres)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related