Formatting a sql query string in sqlite3

Question

I played with sqlite3 and I get sqlite3.OperationalError: next to "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 add values ​​to 3 separate queries, it looks like the code is working, but I'm trying to save the code more DRY using a for loop. 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 format the sql query format with a loop?

Answer 1

SQL needs rows in VALUES to quote. Chains and floats do not need to be specified.

In the comments below, note that SQL VALUES contains unquoted strings for "Fillet-o-Fish" and "Best Fish in the Sea":

 sql = "INSERT INTO mcdonalds_menu VALUES(%d, %s, %f, %s, %d)".format(ids[num], names[num], price[num], descriptions[num], calories[num]) # INSERT INTO mcdonalds_menu VALUES(2, Fillet-o-Fish, 2.250000, Best fish in the sea, 450) 

Adding some escaped quotes around your string values ​​results in valid SQL:

 sql = "INSERT INTO mcdonalds_menu VALUES(%d, \"%s\", %f, \"%s\", %d)" % (ids[num],names[num], price[num], descriptions[num], calories[num]) # INSERT INTO mcdonalds_menu VALUES(2, "Fillet-o-Fish", 2.250000, "Best fish in the sea", 450)