CODEWITHSUNDEEP
pythonpython-2.7
user1381745
user1381745

Reputation: 3900

Multiple string interpolation in Python

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

Upvotes: 2

Views: 495

Answers (2)

Eugene Yarmash
Eugene Yarmash

Reputation: 150031

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)

Upvotes: 4

MSS
MSS

Reputation: 3633

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

Upvotes: 1

Related Questions