psycopg2 postgres database syntax error near value

Question

I am trying to insert info from a pandas DataFrame into a database table by using a function that I wrote:

def insert(table_name="", name="", genere="", year=1, impd_rating=float(1)):
    conn = psycopg2.connect("dbname='database1' user='postgres' password='postgres333' host='localhost' port=5433 ")
    cur = conn.cursor()
    cur.execute("INSERT INTO %s VALUES %s,%s,%s,%s"  % (table_name, name, genere, year, impd_rating))
    conn.commit()
    conn.close()

When I try to use this function like this:

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

I get the following syntax error:

SyntaxError: invalid syntax
PS D:\tito\scripts\database training> python .\postgres_script.py
Traceback (most recent call last):
File ".\postgres_script.py", line 56, in <module>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')
File ".\postgres_script.py", line 15, in insert
cur.execute("INSERT INTO %s VALUES %s,%s,%s,%s"  % (table_name ,name ,genere , year,impd_rating))
psycopg2.ProgrammingError: syntax error at or near "Avatar"
LINE 1: INSERT INTO test_movies VALUES Avatar,action,2009,7.9

I also tried to change the str replacement method from %s to .format() but I had the same error.

Answer 1

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

Answer 2

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.