How to string format SQL IN clause with Python

Question

I am trying to create a statement as follows:

SELECT * FROM table WHERE provider IN ('provider1', 'provider2', ...)

However, I'm having some trouble with the string formatting of it from the Django API. Here's what I have so far:

profile = request.user.get_profile()
providers = profile.provider.values_list('provider', flat=True) # [u'provider1', u'provider2']
providers = tuple[str(item) for item in providers] # ('provider1', 'provider2')

SQL = "SELECT * FROM table WHERE provider IN %s"
args = (providers,)
cursor.execute(sql,args)

DatabaseError
(1241, 'Operand should contain 1 column(s)')

Curious. Why are you doing a raw sql IN query when you already have the django ORM? — jdi
– jdi, Commented Jun 25, 2012 at 19:29
@jdi It's a long sql query that I'm building with string concatenation based on some user-inputted values (about 20 lines long). — David542
– David542, Commented Jun 25, 2012 at 19:34
The ORM has aggregations though. But I guess I just have to take your word for it that the ORM can't do it :-) — jdi
– jdi, Commented Jun 25, 2012 at 19:53
I'm with @jdi here - is the table you're querying inside the ORM? (slight edit: I'm not with jdi, I mean that in the sense of a somewhat confused view...) — Jon Clements
– Jon Clements, Commented Jun 25, 2012 at 20:00
@JonClements: Jon, I am offended. We talked about being honest in public. David, we are together, and we also share similar opinions on database ORMs — jdi
– jdi, Commented Jun 25, 2012 at 20:14

jdi · Accepted Answer · 2012-06-25 19:46:32Z

6

MySQLdb has a method to help with this:

Doc

string_literal(...) string_literal(obj) -- converts object obj into a SQL string literal. This means, any special SQL characters are escaped, and it is enclosed within single quotes. In other words, it performs:
"'%s'" % escape_string(str(obj))

Use connection.string_literal(obj), if you use it at all.
_mysql.string_literal(obj) cannot handle character sets.

Usage

# connection:  <_mysql.connection open to 'localhost' at 1008b2420>

str_value = connection.string_literal(tuple(provider))
# '(\'provider1\', \'provider2\')'

SQL = "SELECT * FROM table WHERE provider IN %s"
args = (str_value,)
cursor.execute(sql,args)

answered Jun 25, 2012 at 19:46

jdi

93k20 gold badges174 silver badges208 bronze badges

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

5 Comments

Justin.Wood Over a year ago

thats pretty cool. Did not know about the string_literal method. I am assuming that since MySQLdb follows the db api pretty strictly, this exists in most implementations?(sqlite3, postgres, etc) Definetly going back to the docs

jdi Over a year ago

@Justin.Wood: To be honest, I didn't really know about it either, but I don't have much previous experience using MySQLdb and passing tuple values. Its always an ORM. I just looked at the docs too for this one :-)

Justin.Wood Over a year ago

Neat. I work in some legacy code base that doesnt use an ORM, and we get this kinda stuff constantly. So this is going to be a big plus for me. You got a link to this in the docs? Couldnt find it. Though it is easy enough to find in the source.

jdi Over a year ago

@Justin.Wood: Strangely in some versions its simply called literal: mysql-python.sourceforge.net/MySQLdb-1.2.2/public/…

Justin.Wood Over a year ago

Nah, that is an internal function for MySQLdb. You are supposed to use the syntax you used in the answer. It's in the source Luke! ` def _get_string_literal(): def string_literal(obj, dummy=None): return db.string_literal(obj) return string_literal def _get_unicode_literal(): def unicode_literal(u, dummy=None): return db.literal(u.encode(unicode_literal.charset)) return unicode_literal `

Jon Clements · Accepted Answer · 2012-06-25 20:39:56Z

1

Another answer that I don't like particularly, but will work for your apparent use-case:

providers = tuple[str(item) for item in providers] # ('provider1', 'provider2')
# rest of stuff...

SQL = 'SELECT * FROM table WHERE provider IN {}'.format(repr(providers))
cursor.execute(SQL)

edited Jun 25, 2012 at 20:39

answered Jun 25, 2012 at 20:34

Jon Clements

143k34 gold badges254 silver badges288 bronze badges

2 Comments

Jon Clements Over a year ago

Could also write '...{!r}.format(providers) I guess - just depends on taste

user8403508 Over a year ago

This option is not good because if there is only 1i item in the list there will be a comma at the end which is invalid syntax

jdi · Accepted Answer · 2012-06-25 19:41:50Z

0

You should probably do the string replacement before passing it to the cursor object to execute:

sql = "SELECT * FROM table WHERE provider IN (%s)" % \
        (','.join(str(x) for x in providers))
cursor.execute(sql)

edited Jun 25, 2012 at 19:41

jdi

93k20 gold badges174 silver badges208 bronze badges

answered Jun 25, 2012 at 19:36

Justin.Wood

7255 silver badges10 bronze badges

4 Comments

jdi Over a year ago

This may be problematic because the strings are not quoted. Maybe replace str with repr ?

Justin.Wood Over a year ago

could also add quotes to the join eg. %('"'+'","'.join(str(x) for x in providers)+'"') - that could be seen as a bit hack though I suppose

jdi Over a year ago

But I think its required for this solution.

ron rothman Over a year ago

FWIW, this method is susceptible to SQL injection attacks. The accepted answer (by jdi) is safer.

Jon Clements · Accepted Answer · 2012-06-25 19:47:07Z

0

So, you have string input for ID's required:

some_vals = '1 3 5 76 5 4 2 5 7 8'.split() # convert to suitable type if required
SomeModel.objects.filter(provider__in=some_vals)

answered Jun 25, 2012 at 19:47

Jon Clements

143k34 gold badges254 silver badges288 bronze badges

3 Comments

jdi Over a year ago

Yea I asked about this in the main comments. OP said there is a specific reason they are going for a raw query

Jon Clements Over a year ago

@jdi Interesting - if the object is inside the ORM, the SQL query can be retrieved from this answer. Otherwise, I can only think they're querying something outside the ORM.

jdi Over a year ago

I am totally with you on that, for sure. We can only assume the OP has needs that the ORM simply can't meet.

Ashwini Chaudhary · Accepted Answer · 2012-06-25 19:30:12Z

-1

"SELECT * FROM table WHERE provider IN ({0},{1},{2})".format(*args) #where args is list or tuple of arguments.

answered Jun 25, 2012 at 19:30

Ashwini Chaudhary

252k60 gold badges478 silver badges519 bronze badges

1 Comment

jdi Over a year ago

This is a little bit limited since it assumes the length of the list, right?

Tanu · Accepted Answer · 2012-06-25 20:23:01Z

-2

try this.... should work.

SQL = "SELECT * FROM table WHERE provider IN %s"%(providers)
exec 'cursor.execute("%s")'%(SQL)

answered Jun 25, 2012 at 20:23

Tanu

2673 silver badges7 bronze badges

Collectives™ on Stack Overflow

How to string format SQL IN clause with Python

6 Answers 6

5 Comments

2 Comments

4 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

2 Comments

4 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related