CODEWITHSUNDEEP
pythonsqlite
Dor-Ron
Dor-Ron

Reputation: 1807

string formatting a sql query in sqlite3

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?

Upvotes: 11

Views: 12238

Answers (3)

Jakub Kukul
Jakub Kukul

Reputation: 14494

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)

Upvotes: 10

gregory
gregory

Reputation: 12895

With Python 3.6+ you can simplify this quoting mess with f strings. For example: 

c.execute(f"select sql from sqlite_master where type='table' and name='{table_name}';")
for r in c.fetchall():
    print(r)

In this snippet, the important thing to note is f preceding the sql string. This allows one to pass in variables surrounded by curly braces, in my example: '{table_name}'

Upvotes: 3

Grokify
Grokify

Reputation: 16334

SQL needs strings in VALUES to be quoted. Integers and floats do not need to be quoted.

In the commented output below, notice that the 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 produces 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)

Upvotes: 8

Related Questions