CODEWITHSUNDEEP
pythonpostgresql
IpSp00f3r
IpSp00f3r

Reputation: 83

Python psycopg2 syntax error

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

Upvotes: 4

Views: 47591

Answers (3)

nakli_batman
nakli_batman

Reputation: 741

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!

Upvotes: 0

Vardhman Patil
Vardhman Patil

Reputation: 517

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

Upvotes: 1

jgmh
jgmh

Reputation: 689

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.

Upvotes: 9

Related Questions