Idiomatic way to construct a SQL INSERT query in Python

There is a neater, more Pythonic way of doing this using the str.format function. However, it is not the way you should do it.

Currently, the way you are building your query, you are vulnerable to SQL injection. This is the point @Mat's Mug was getting at in his comment. What if a malicious user decides to give the following as a user ID:

user_id = "1,1,99,'today');DROP TABLES;INSERT INTO words (word, user_id, game_id, score, time_stamp) VALUES ('Goodbye', 1"

If this happens, a disaster beyond your imagination will occur*!

To prevent this, the cursor from the MySQLdb module (which I assume you are using) allows you to pass in a tuple or dict of the parameters to the execute function:

time_stamp = datetime.datetime.now().isoformat()
sql = "INSERT INTO words (word, user_id, game_id, score, time_stamp) VALUES {}"

# Create the query
sql = sql.format(','.join(["('%s', %s, %s, %s, '%s')" for _ in words]))

# Create the parameter tuple.
params = ()
for word in words:
    params += (word['str'], user_id, game_id, word['score'], time_stamp,)

# Sending a tuple is easiest in this situation
cur.execute(sql, params)

If you want to look at another database solution for Python, take a look at SQLAlchemy. Its a pretty powerful framework that makes working with databases much simpler.

*=Sorry for the Phantom of the Opera quote. I'm feeling oddly dramatic right now.

Update

To answer your question about removing the for-loop, the best you can hope for is to use a list comprehension. In many situations, list comprehensions are typically the Pythonic way of iterating over a collection. However, in your situation, this probably is not the case.

The comprehension for your current code would look like this:

','.join([str(tuple([word['str'], user_id, game_id, word['score'], time_stamp]))
          for word in words])

This is really only effective creating the end to your query. Unfortunately, in addition to being hard to read, it is still vulnerable to SQL injection.

In your case, I think a for-loop would be the cleanest way to implement what you want.