53

I'm very new to python. I need a simple and clear script to add quotes to every list elements. Let me explain more. Here is the my code.

parameters = ['a', 'b', 'c']
query = "SELECT * FROM foo WHERE bar IN (%s)" % (', '.join(parameters))

I want to use this to query. But result is invalid query. Here is the result.

SELECT * FROM foo WHERE bar IN (a, b, c, d)

I want to like this:

SELECT * FROM foo WHERE bar IN ('a', 'b', 'c', 'd')

How to add quotes while joining elements.

crizCraig
  • 8,487
  • 6
  • 54
  • 53
Zeck
  • 6,433
  • 21
  • 71
  • 111

5 Answers5

90

A naive solution would be to iterate over your parameters list and append quotes to the beginning and end of each element:

(', '.join('"' + item + '"' for item in parameters))

Note: this is vulnerable to SQL injection (whether coincidental or deliberate). A better solution is to let the database quote and insert these values:

query = "SELECT * FROM foo WHERE bar IN (%s)" % ','.join('?' * len(params))
cursor.execute(query, params)

It's easier to read and handles quoting properly.

Blender
  • 289,723
  • 53
  • 439
  • 496
  • 5
    `["'" + item + "'" for item in parameters]` would give him single quotes, which is what he asked for. – machine yearning Jul 19 '11 at 01:11
  • 2
    to nit pick, you don't need the list [] here: (', '.join('"' + item + '"' for item in parameters)) – carl Jul 19 '11 at 02:39
  • 1
    @Blender No, a list DOESN'T get spat out of that expression. The generator makes the items as `string.join` asks for them, then they can immediately get garbage collected. With a list comprehension, they all get generated and stored, then join iterates over the list, and garbage collection on them can't occur until join is done with the whole list. – agf Jul 19 '11 at 03:21
  • @agf, thanks for the info. I didn't know that generator expressions and list comprehensions were different in that regard. I will update my answer. – Blender Jul 19 '11 at 03:27
  • @Blender Take a look at my answer for an even simpler, more efficient version (posted long after yours). – agf Jul 19 '11 at 03:28
  • When I do this, I get an error as the ? and not replaced by the variables. Why is this? I'm on Python 3.6 – Esben Eickhardt Nov 28 '17 at 12:09
  • @EsbenEickhardt: What database driver are you using? – Blender Nov 28 '17 at 15:58
  • @Blender I am using psycopg2.connect, and I dont give it a database driver as input, so it must handle it in the background. – Esben Eickhardt Nov 29 '17 at 08:48
  • 2
    @EsbenEickhardt: `psycopg2` uses `%s` (for everything, not just strings) instead of `?`. – Blender Nov 30 '17 at 01:03
12

For simple parameters, the following should work:

query = "SELECT * FROM foo WHERE bar IN %s" % repr(tuple(map(str,parameters)))

This may break down when the parameter names themselves include quotes, as the escaping rules are different.

Sam Ruby
  • 4,270
  • 22
  • 21
  • This script generates invalid query. – Zeck Jul 19 '11 at 01:10
  • It generates "SELECT * FROM foo WHERE bar IN ('a', 'b', 'c')", which matches what was asked for, and works (at least with the one database I tried, namely sqlite3). – Sam Ruby Jul 19 '11 at 01:13
  • Sorry I'm using PostgreSQL. It shows me SELECT * FROM foo WHERE bar IN ((u'a', u'b', u'c', u'd')) this. An invalid query. – Zeck Jul 19 '11 at 01:15
  • Ah, your parameter array has unicode characters not strings as you asked. I've modified the example to convert to strings. In any case, if you have complex strings you want to do considerably more than simply add quotes. – Sam Ruby Jul 19 '11 at 01:23
  • I see. Thank you very much. Sorry for give you down rate. Feel sorry. – Zeck Jul 19 '11 at 01:24
8

As you asked it, use this:

parameters = ['a', 'b', 'c']
', '.join(map(lambda x: "'" + x + "'", parameters))

Since you're creating an SQL query, please use your database library's features regarding input sanitation (example for mysqldb). You don't want to end up with an issue like Bobby Tables.

Community
  • 1
  • 1
robert
  • 33,242
  • 8
  • 53
  • 74
  • `(lambda x: "'" + x + "'", parameters)` would give him single quotes, which is what he asked for. – machine yearning Jul 19 '11 at 01:09
  • @robert: A good generator expression using builtins will almost always be faster than using map on a user-defined function on CPython, because it will happen almost entirely in C. It's also more readable and the Pythonic way to do it. Much love for little Bobby Tables though. – agf Jul 19 '11 at 03:09
  • @agf: more readable, yes; faster? Not on Python 2. Compare `timeit(lambda: ', '.join(map(lambda x: "'" + x + "'", parameters)))` and `timeit(lambda: ', '.join("'" + x + "'" for x in parameters))`. The `map()` version is 15-20% faster on Python 2.7.1+/Ubuntu 11.04 64-bit. The generator version is about 8% faster on Python 3.2/ditto. – Chris Morgan Jul 19 '11 at 04:40
  • @Chris-Morgan: I'm on Python 2.7.1 32-bit on 64-bit Windows 7 and the the generator version is 20% faster. Edit: Running it a second time, the generator version is the same speed, but the map version is much faster. – agf Jul 19 '11 at 04:44
  • @Chris-Morgan: And the version from my answer below, `timeit(lambda: "', '".join(parameters))`, is about 5x faster than `timeit(lambda: ', '.join(map(lambda x: "'" + x + "'", parameters)))`, although that's comparing apples to oranges. It seems to me map/lambda must somehow be doing the string concatenation differently? – agf Jul 19 '11 at 04:52
3

In general (ignoring SQL)

In [3]: print(' '.join('"%s"' % x for x in ['a', 'b']))                                                                                                                                              
"a" "b"
crizCraig
  • 8,487
  • 6
  • 54
  • 53
0

if your list looks like

lst = ['abc', '123', 'xyz']
str_row = "( '"+"','".join(map(str, lst))+"')"
print(str_row)

output will be

( 'abc','123','xyz')
bhargav3vedi
  • 521
  • 1
  • 6
  • 11