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

Question

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!

dano · Accepted Answer · 2014-07-10 20:00:02Z

2

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

answered Jul 10, 2014 at 20:00

dano

95.5k21 gold badges234 silver badges231 bronze badges

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

2 Comments

user3734644 Over a year ago

Hi, this is probably really silly, but could you help me with a very similar error? If I run the following code: cur.execute('''INSERT INTO trademarks_image_mark (trademarks_id, image_mark_id) SELECT id FROM trademarks WHERE serial_number LIKE %s, id FROM image_mark WHERE image_file_name LIKE %s''', [data['ApplicationNumberText'], data['ImageFileName']]) I get a syntax error for the comma after the first %s. Do you know why this might be happening?

dano Over a year ago

@user3734644 That just looks like malformed SQL. This gets the same error:

cur.execute("INSERT INTO trademarks_image_mark (trademarks_id, image_mark_id) SELECT id FROM trademarks WHERE serial_number LIKE 'ok', id FROM image_mark WHERE image_file_name LIKE 'blah'")

. I'm not sure offhand what the right way to do the INSERT you're trying is, but I'm sure you can figure it out (or ask a question here, I suppose).

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related