Inserting multi-word string and an empty array with psycopg2

Question

psycopg2 complains when inserting multiple words, empty strings, and empty arrays:

name = "Meal Rounds"
description = ""
sizes = []
cur.execute(""" INSERT INTO items (name, description, sizes) VALUES (%s, %s, %s)""" % (name, description, sizes))

Errors:

# Multi word error
psycopg2.ProgrammingError: syntax error at or near "Rounds"
LINE 1: ... (name, description, sizes) VALUES (Meal Rounds, , ...
                                                    ^
# Empty string error
psycopg2.ProgrammingError: syntax error at or near ","
LINE 1: ...scription, sizes) VALUES ("Meal Rounds", , [], Fals...
                                                    ^
# Empty array error
psycopg2.ProgrammingError: syntax error at or near "["
LINE 1: ...n, sizes) VALUES ("Meal Rounds", "None", [], False)...
                                                    ^

I can get around the multi word error by escaping:

""" INSERT INTO items (name, description, sizes) VALUES (\"%s\", \"%s\", %s)"""

But for tables with 15+ columns, escaping each one is a pain. Does psycopg2 not handle this in an easier fashion? It will still throw errors for empty strings though.

How do I insert multiple words more efficiently, and how to insert empty strings and arrays?

Here is what psql prints out on my columns:

 name          | character varying(255)   | 
 description   | character varying(255)   | 
 sizes         | integer[]                |

What's your table DDL look like?

khampson
– khampson

2014-12-15 06:19:42 +00:00
Commented Dec 15, 2014 at 6:19 — khampson
– khampson, Commented Dec 15, 2014 at 6:19
@khampson I added psql printout of the column

user21398
– user21398

2014-12-15 06:29:16 +00:00
Commented Dec 15, 2014 at 6:29 — user21398
– user21398, Commented Dec 15, 2014 at 6:29

Joseph Sheedy · Accepted Answer · 2014-12-15 06:36:41Z

1

Your call to execute is creating a string with Python string substitution, which is turning out to be invalid SQL. You should be using the parameter substitution provided by the Python DB API:

https://www.python.org/dev/peps/pep-0249/#id15

To call execute using parameter substitution, you pass it two arguments. The first is the query with parameter strings which are database dependent. Psycopg2 uses "pyformat" paramstyle so your query will work as written. The second argument should be the variables you want to substitute into the query. The database driver will handle all the quoting/escaping you need. So your call to execute should be

cur.execute("""INSERT INTO items (name, description, sizes) VALUES (%s, %s, %s)""", (name, description, sizes))

answered Dec 15, 2014 at 6:36

Joseph Sheedy

6,7604 gold badges34 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Inserting multi-word string and an empty array with psycopg2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related