tuple and formating problem

[darktitan]

Hi
Im using sqlite3 for a lite project and im getting this error

Error:
conn.execute('SELECT * FROM anställda WHERE ID={0},{1},{2},{3}'.format(uid[:4]))
IndexError: tuple index out of range

i have no idea how to fix it. I think the fault lies in the formating or something. Could anyone take a look at the code and help pls.

    # Database  stuff
    #dataid = (uid[:4])
    conn.execute('SELECT * FROM anställda WHERE ID={0},{1},{2},{3}'.format(uid[:4]))
    data = cursor.fetchall()
    #print(data)
    for row in data:
        version = (row[0]) #<----siffran bestämmer column
        print(version)

[marienbad]

sql = "SELECT * FROM anställda WHERE ID = ?"
data = (uid[:4],) # you need the comma as it expects a tuple!

conn.execute(sql, data)

does this work? I am unsure how uid is used and why you have 4 values in the "Where" part.

[darktitan]

Hey
The uid is a rfid tag code when i read the tag i get a nummber like this 150, 110, 1, 164 .
So what im trying to do is to scan the tag and then look up the nummber in the database.

Here is how i do it when im not using a db.

    # UID stuff
    if status == MIFAREReader.MI_OK:
     # Print UID
      print "Card read UID: {0},{1},{2},{3}".format(*uid)
      print (st)
      if uid[:4] == [150,110,1,164]:
        Print ("Hi there")
      time.sleep(2)

[marienbad]

How is the uid stored in the database? is it one column of varchar, or four separate numbers?

[darktitan]

Its stored in one column of TEXT.

[marienbad]

If it is in the same form as "150, 110, 1, 164":

(assumes uid is a string)

sql = "SELECT * FROM anställda WHERE ID = ?"
data = (uid,) # you need the comma as it expects a tuple!

conn.execute(sql, data)

Does this work?

[darktitan]

I dont understand the code you wrote.

[marienbad]

Okay, so the uid is a list of numbers, right? In which case we need to make them a string and check it against the db, so to adjust the above code:

for e.g. uid = [150,110,1,164]

uid_str = ','.join([str(i) for i in uid])

then we create the sql for the query:

sql_str = "SELECT * FROM anställda WHERE ID = ?"

and the data for the query is now the uid_str we created from the list of numbers (i.e. "150,110,1,64"

so we use that as the data and pass it to the execute function:

conn.execute(sql_str, uid_str)