SQLite and Python Notes

I don't have a background in SQL, so getting the syntax correct for SQLite in Python was a little tricky, especially since it reads like it's straight out of 1983. So, here is some working syntax for a search/select and replace, and also a search/select and an iteration through the results.

Search for one result (on a unique variable) and change some of that entry's data:

cursor.execute("UPDATE outfits SET size=?, members=?, scraped=? WHERE id=?", (how_many_members, char_id_list, 1, int(outfit_id)))

db.commit() 

This assumes you know a bit about SQL. cursor is your cursor object. This snippet searches the db for any entries (lines, rows, whatever) for where the id variable matches the value of outfit_id. In this case, that variable will have all unique entries since I declared it that way when I made the db (which is some other SQLite code that is in multiple other places on the net). So, this line finds the one I want and then changes those three variables, then you commit it which actually write it. That seems really weird to me, either do it or don't do it. I assume this made some sense back in 1983 when people wrote code in capital letters. Oh and outfits here is the name of your table in the db. Well it's the name of my table in my db.

Iterator on search results:

cursor.execute("SELECT * FROM outfits WHERE scraped=?", (0,)) # this selects them but doesn't return them for use. NOTE TUPLE!!! 

not_scraped_outfits = cursor.fetchall() # aha! 

for an_outfit_row in not_scraped_outfits: 
    # do your stuff here 

That seems weird to me, but I guess I don't understand the cursor idea. You SELECT in caps, but then you have to fetchall(). That seems like two steps where you only need one. So, you SELECT everything (the asterisk, I think) from your table that matches the WHERE call, here where scraped is 0, since it's a Boolean. That returns possibly none, one, or more. Usually for me in this particular code it will return several, and then you have to iterate through the results, and I think there are a few ways to code the iterator call, but the code I have here works so there you go. Execute a SELECT which is a search (the WHERE), then fetchall the results (even though you already selected them), then you can iterate through them.

NB: Tuple! When you do the funky security thing, which I can't explain and don't care about since I am only running local code, the argument has to be a tuple, so if you are just passing one argument you need a trailing comma:

cursor.execute("SELECT * FROM outfits WHERE scraped=?", (0)) # fail 

cursor.execute("SELECT * FROM outfits WHERE scraped=?", (0,)) # success, due to the last comma there


Also, one of the many pages I was poking around at suggested SQLite Manager, a plugin for Firefox. There may be other similar things, I have no idea, but I really like it, it's free, and if you don't have anything that allows you to view the innards of your SQL db easily, I strongly recommend it. If you don't use Firefox, heck it's just another app (I tend to think I don't need three browsers on my machines, but hey).

More also, it is apparently a good idea to store really long ID numbers as text, not numeric. (Because something, somewhere, decided to round them all off so they were all wrong.)