CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
user21398
user21398

Reputation: 1493

Inserting multi-word string and an empty array with psycopg2

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[]                |

Upvotes: 1

Views: 951

Answers (1)

Joseph Sheedy
Joseph Sheedy

Reputation: 6726

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))

Upvotes: 1

Related Questions