psycopg2 postgres database syntax error near value

The error message is explicit, this SQL command is wrong at Avatar: INSERT INTO test_movies VALUES Avatar,action,2009,7.9. Simply because values must be enclosed in parenthesis, and character strings must be quoted, so the correct SQL is:

INSERT INTO test_movies VALUES ('Avatar','action',2009,7.9)

But building a full SQL command by concatenating parameters is bad practice (*), only the table name should be directly inserted into the command because is is not a SQL parameter. The correct way is to use a parameterized query:

cur.execute("INSERT INTO %s VALUES (?,?,?,?)"  % (table_name,) ,(name ,genere , year,impd_rating)))

(*) It was the cause of numerous SQL injection flaws because if one of the parameter contains a semicolumn (;) what comes after could be interpreted as a new command

Pandas has a DataFrame method for this, to_sql:

# Only needs to be executed once.
conn=psycopg2.connect("dbname='database1' user='postgres' password='postgres333' host='localhost' port=5433 ")


df.to_sql('test_movies', con=conn, if_exists='append', index=False)

This should hopefully get you going in the right direction.

In your original query

INSERT INTO %s VALUES %s,%s,%s,%s

there is a sql problem: you need braces around the values, i.e. it should be VALUES (%s, %s, %s, %s). On top of that the table name cannot be merged as a parameter, or it would be escaped as a string, which is not what you want.

You can use the psycopg 2.7 sql module to merge the table name to the query, with placeholders for the values:

from psycopg2 import sql

query = sql.SQL("INSERT INTO {} VALUES (%s, %s, %s, %s)").format(
    sql.Identifier('test_movies'))

cur.execute(query, ('Avatar','action',2009,7.9))

This will make secure both merging the table name and the arguments to the query.

Hello mohamed mahrous,

First install psycopg2 package for the access access PostgreSQL database.

Try this below code,

import psycopg2
conn=psycopg2.connect("dbname='database1' user='postgres' password='postgres333' host='localhost' port=5433 ")
cur=conn.cursor()

def insert(table_name,name,genere,year,impd_rating): 
    query = "INSERT INTO "+table_name+"(name,genere,year,impd_rating) VALUES(%s,%s,%s,%s)"
    try:
        print query
        cur.execute(query,(name,genere,year,impd_rating))
    except Exception, e:
        print "Not execute..."
    conn.commit()

b=0
for row in DF['id']:
    insert (impd_rating=float(DF['idbm_rating'][b]),year=int(DF['year'][b]),name=str(DF['name'][b]),genere=str(DF['genere'][b]),table_name='test_movies')
    b= b+1

conn.close()

Example,

import psycopg2
conn=psycopg2.connect("dbname='database1' user='postgres' password='postgres333' host='localhost' port=5433 ")
cur=conn.cursor()

def insert(table_name,name,genere,year,impd_rating): 
    query = "INSERT INTO "+table_name+"(name,genere,year,impd_rating) VALUES(%s,%s,%s,%s)"
    try:
        print query
        cur.execute(query,(name,genere,year,impd_rating))
    except Exception, e:
        print "Not execute"
    conn.commit()

b=0
for row in DF['id']:
    insert (impd_rating="7.0",year="2017",name="Er Ceo Vora Mayur",genere="etc",table_name="test_movies")
    b= b+1

conn.close()

I hope my answer is helpful.
If any query so comment please.

i found a solution for my issue by using sqlalchemy and pandas to_sql method thanks for help everyone

from sqlalchemy import *
import pandas as pd
def connect(user, password, db, host='localhost', port=5433):

    '''Returns a connection and a metadata object'''

    # We connect with the help of the PostgreSQL URL

    # postgresql://federer:grandestslam@localhost:5432/tennis

    url = 'postgresql://{}:{}@{}:{}/{}'

    url = url.format(user, password, host, port, db)

    # The return value of create_engine() is our connection object

    con = sqlalchemy.create_engine(url, client_encoding='utf8')

    # We then bind the connection to MetaData()

    meta = sqlalchemy.MetaData(bind=con, reflect=True)

    return con, meta

con, meta = connect('postgres','postgres333','database1')
movies= Table('test',meta,
Column('id',Integer,primary_key=True),
Column('name',String),
Column('genere',String),
Column('year',Integer),
Column('idbm_rating',REAL))
meta.create_all(con)

DF=pd.read_csv('new_movies.txt',sep='       ',engine='python')
DF.columns=('id','name' ,'genere' ,'year' ,'idbm_rating' )
DF.to_sql('movies', con=con, if_exists='append', index=False)