Multiple string interpolation in Python

Question

I'm trying to build an SQL query that involves two separate insertions of the same string, which itself is constructed by joining a list (terms) with commas. I'm doing this as follows:

format_strings = ','.join(['%s'] * len(terms))
sql = """
SELECT i.id, i.title, i.description, MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE) AS score
FROM items i
WHERE MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE) AS score""" % format_strings, (terms, terms)

The result is TypeError: not enough arguments for format string

This works with just the one use of terms, but not a second. I'm very new to Python, so this is probably something pretty straightforward. Any help much appreciated!

Edit: terms comes from running .split() on a string including spaces, so ('apples', 'oranges') would be an example value.

Update:

The following results in an SQL error, but it does look rather like the strings aren't being substituted (an %s appears in the SQL):

terms = term.split()
format_strings = ','.join(['%s'] * len(terms))
sql = """SELECT i.id, i.title, i.description, MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE) AS score
FROM items i
WHERE MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE)""" % (format_strings, (terms, terms))
cursor.execute(sql)

Output:

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s IN NATURAL LANGUAGE MODE) AS score\tFROM items i\tWHERE MATCH (i.title, i.descr' at line 1")

Could you give an example of what terms looks like, and an example of what sql would look like when properly formatted? Because without this, we have to be parsing your SQL and trying to guess what you're trying to do. — user1950164
– user1950164, Commented May 5, 2016 at 13:24

Eugene Yarmash · Accepted Answer · 2017-08-03 14:34:41Z

4

You need to pass two instances of format_strings when formatting the query, or, better yet, use str.format() and reference the argument twice:

sql = """
SELECT i.id, i.title, i.description, MATCH (i.title, i.description)
  AGAINST ({0} IN NATURAL LANGUAGE MODE) AS score
FROM items i
WHERE MATCH (i.title, i.description) AGAINST ({0} IN NATURAL LANGUAGE MODE)
  AS score""".format(format_strings)

Then pass the parameters to cursor.execute():

cursor.execute(sql, terms*2)

edited Aug 3, 2017 at 14:34

answered May 5, 2016 at 13:39

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

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

Comments

MSS · Accepted Answer · 2016-05-05 13:41:50Z

1

You can prepare your sql as follows:

format_strings = ','.join(['%s'] * len(terms))
sql = """SELECT i.id, i.title, i.description, MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE) AS score FROM items i WHERE MATCH (i.title, i.description) AGAINST (%s IN NATURAL LANGUAGE MODE) AS score""" % (format_strings, format_strings)
#Execute the sql in your database cursor
db_cursor.execute(sql,[terms,terms])

answered May 5, 2016 at 13:41

MSS

3,6832 gold badges28 silver badges57 bronze badges

Collectives™ on Stack Overflow

Multiple string interpolation in Python

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related