something is wrong inside my sqlite code but i can't see it

Question

I have reduced it from what it was before. Now I am trying to update one thing in a database. here is the function in the first file that executes the second one that is in another file

def UpdateItem(self):
    name1 = self.ItemIDLabelLineEdit.text()
    name8 = self.ItemNameLineEdit.text()
    print(name1)
    print(name8)
    SqlData.ItemUpdate(self, name1, name8)

here is the second function that is in another file

def ItemUpdate(self, name1, name8):
    db= sqlite3.connect("SQLite database")
    cursor=db.cursor()
    print("HEy")
    print(name1)
    print(name8)
    cursor.execute("""UPDATE Items_Table SET Item_Name = """+name8+""" WHERE Item_ID = """+name1+""" """)

when I run the python code it says the error is the value for name8 doesn't have a column. I can't see the error inside the cursor.execute statement. if you can help thank you

first of all, post a full error stack – MaxU - stand with Ukraine Feb 19 '16 at 20:42 — MaxU - stand with Ukraine, Feb 19 '16 at 20:42

score 1 · Answer 1 · edited May 23 '17 at 11:50

Don't use string concatenation to make your query - this is error prompt and not safe. Instead, create a parameterized query:

query = """
    UPDATE 
        Items_Table 
    SET
        Item_Name = :name8, 
        Item_Description = :name5, 
        Item_Price = :name3, 
        Item_Cost = :name2, 
        Item_Stock_Level = :name6, 
        Item_ReOderLevel = :name4, 
        Item_Minimum_Stock_Level = :name7 
    WHERE 
        Item_ID == :name1"""
cursor.execute(query, locals())

score 0 · Accepted Answer · answered Feb 20 '16 at 10:20

A simplistic insert and update using parameters for your case, I hope it helps.

import sqlite3
db= sqlite3.connect("SQLite database.db")
cursor=db.cursor()
cursor.execute("select * from Items_Table")
print cursor.fetchall()

name1="3000"
name8="Number 3000"
cursor.execute("insert into Items_Table (Item_ID,Item_Name) values(?,?)",(name1,name8))

cursor.execute("select * from Items_Table")
print cursor.fetchall()

name8="Number Changed from 3000"
cursor.execute("update Items_Table set Item_name = ? where (Item_ID = ?)",[name8,name1])

cursor.execute("select * from Items_Table")
print cursor.fetchall()

db.commit()
db.close()

Output:

$ python db.py
[(u'1000', u'number 1000'), (u'2000', u'number 2000')]
[(u'1000', u'number 1000'), (u'2000', u'number 2000'), (u'3000', u'Number 3000')]
[(u'1000', u'number 1000'), (u'2000', u'number 2000'), (u'3000', u'Number Changed from 3000')]
$

something is wrong inside my sqlite code but i can't see it

2 Answers2