CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
user3734644
user3734644

Reputation: 53

How to Insert tuple in postgres using [Insert into...select] statement, Not [Insert into...vales] method

I am knew to this and am trying to insert data into this postgres table as follows in python:

cur.execute('''INSERT INTO trademarks_image_mark (trademarks_id, image_mark_id)
               SELECT currval('trademarks_id_seq'), id FROM image_mark WHERE 
               image_file_name LIKE %s''', data['ImageFileName'])

..Yet I keep on getting this error:

TypeError: not all arguments converted during string formatting

Does anyone know why this might be happening? I know how to insert tuples like (%s) with regular Insert into...values statements, but not in this format. Much appreciated and thanks!

Upvotes: 1

Views: 3173

Answers (1)

dano
dano

Reputation: 94881

As stated in the psycopg2 docs, you have to pass the arguments being inserted into the query as a sequence, even if there's only one:

cur.execute('''INSERT INTO trademarks_image_mark (trademarks_id, image_mark_id)
               SELECT currval('trademarks_id_seq'), id FROM image_mark WHERE 
               image_file_name LIKE %s''', (data['ImageFileName'],))

Here's the relevant section from the docs:

For positional variables binding, the second argument must always be a sequence, even if it contains a single variable. And remember that Python requires a comma to create a single element tuple:

>>> cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # WRONG
>>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # WRONG
>>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
>>> cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # correct

Upvotes: 2

Related Questions