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?

Turun · Accepted Answer · 2021-02-22 02:12:36Z

10

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)

edited Feb 22, 2021 at 2:12

Turun

8437 silver badges7 bronze badges

answered Jul 11, 2020 at 14:32

Jakub Kukul

15k4 gold badges61 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grokify · Accepted Answer · 2015-06-20 23:41:53Z

8

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)

edited Jun 20, 2015 at 23:41

answered Jun 20, 2015 at 23:35

Grokify

16.5k8 gold badges70 silver badges94 bronze badges

1 Comment

Christian Oudard Over a year ago

Please be aware that you're allowing SQL injection with this technique.

gregory · Accepted Answer · 2018-06-30 02:51:36Z

3

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}'

answered Jun 30, 2018 at 2:51

gregory

13.8k2 gold badges35 silver badges45 bronze badges

Collectives™ on Stack Overflow

string formatting a sql query in sqlite3

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related