0

basic question that i haven't been able to find the exact solution for. i'm trying to insert a paramaters into customer table :

def cur():
    db = sqlite3.connect("bookdb.sqlite")
    cursor = db.cursor()
    return cursor

def add_customer(id, name, city, age):
    cursor = cur()
    sql = f'insert into customer(id,name,city,age) VALUES({id}, {name}, {city}, {age})'
    cursor.execute(sql)
    cursor.connection.commit()

and when using :

add_customer(7, 'jimbo', 'NY', 22)

i get the following error:

sqlite3.OperationalError: no such column: jimbo

it appears to work fine when i only insert integers into the function but not strings, the name column exists and i'm unable to add any strings into it.

AlfaGoose
  • 1
  • 3
  • 2
    Use placeholders and parameters instead of substituting into the string. This will solve the error (you're missing quotes around `{name}` and `{city}`) and also prevent SQL injection. – Barmar Dec 13 '21 at 22:21
  • Use `print(sql)` and the problem should be obvious. – Barmar Dec 13 '21 at 22:22

1 Answers1

1

Jut in case it wasn't obvious, here's the correct way to do queries using placeholders, allowing the database connector to do the quoting:

db = sqlite3.connect("bookdb.sqlite")
def cur():
    return db.cursor()

def add_customer(id, name, city, age):
    cursor = cur()
    sql = 'insert into customer(id,name,city,age) VALUES(?, ?, ?);'
    cursor.execute(sql, (name, city, age))
    cursor.connection.commit()

You don't really want db to be a function local variable. You should have it be a global, so you can create more cursors from it later on.

Tim Roberts
  • 48,973
  • 4
  • 21
  • 30