string formatting a sql query in sqlite3

Question

I've been playing around with sqlite3, and I get an sqlite3.OperationalError: near "sweet": syntax error for this line of my code query_cursor.execute("INSERT INTO mcdonalds_menu VALUES(%d, %s, %f, %s, %d)" % (ids[num],names[num], price[num], descriptions[num], calories[num])) When I put in the values in 3 separate queries the code seems to work, but I'm trying to keep my code more DRY by using a for loop. The code so far:

import sqlite3

filename = sqlite3.connect("McDonalds_Menu.db")
query_cursor = filename.cursor()

def create_table():
    query_cursor.execute( "CREATE TABLE mcdonalds_menu (id INTEGER, name VARCHAR(20), price DECIMAL(3, 2), description TEXT, calories INTEGER)")

ids = range(1,4)
names = ["McFlurry", "Fillet-o-Fish", "McCafe"]
price = 1.50, 2.25, 0.99
descriptions = ["Delicious sweet icecream", "Best fish in the sea", "Freshly brewed Colombian coffee"]
calories = 220, 450, 75

def data_entry():
    for num in xrange(3):
        query_cursor.execute("INSERT INTO mcdonalds_menu VALUES(%d, %s, %f, %s, %d)" % (ids[num], names[num], price[num], descriptions[num], calories[num]))    
    filename.commit()

if __name__ == "__main__":
    create_table()
    data_entry()

Is it possible to string format a sql query using a loop?

Answer 1

All the other answers relying on python's string manipulation are insecure and might not correctly escape quotes in your strings.

The best way to do it, as suggested in sqlite3 documentation, is to use the DB-API’s parameter substitution. In your example, it would look like this:

menu_items = [(1, 'McFlurry', 1.5, 'Delicious sweet icecream', 220),
              (2, 'Fillet-o-Fish', 2.25, 'Best fish in the sea', 450),
              (3, 'McCafe', 0.99, 'Freshly brewed Colombian coffee', 75)
              ]
c.executemany('INSERT INTO mcdonalds_menu VALUES (?,?,?,?,?)', menu_items)