Python psycopg2 syntax error

Question

I am new to python and working on using the psycopg2 to insert data in postgres database. I am trying to insert items but get the error message

"Psycopg2.ProgrammingError: syntax error at or near "cup" LINE 1: INSERT INTO store VALUES(7,10.5,coffee cup)

with the ^ next to coffee cup. I am assuming the order is wrong but i thought you could enter it this way as long as you specified the values.

Here is the code.

import psycopg2

def create_table():
    conn=psycopg2.connect("dbname='db1' user='postgres' password='postgress123' host='localhost' port='5432'")
    cur=conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS store (item TEXT, quantity INTEGER, price REAL)")
    conn.commit()
    conn.close()

def insert(quantity, price, item):
    conn=psycopg2.connect("dbname='db1' user='postgres' password='postgress123' host='localhost' port='5432'")
    cur=conn.cursor()
    cur.execute("INSERT INTO store VALUES(%s,%s,%s)" % (quantity, price, item))
    conn.commit()
    conn.close()

create_table()
insert(7, 10.5, 'coffee cup')

Don't do string interpolation directly in sql queries. It leaves you vulnerable to sql injection attacks. In this case it also causes your query to be improperly formatted. — Paul Rooney
– Paul Rooney, Commented Jun 30, 2017 at 0:08
Thanks Paul. I am very new to the python and psycopg2, and was following examples on the web to get my feet wet on how things work. In this case, how would you avoid SQL injections in the code above? Psycocg2 document advised that %s should be used regardless of what type of data is being passed. Any help would be greatly appreciated. — IpSp00f3r
– IpSp00f3r, Commented Jul 1, 2017 at 22:51

snakecharmerb · Accepted Answer · 2025-11-22 12:10:47Z

10

Remember to always use the second argument of the execute command to pass the variables, as stated here.

Also, use the name of the fields in your syntax:

cur.execute(
    "INSERT INTO store (item, quantity, price) VALUES (%s, %s, %s);", 
    (item, quantity, price)
)

That should do the trick.

edited 49 mins ago

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

answered Jun 30, 2017 at 0:04

jgmh

70912 silver badges25 bronze badges

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

1 Comment

IpSp00f3r Over a year ago

This worked like a charm. Thanks for pointing me in the right direction

Vardhman Patil · Accepted Answer · 2017-06-30 08:06:39Z

Problem in your case is coffee cup parameter value is considered as string but psycopg2 accept the value in single quote. Basically as per my understanding when we create SQL query for psycopg2 it ask for single quote for data parameters [if you have given double quote for query start and end] In your case you have given double quote for Query Start and end so you need to give single quote for the parameters.

My Observation is you provide single quote for each data paramater in psycopg2

import psycopg2

def create_table():
    conn=psycopg2.connect("dbname='db1' user='postgres' password='postgress123' host='localhost' port='5432'")
    cur=conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS store (item TEXT, quantity INTEGER, price REAL)")
    conn.commit()
    conn.close()

def insert(quantity, price, item):
    conn=psycopg2.connect("dbname='db1' user='postgres' password='postgress123' host='localhost' port='5432'")
    cur=conn.cursor()
    #cur.execute("INSERT INTO store VALUES(%s,%s,%s)" % (quantity, price, item))
    cur.execute("INSERT INTO store VALUES('%s','%s','%s')" % (quantity, price, item))
    conn.commit()
    conn.close()

create_table()
insert(7, 10.5, 'coffee cup')

nakli_batman · Accepted Answer · 2022-11-11 00:58:16Z

0

I also faced the very same problem, and after a while troubleshooting the code, I found that I forgot to add commas(,) in the Insert query.

The code that causes the error:

data['query'] = 'insert into contacts (name, contact_no, alternate_contact_no, email_id, address)' \
                'values (%s %s %s %s %s)'

As you can see in above code, I forgot to add commas after every '%s'.

The correct code:

data['query'] = 'insert into contacts (name, contact_no, alternate_contact_no, email_id, address)' \
                'values (%s, %s, %s, %s, %s)'

Hope, It helps!

answered Nov 11, 2022 at 0:58

nakli_batman

84112 silver badges8 bronze badges

Collectives™ on Stack Overflow

Python psycopg2 syntax error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related