Diving into SQLite Using Python

...Or, The Trouble With Tuples.
(Sorry, the pun had to be made).

For the past 3-ish months, I've been teaching myself Python. I started off with Wesley Chun's Live Lessons video tutorials, and later moved on to his book Core Python, 2nd ed. It has been a satisfying ride so far. Not without tribulations of course, but things are coming along nicely.

Once I had learned enough of the basics, I jumped into writing some programs. Simple things at first of course such as number guessing games and the like-- at first taken from textbook exercises, but later also incorporating various other amateur programming challenges I found on the web.

My most recent project is a database manager application. Originally it was a response to a programming challenge posted on the Ubuntu Forums, but slowly developed into a larger and more powerful app as I decided to add more and more features not called for in the assignment.

Development of the app moved along at a steady pace until I implemented record deletion. I could successfully search the DB using a parameter entered by the user and edit the record, but when I tried to delete it I was met with the error:

ValueError: parameters are of unsupported type

This was something I had not previously encountered. After exhausting my available resources, I decided to ask for help on the Ubu-forums. I started a thread (full details about the program and the solution can be found therein) and got my answer in short notice. Essentially it was this:

I had already successfully implemented DB record editing with the statement

cursor.execute('UPDATE main SET FName=?, LName=?, age=? WHERE id=?', (dataFName, dataLName, dataAge, record))

This part of things worked without a hitch. But when, in the same function, I ran

cursor.execute('DELETE FROM main WHERE id=?', (record))

I would get the "unsupported type" error.

The problem, I learned, was with the variable "(record)" that I was passing to the SQLite statement. The data passed needs to be of type Tuple and I was not providing one. I was providing a mutable string!

I was puzzled by the fact that the edit statement worked and the delete statement did not. It dawned on me that I was inadvertently creating a Tuple in the edit statement-- this was completely a by-product of me passing multiple variables across. It just happened to be creating the tuple I needed without me realizing it. With that in mind, what I had to do was make a very small change to the delete statement in order to create a tuple. I added a comma after record so that the statement read:

cursor.execute('DELETE FROM main WHERE id=?', (record,))

And that was all it took! Lesson learned. Things are moving along nicely with this hurdle out of the way and I hope to soon be finished with the app.