psycopg2 inserting variables with quotes and parenthesis into table

Question

I am using python 3.6 and psycopg2 to upload CSV-files to a postgres database. postgresql is not too happy with quotes and paranthesis in the variables. Is there a smart way to insert such variables into a database?

var_list = ['p_pladser.3320', '1108', "Christian II's Allé", '1', 'nej', "Christian II's Allé", 'Ulige husnr.', 'Amager Vest', '', 'Uafmærket parkering', '2012-02-14T12:06:07', '2009-07-15T00:00:00', '', '37086', 'MULTILINESTRING ((12.60868230570311 55.65583969695316, 12.608588075325498 55.65581925066134))']

I have tried

query = "INSERT INTO p_pladser (FID, vejkode, vejnavn, antal_pladser, restriktion, vejstatus, vejside, bydel, p_ordning, p_type, rettelsedato, oprettelsesdato, bemaerkning, id, wkb_geometry) VALUES %s" % repr(tuple(map(str,var_list)))
dbcur.execute(query)

and

query = "INSERT INTO p_pladser (FID, vejkode, vejnavn, antal_pladser, restriktion, vejstatus, vejside, bydel, p_ordning, p_type, rettelsedato, oprettelsesdato, bemaerkning, id, wkb_geometry) VALUES %s" % ','.join('?' * len(var_list))
cursor.execute(query, var_list)

Both suggestions from another post to a similar but simple problem.

What about reading the documentation? http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries — piro, Nov 28 '17 at 16:23

score 3 · Accepted Answer · answered Nov 28 '17 at 12:51

3

In short: don't put literals into the query; use placeholders and parameter binding.

answered Nov 28 '17 at 12:51

9000

39,899
9
66
104

score 0 · Answer 2 · answered Nov 28 '17 at 12:48

I found out that in SQL you escape ' by adding an extra '. The query works now if I just do the following.

# Replacing single quote with two single quotes
var_list = [w.replace("'", "''") for w in var_list]

# Adding the variables to query
query = "INSERT INTO p_pladser (FID, vejkode, vejnavn, antal_pladser, restriktion, vejstatus, vejside, bydel, p_ordning, p_type, rettelsedato, oprettelsesdato, bemaerkning, id, wkb_geometry) VALUES (" + (', '.join("'" + item + "'" for item in row)) + ")"

# Executing query                
dbcur.execute(query)

You could say that for most posts on stackoverflow. – Esben Eickhardt Nov 29 '17 at 08:50 — Esben Eickhardt, Nov 29 '17 at 08:50

psycopg2 inserting variables with quotes and parenthesis into table

2 Answers2